Modules & VBA :: How To Set Up Variables When Access Starts
Oct 9, 2013
I need to set some 'global' variables with default values when my Access 2007 database is loaded. Depending on the user etc these values may be modifed after Access starts but defaults need to be set.
I declared the variables as 'Public' in a Module, then put a function in the same Module (to set the default values) then tried to call the function from an "AutoExec" macro, so it's the first thing that runs when Access loads.The macro throws an error - it can't 'find' the function(?)
I have been at this for almost 3 weeks now and I'm having great difficulties trying to get this right and working. First let me explain what I am doing and what I am trying to do.
Firstly, I am making a Maintenance database using MS access software, what I have are tables, forms and no queries or report existing so far. The forms and tables all work correctly.
After the form (Job request) has been completed I need to save/export into PDF so I am able to hyperlink it against its existing asset card elsewhere.
now what I have done is Created button, on event "on click" I have tried to go to macro builder
ExportWithFormatting Objet Type: Form Object Name: FrmMachineFault/GenMaint Output format: PDF Output file: Auto Start: No Template File: Encoding: Output Quality: Print
when I click this button it opens to save to and it works perfectly but I have to manually type the file name when it gets to the Save to section.
What I want it to do is define the name by the existing fields in the open form. Example Closed date, Effected area, Asset and title.
I have access db sending report with outlook every Monday.
But the problem is if outlook is open when I start the db, email goes in to outgoing folder and msg box of outlook opens saying " outlook will close after countdown if I do not hit the "DO NOT EXIT" button or "EXIT AND SEND LATER" button.
If I hit "EXIT AND SEND LATER" button, it sends next time I open the outlook.
Can't find any way to declare variables in the sort of module that Access uses by default.Having changed the default, I now get 'Option Compare Database' as the first line (previously nothing was there). Adding anything at all in that section, or changing it to 'Option Explicit' has the consistent effect that all the procedure names in the module are not recognised and nothing works.
Variables declared in procedures work only in the procedure even if declared as Public. Can't get a Static variable to work in more than one procedure. However many variables I declare, there are never any in the Declarations list at the top of the Code Window.
When I open my DB (I use Access 2003), Access flashes a window 'Security warning : This file may not be safe if it contains code that was intended to harm your computer. Do you want to open this file or cancel the operation?' How do I stop this annoying message so that my DB opens directly?
Im a relative novice with access VBA, and I'm really struggling with using Dcount with date variable. All I want to do is count if a certain date appears in a table. Here is the extract from my code:
Whatever dates are in tblworkoutlogs, datecount is still = 0...I've trawled the net and tried many variations of the code but no success!if I change all the date formats to strings in the code and the tables, it works so I know i'm looking in the correct place.
I have a public sub routine which requires parameters to be passed to it when I call it from an access form. When I try to enter the code to call the sub I get a compile error. I've also tried calling it from another sub in the same module but get the same compile error - see below.
Code: Sub EmailData(Datafile As String, To_mail As String, CC_mail As String, Subject_mail As String) 'code to use variables passed in End Sub
I am trying to set up some template emails using text someone has entered in a form with a variable indicated with a key word in brackets aka. [ChangeID] or [ChangeDate]. The field on the form is formatted as Rich Text so I am getting http code. (No problems yet) In the form the template is required, I lookup the template required and I get the string. I replace the brackets with the following
I have been tasked to create a multi-keyword search form, however, my form isn't working right and only the first record of the table is opened.
Code: Public Sub txtSearch_AfterUpdate() Dim strWhere As String Dim strWord As String Dim varKeywords As Variant Dim i As Integer Dim IngLen As Long
[Code] .....
These are the codes that I am using for my search form. I have a feeling that I am not calling the variable from the after update portion the right way.
I have a Change Management database with several fields, the key fields that I need to talk to work together are as follows:
[Patch] , [CVE] & [Patch Approved]
When opening a form I have I can sort it by the patch number which will then sort out all other CVE's that are not associated with that patch. What I would like to be able to do is approve one patch under the [Patch Approved] field and have it carried through the rest of the filtered CVE's that have the same [Patch] number/ field.
Public Function OdometerInput(varodometer As Variant) As Long Dim varKilometres As Variant varKilometres = varodometer * 1.609344 OdometerInput = CLng(varKilometres) End Function
It works fine in the immediate window (although I haven't just fathomed what to do with null values and such) But my question which I am sure will be 'easy when you know' is how do I pass the variable to it from a text box on a form and retrieve the data in another text box on a form.
I have a simple date stamp that works great in a private sub within a form. (error handling removed for clarity)
Code:
Private Sub btnDateStamp_Click() ' UserInit is global variable Me!Notes.SetFocus Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & UserInit & _ " -" & vbCrLf & Me!Notes
[code]...
I am rewriting it as Module function that is Called from various forms, to save space. The function receives the parameters varFormName and varControlName. I wish to write the results of the function back to a memo field on the form.I am stumped at the get go by the need to refer to the Forms controls with a full reference instead of the Me command.
Code:
Function DateStamp(varFormName As String, varMemoName As String) 'varInitials, varFormName, varMemoName are global variables Forms!varFormName.Controls!varMemoName.SetFocus ' Error here 'Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & varInitials & _
[code]...
how to refer to the forms control's with their full reference, from within the Module's function, the rest will fall into place.
I am fairly new to VBA and am trying to apply a filter within a form so that agent records can be filtered based on an agent's name (so that they can only see information that refers to them).
I also want a record to be removed when it has been completed (when this habbens a date completed field will populated) however I do want to see those completed records that refer to the agent that have been completed today.
Here is my code. The 2 filters work on their own howver when I put them together I get a mismatch error.
If Me.txtRole = "Agent" Then DateCompletedFilter = "(DATECOMPLETED Is Null) Or (DATECOMPLETED = Date())" AgentFilter = "CASEOWNER ='" & Me.txtName & "'" DoCmd.ApplyFilter , AgentFilter And DateCompletedFilter Exit Sub End If
I'm looking at importing data into a table from a spreadsheet. The spreadsheet will only contain a single column of data, while the table in Access will have a few more, some of which will would be will provided from the form the user is using to import the data, and some at a later date.
Is there a way to do this? I've found the "DoCmd.TransferSpreadsheet acImport" command, but not sure this fulfills what I need, as I can't see a way to set variable data.
Here is a brief idea of what my Table would look like, where where the data to import would be obtained from
I am trying to set a filter on a form with VBA using variables and having no success at all. The code I tried last, which seemed to be "close, but no cigar", was:
sLastSource and sBiller are global variables. When I debug this code the sFilterValue is exactly what I would plug in manually and the Me.Filter shows up as "[Field Name] = Filter Value". But it crashes on Me.Filter = sFilterValue. I have tried all sorts of combinations but nothing I've tried works. What is the proper syntax for using variables as filters?
I have never tried passing variables while calling a function so I don't know what the heck I'm doing. I'll give a simplified example of what I'm trying to do. The second variable vRank is reporting properly but the first one vID gets "stuck" on whatever the first item in the listbox is.
Code: Dim vrt As Variant Dim upSQL As String For vrt = 0 To Me.List1.ListCount - 1 If Me.List1.Selected(vrt) = True Then Call ChangeUp(Me.List1.Column(0, vrt), Me.List1.Column(1, vrt))
I am writing a process for a hospital application in the UK
We have a concept of "Takes" which are periods to which patients coming into hospital are assigned to and thus the consultant (specialist) who looks after them.
They are fixed times between 0800-2000 (Day take) and 2000-0800 (+1) Night take.
I have written the following function to try and determine which take a patient should be assigned to based on the time now to power various queries to enable the end user to quickly see "today's day take" and "yesterday's take"
Testing it I seem to not be able to hit the right "Take times" in my output. I suspect it's a problem with the way I'm initiating my variables as Today 0800 and Today 2000
Code: Function GetTakeStarttime() Dim StartTime As Long Dim EndTime As Long Dim CurrentTime As Long Dim YestStartTime As Long
I am using Access 2013 under Windows 7. In my database I have a table, tblStock, with field names Module, Component_1, Component_2, etc. up to Component_50. I also have fields Qty_1, Qty_2, etc. up to Qty_50. These field names are not easily changed as they are constantly updated from another database. I want to create a table, tblTempBOM, using VBA by selecting a particular value of Module selected from a combo box on a form, from tblStock and creating a record with the Fields "Module", "Component" and "Qty" for each Component and Qty from 1 to 50. I am trying to use a From... Next... loop to cycle through the Component and Qty fields and store the data in the new table.
Dim strModule As String Dim strSQL As String Dim strQty As String Dim strEye As String Dim strTest As String
[code]....
This runs to the point where I try to set the value of rcd![StockCode], but the item rcdStock![strTest] has no value.Can I reference the value of the field in the rcdStock Recordset in some other way that would work?
Look at the below SQL 'INSERT INTO' statement ? I'm trying to insert multiple variable values into an 'INSERT INTO' statement. I'm getting the below error message. The code is listed below. I started out with two (2) variables, but will have thirteen to insert into a table. Also, in the code below is the VBA statement to retrieve the variable data. I'm getting the data, but cannot insert the data into the table.
Private Sub Test2_Click() Dim strSQL As String Dim strSalesman As String Dim strContentArea As String DoCmd.SetWarnings False
[Code] ....
Error Microsoft Visual Basic popup Run-time error '3061'
I want to check to see if ALL textboxes are blank, then issue a message. My "Select Case" statement and "If" statement does not work. How can I accomplish this ? Below is what I have.
Blank = Null Select Case Blank Case strChartOfAccts1, strChartOfAccts2, strChartOfAccts3, strChartOfAccts4, strChartOfAccts5 & _
Debugging my VBA code. I'm trying to track/display the content/value of a couple of variables to make sure they have the right value at a certain point.
What is the VBA code to say "display content of variable Var1" ? Where exactly will it display the value when instruction is executed?
I know I can just hover the mouse on a variable to see its content but some string varables like SELECT commands are too long and you cannot see the whole string.
Is there any way of opening recordsets as public or global in a form? E.g. if i do something like
Set rs = db.OpenRecordset("TblCustomers", dbOpenSnapshot, dbReadOnly)
the variable rs should be available in all the private sub i have on that form, and i should be able to access records without opening database/ record sets for individual subs.
