Modules & VBA :: When To Use Global Variable
Apr 8, 2014
I have an application where several different procedures are run repeatedly every xx seconds to get live updates from a server (different procedures depending which form the user currently has open.) Within each procedure, a separate single procedure is called only if a stored boolean setting is set as true. The setting itself is only changed very infrequently, such as on application startup, but needs to be initially set (i.e. it cannot just have a default of False if it is not set.)Because of this I am storing the setting in a single record table and have a function 'UsingWPilot' to return the boolean value. However, given the frequency that the setting needs to be accessed, and the fact that speed is crucial in the running of the app, I am considering adding a global string variable and modifying the function to look at the variable (possible values 'N' and 'Y') and only going to the table if the variable has not been set.
I have heard various things about how you should never if possible use global variables. My question is: is it faster to use a global variable than to open the table record each time?
option 1 :
Code:
Public Function UsingWPilot() As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("rbl_Settings_System")
Option 2:
Code:
Public Function UsingWPilot() As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
If Len(g_WPilot) > 0 Then
UsingWPilot = (g_WPilot = "Y")
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Settings_System")
[code]....
View Replies
ADVERTISEMENT
Apr 16, 2015
I want to use several values entered in form controls as variables within multiple subs triggered by further form edits. I do not want to define the variables in each sub as this will bloat my code, but I am not being successful in declaring my variables outside of an individual sub, and it is the 'Form!' reference that is throwing it I think.
Here are my variables:
Code:
Dim limit0txt, limit1txt, limit2txt, limit3txt As Integer
limit0txt = Forms!F_Samples_CF_FF_FLot!SF_EnviroCountLimitscntrl.Form!Limit0
limit1txt = Forms!F_Samples_CF_FF_FLot!SF_EnviroCountLimitscntrl.Form!Limit1
limit2txt = Forms!F_Samples_CF_FF_FLot!SF_EnviroCountLimitscntrl.Form!Limit2
limit3txt = Forms!F_Samples_CF_FF_FLot!SF_EnviroCountLimitscntrl.Form!Limit3
If I put these at the top of a module I get an "invalid outside procedure" error message. If I put these in their own module as 'Public' I get the same error. how within a forms code module I can make these variables available to as many events as I like?
View 6 Replies
View Related
Sep 23, 2005
I have 3 different forms, each belonging to 3 different tables. The user is required to enter data on form 1 then form 2 & then form 3. The records on all the 3 forms has to be attached to the same primary key.
The problem is that the user has to enter the same primary key for each form.
I want to be able to carry the primary key entered on form 1 to form 2 and form 3, so that the user does not have to type it 3 times. How can i accomplish this ? Can i call the last primary key entered on table 1 and automatically pop it up on form 2 & 3 every time form 2 & 3 load?
Thanks
View 2 Replies
View Related
May 18, 2005
I am trying to use a global variable in a dlookup but get an error when i try and run the form
Here is the code:
VUserID is my Global Variable.
Private Sub Form_Open(Cancel As Integer)
Dim UsersName As String
Me.WelcomeBox = UsersName
UsersName = DLookup("[Name]", "tbl Password", "[username] = vUserID")
End Sub
Any Ideas
View 1 Replies
View Related
Oct 2, 2006
Got a problem trying to reference a global variable in the control source of a text box.
If i do a DLookup when the form opens and have the text box value = the result it works fine ie
Private Sub Form_Open(Cancel As Integer)
Text2.Value = DLookup("UserName", "Users", "[UserID]= " & MyUserID)
End Sub
Where MyUserID is the global variable.
if i put
= DLookup("UserName", "Users", "[UserID]= " & MyUserID)
in the control source of Text2 i get #Name? as a result and if i input
= DLookup("UserName", "Users", "[UserID]= 1")
i get the correct result.
As a test i tried to have the control source = my global variable, of another text box control source with the same result.
It looks like i can only reference my global variable within the VB and not in the forms control sources. Is this correct or can you referece the Global variable in the control source.
Shot
View 6 Replies
View Related
Dec 15, 2004
i am having trouble with my where clause.. ca someone take a peek please..
Note: gID is defined globally as integer and tblCheckReq.ID is defined as an auto-number (in table). Thanks in advance!
SELECT tblCheckReq.UserName, tblUsers.UserDept, tblCheckReq.PaymentMethod, tblCheckReq.PaymentDueDate, TRIM(tblCheckReq.Company & " " & tblCheckReq.TaxType & " " & tblCheckReq.PaymentType) AS DISTDETAIL, tblCheckReq.TaxYear, tblStateTaxDepts.To, tblStateTaxDepts.Street, Trim(tblStateTaxDepts.City & ", " & tblStateTaxDepts.State & " " & tblStateTaxDepts.Zip) AS FULLADDR, tblCheckReqAmtsToAccts.GLAccountNo, tblCheckReqAmtsToAccts.Amount, tblCheckReq.ID, tblCheckReq.Approvedby, tblUsers.SalaryCode, tblCheckReq.Date, tblStateTaxDepts.Dept, tblStateTaxDepts.To, tblCheckReq.TaxYear, tblUsers_1.SalaryCode, tblCheckReq.Company, tblCheckReq.TaxType, tblCheckReq.PaymentType
FROM (((tblCheckReq INNER JOIN tblCheckReqAmtsToAccts ON tblCheckReq.ID=tblCheckReqAmtsToAccts.ID) INNER JOIN tblUsers ON tblCheckReq.UserName=tblUsers.UserName) INNER JOIN tblStateTaxDepts ON (tblCheckReq.TaxType=tblStateTaxDepts.TaxType) AND (tblCheckReq.State=tblStateTaxDepts.StateAbrv)) INNER JOIN tblUsers AS tblUsers_1 ON tblCheckReq.Approvedby=tblUsers_1.UserName
WHERE (((tblCheckReq.ID)='" & gID & "'));
View 4 Replies
View Related
Aug 9, 2005
Hello,
I have the following query that I set up as a test, and it runs fine:
SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID = PROBLEM_DE.PROBLEMNR
WHERE (((STATUSHISTORIE.STATUSDATUM)<#1/1/2005#) AND ((PROBLEM_DE.DATENBEREICH)='SPMO') AND (((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))='K29') AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY STATUSHISTORIE.PROBLEM_ID,
STATUSHISTORIE.STATUSDATUM;
I then set up two global variables ( a String and a Date) and respective functions to return them – ReturnE( ) and ReturnKW( ). Now my query looks like this, but takes ages to run:
SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON [STATUSHISTORIE].[PROBLEM_ID]=[ PROBLEM_DE].[PROBLEMNR]
WHERE (((STATUSHISTORIE.STATUSDATUM)<ReturnKW( ) ) AND ((PROBLEM_DE.DATENBEREICH)='SPMO') AND (((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))=ReturnE( ) ) AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY [STATUSHISTORIE].[PROBLEM_ID], [STATUSHISTORIE].[STATUSDATUM];
My two public functions that return the global variables look like this:
Public gstrE As String 'global variable: contains E used for query
Public gdatKW As Date
Public Function ReturnE ()
ReturnE = gstrE
End Function
Public Function ReturnKW ()
ReturnKW = gdatKW
End Function
The tables are actually Views set up from an ODBC Data source. Can anyone please tell me why these global variables are causing the traffic jam? :)
Thanks in advance
J
View 6 Replies
View Related
Mar 15, 2006
Hi All,
Can anybody tell me whether it is possible to use a global variable defined in a vb module as part of the criterea in a query, if not whether there is a work around.
I suppose I could temporarily write the data to a table and use it from there, I thought there would be less messing around this way though.
Cheers
View 2 Replies
View Related
Feb 3, 2005
i noticed that if access is open for a long time
the globals return null
can i set the time of a global variable?
View 1 Replies
View Related
Feb 6, 2008
I have a variable declared in a module as such
Code:Dim tracking_customer As Double
When i use one form to set the variable like
Code:Private Sub Command83_Click()'Set the global variable to this customer number'So it can be called in "ON OPEN" event in URL formtracking_customer = Me![Customer Number]'gets expected number in following lineMsgBox ("Trackign is " & tracking_customer) DoCmd.OpenForm "Url Tracking", acNormalEnd Sub
Now when I open that next form I try grabbing the newly set module variable but I always get the number 0. So I am assuming it loses the number once I change forms for some reason
Code:Dim trackID As DoublePrivate Sub Form_Open(Cancel As Integer)trackID = tracking_customer'Following line always produces 0MsgBox ("hey this is tracking customer number " & trackID)End Sub
Can someone explain this, and let me know what I am doing wrong please.
View 4 Replies
View Related
Oct 21, 2013
I have this declaration in a module called Global Code
Option Compare Database
Public currentCustomerId As Long
Option Explicit
There is one report that simply prints a the firstname of current customer and the amount of each of his invoices.
The current displayed customer's ID is always copied into the variable currentCustomerId.
Below is the SELECT code of the data source of the report.
But whenever I open this report it asks for currentCustomerId.
Is my decalartion not global enough?
SELECT tblCustomers.FirstName, tblInvoice.Amount, FROM tblCustomers INNER JOIN tblInvoices ON tblCustomers.CustomerID=tblInvoices.CustomerID WHERE tblInvoices.CustomerID=currentCustomerId;
Note: I chose to save the current customer's ID in a global variable because, for some reason, when I select the report the Customer form loses the current customer record and goes to the end of the table as if creating a new customer record. This would not be an issue as long as I can retrieve the value in a variable.
View 4 Replies
View Related
Nov 13, 2013
I have a form with up to 100 buttons generated based on a number of conditions. I am trying to pass the caption of a button to a sub so that I can open the correct forms. When a button is clicked how can I read it's caption. Me.caption is reading the form caption.
View 7 Replies
View Related
Apr 22, 2015
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.
View 6 Replies
View Related
Sep 4, 2013
I would like to declare Global or Public variables from a table so they can be added to or edited easily. I had the following function to do this with Alpha Five but at the moment my lack of knowledge of Access VBA is making this task difficult.
Here is the function that I use in Alpha:
FUNCTION udVars AS A (udTabName AS C )
dim codeStr as c
t=table.open_session(udTabName,file_ro_shared)
t.fetch_first()
while .not. t.fetch_eof()
[Code] ....
It opens a table reads in the records and then makes them into a string the string would look something like this:
"Public EuroRate as Single = 0.885"
I would then need to use this string to declare the variables but not sure what command to use - I was looking at the Eval function last night but couldn't get this to work...
The table would hold strings for all the above so you would have:
My_Var (variable name)
My_Typ (type of variable e.g. Single)
My_Val (value e.g. 0.885)
The table would hold as many variables as required, some would be dates, some paths for making directories and some would be numbers.
View 6 Replies
View Related
Jul 8, 2013
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
View 14 Replies
View Related
Oct 3, 2014
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer
LR = 5
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wbRef = xlApp.Workbooks.Add
With wbRef
wbRef.Activate
.Worksheets("Sheet1").Activate
With ActiveSheet
.Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
end With
View 3 Replies
View Related
Aug 20, 2014
I look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..
Code:
Public fDate As Variant
Public vField As String
Public vFile As String
'GTSdata
vField = "txt_gts_data"
[Code] ....
What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate
What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .
Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)
How do I say update the contents of the variable, not the variable itself?
View 7 Replies
View Related
Aug 26, 2014
If I set a variable inside of a subroutine, it is set to nothing upon the end of the sub. Can I set a variable outside a sub and set its value, so that you can use it within subs?
View 14 Replies
View Related
May 12, 2014
How can I define a variable which can be used by another Sub and of course the value stored in it?For instance:
Private SUB A ()
DIM A1 as String
A1 ="ABC"
END SUB
PRIVATE SUB B()
PRINT A1
END SUB
View 5 Replies
View Related
Nov 6, 2013
I am just in the middle of writting a little bit of code that will go through each record in a query and export it into a word table. It's in the early stages but all was working yesterday. I've come to it this morning and ran it and now I get an error message;Run Time error 91: Object Variable or With Block Variable not set.
Code:
Private Sub Command15_Click()
Dim MyDb As DAO.Database
Dim rsLogin As DAO.Recordset
Dim ObjHead As String
Set MyDb = CurrentDb()
Set rsLogin = MyDb.OpenRecordset("query here")
[code]....
View 2 Replies
View Related
Nov 6, 2013
I have some code that will allow a user to pass to it the name of a report. The code will do various things to that report before presenting it for Preview, opening in Word etc.
At one point in the code I would like to refer to some control or property of the report. If I was to include this directly for one report, I might type:
Reports!Annual_Budget_Report.Recordsource = xyz
But instead of a specifc report, it would be the report as a variable (srReport_Name). However, if I type:
Reports!stReport_Name.Recordsource
The code complains, rightly, that there isn't a report called stReport_Name. The stReport_Name variable is currently set as String. I *think* the answer might lie in the variable type, but I'm still struggling.
View 3 Replies
View Related
Sep 19, 2013
I have a form in which am gathering information from the user to populate a table(Customer Master List) and at the same time (btn_Copy_Click Event)transferring the info to another form(Case) populating another table(Customer_Call). It is working fairly well, My issue is that before transferring the info I need to run a check(SQL Query) to make sure the customer or actually the Well ID don't exist in the Customer_Call table. I am trying to do this in pieces .....
1) capturing the well_Id in a variable(WellID) in the first form and using that to build the sql string and query the Customer_Call table.
2)once that works place it in a if / else clause to copy or not with appropriate messages
With that I am stuck in step 1
It works up until Set rst = CurrentDb.OpenRecordset(strSQL) the i get Run-time error '3061' Too few parameters. Expected 1.
Code:
Dim WellID As String
Dim strModel As String
Dim strSQL As String
Dim rst As DAO.Recordset
WellID = Forms!f_Customer_Lookup.Well_ID
MsgBox WellID ' testing to see if it picks up the correct box in form
strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _
"FROM Customer_Call " & _
"WHERE Customer_Call.[Cus_Well_ID] = WellID;"
Set rst = CurrentDb.OpenRecordset(strSQL)
strModel = rst!Cus_Well_ID
rst.Close
MsgBox rst ' Testing to see if the strSQL captured the data
Set rst = Nothing
End Sub
View 8 Replies
View Related
Jan 8, 2015
I use a macro that attaches a document automatically to an e-mail address ready to send this report is governed by a query of which the criteria is " loss order number " this is in a pdf format of which I need if possible
The problem I have is the same pdf file keeps attaching itself to the e-mail no matter which loss order number I select. The reason I think is because the report is called NCR so it is not changing from the previous one. What I think can solve it but not sure and don't know how to do is get the " loss order number " within the report name.
If I just send the report to a file direct it does work as I am prompted for a file name
Can the loss order number get into the file name within a macro/vba
Code:
Name="EMailDatabaseObject
Name="ObjectType">Report
Name="ObjectName">SEND NCR
Name="OutputFormat">PDF Format (*.pdf
Name="To">=[E-mail Address] & IIf(Nz([E-mail Address
View 2 Replies
View Related
Jan 21, 2015
I have the following simple variable assignment...
Dim PolRef As String
Let PolRef = Me.Policy_Ref_Num
But when I run my code I get "Invalid Use of Null" and PolRef is set to ""
This field is populated however, so I'm confused to why I cant set my variable to it.
If I don't use a variable and simply have let XXX = me.Me.Policy_Ref_Num then it still wont return the value from the field.
View 5 Replies
View Related
Mar 15, 2015
I want to get the output of a vba query (only one solution possible) in to a variable but the variable stays empty.
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT info FROM evaluationtable WHERE evaluation= " & evaluationchoice & " ")
var = rst(0).Value
rst.Close
some explination: evaluation and info are fields of evaluationtable evaluationchoice is a field in an accessform where I can choice a value from the evaluation field
the table is build as this (only two fields) evaluation - info
View 5 Replies
View Related
Aug 18, 2013
Why does this work when the text box is used and not when the variable is used directly?
Code:
Private Sub Command61_Click()
Dim strDBName As String
strDBName = getDBName()
Me.Text59 = strDBName
Me.Text62 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = text59")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = " & strDBName)
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = strDBName")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName]" = strDBName)
End Sub
Text62 returns the correct value
Text64 failes on everyone of the examples
View 7 Replies
View Related