Modules & VBA :: Define Global / Public Variables From A Table?
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.
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.
I have a table with only two fields and one record: BegDate and EndDate (beginning and end date of the reporting period respectively). I also made a function with variables that look up those values for use as a date parameter in a query.
Here is the code:
Option Compare Database Option Explicit Public Function getCurrentRepDates() As Date Dim dtBegDate As Date dtBegDate = DLookup("BegDate", "tblCurrentRepDates")
[Code] ....
I am getting a syntax error for the line marked red. How can I use "Between" function in VBA code? Access 2010
I have an access form called forma. It has a command button. When I click a command button, FormB opens up and FormA closes. The FormB also has a command button. When I click the command button of Form B, it closes and opens FormC. Like this there are total 30 forms. What I need is. I want to count the no. of clicks of the command button in total. Actually each button is a correct option of A multiple choice question. I tried to declare a public Variable called total in the declareation of the first form and tried to use it directly in the succeding forms. It does not capture the values. How can I increase the value of the varialbe declared in the first form even after the form closes and capture till end. Actually I also should be able to send the value in a table. Please Help Me.!
Anyone know how to reference a global variable as criteria for a query?
I am currently using invisible text boxes on an intermediary form as criteria for my query and it works fine, but this solution seems inellegant and I'm looking for something a little smoother. I just need the correct syntax to reference a variable in the criteria section of a query.
i have a form (frm1) and subform (sfrm1) that when a room number is double-clicked in the subform another form (frm2) and subform (sfrm2) loads. frm1 has building info and frm2 is used for inspection info. if a room has never been inspected then when frm2 loads the field [room] will be empty. however, after the first inspection the room number is obviously entered for frm2.
so i have created a global variable that holds the value of [lab_room] from frm1 and is supposed to use this value on frm2 if the room has never been inspected - ie, [room] is null. however, the value of intRoom (global variable) is lost after is leaves frm1 and goes to frm2. below is my code:
modGlobal (module with global variables declared) Option Compare Database
Global intVar As Variant Global intRoom As String
Private Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid) As Long
Private Type GUID 'Memory structure used by CoCreateGuid Data1 As Long Data2 As Integer Data3 As Integer Data4(7) As Byte End Type
Private Const S_OK = 0 'Return value from CoCreateGuid
frm1 subform double click event Private Sub Form_DblClick(Cancel As Integer) On Error GoTo Err_DblClick
Dim stDocName As String Dim stLinkCriteria As String Dim stMsg As String
'if the user double clicks on an area that has no lab_id associated with it 'prevents a null value for lab_id If IsNull(Me!lab_id) Then stMsg = "There is no lab associated with this room." intRes = MsgBox(stMsg, vbOKOnly + vbExclamation, "Error - No Lab Selected") GoTo Exit_DblClick End If
I have a number of global variables set when a user logs into my application which i want to use in an SQL string to record these in a table however im not sure how i go about it i know simply inserting the defined names in the SQL wont work.
Is there a way to define these in global functions instead?
I'm into one of the subforms that will be using the public variable from the main form and am not having success passing the data from the main form over. on the subform, i created a textbox (txt_currentyear) w/ this in the control source field: =[WrkYear] & " Golf Outing"
WrkYear was the defined in a module as: ********** Option Compare Database Option Explicit
Public WrkYear As String **********
I have created a listbox w/ a few years in it (current_year_listbox). this has =[WrkYear]=Me.Current_Year_listbox.Value in the after update field. i loaded the main form, selected the year, went into the subform to see if it passed the data along w/ no luck.
do i need to call or reference that module in every form or report before i can use the data from it? right now, i just get an empty field on the subform.
It just occurred to me that I use certain variables in several of my routines. Maybe you've done the same... something like:
Dim db as DAO.Database Dim rst as DAO.recordset Dim qdf as DAO.QueryDef
Now, if it's re-used in many routines, wouldn't it make more sense to make it a public variable, and set them to whatever you need it for a certain routine? Would it be more efficient than having to re-initializing and discarding it for every routine that used it? What problem would one expect from making them public?
Also, since it's very common to set db = CurrentDb, would it also make sense to set it publicly, and for cases where you aren't using CurrentDb, use a private variable just for that one?
Hi all, I seem to have encountered a strange problem at work.
I declared a public variable for user department. The user selects his department from a Log On form and if the password is correct, he opens another form with the department variable passed on in openargs.
All of this works fine in access2k but upon testing with 2003, the openargs somehow do not seem to be being passed on. Maybe I am being dense somewhere but it is kinda puzzling for me.
I am not using access's security for users and groups because I am trying to reduce the complexity of the whole project (I wont be around to maintain it next time).
This table holds variables that I want to declare to use throughout my application. I have been told in another thread that tempVars are the best way to do this.
I have written the following code, which works on a limited basis:
Private Sub btnSetVAr_click() TempVars.Add "udvVar", Me!My_value.Value End Sub
This defines a single variable on each button press, fine to work out how the code works, but not much use. What I really need to do is when the initial menu screen loads to call a routine to assign all the variables stored in the table using a loop to do this. The idea is to make all variables values easy to edit or add to, rather than have to edit code each time we need to change them.
The variables table holds 14 records so far, such as:
My_Var, My_Value
EuroRate, 0.885 ConDisc, 0.9 MollDisc, 0.8
As well as holding numbers, they hold strings and date values.
So, on loading the initial form, use an event to assign the variables from the table using the tempVar name as the value held in "My_Var" and it's value as held in "My_Value"
Access 2010 vba - I'm trying to pass a start date and end date to a date field in a make table query, and use the 'between' operator on that date field.
So I have a criteria on the date field like this "Between [dtStart] and [dtEnd]" and if I run the query manually it asks for 2 values and then works fine.
Here's the code I'm trying to run:-
Set qdef = db.QueryDefs("qryTest") qdef.Parameters("dtStart") = StartDate1 qdef.Parameters("dtEnd") = EndDate1 Set rs1 = qdef.OpenRecordset(dbOpenDynaset, dbSeeChanges)
and I get the error "3219 Invalid Operation" on the last line.
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")
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?
I have one color scheme I want to use all through the database I am developing. The next examples have just one color defined, to make it simpler (a dark blue, that I would call B1)
Code: private sub setlabel() Dim B1 B1 = RGB (0,52,105) me.label1.forecolor = B1 end sub
... however this means I have to repeat the color definition every sub, so I thought would be neater to define a function to set my color codes (I have 20 colors).
Code:
Function SetColor() Dim B1 B1 = RGB (0,52,105) End function
My objective, is when I'm working in forms, Iwould (ideally) call this function "setcolor" and just write my code for the blue. I tried the examples below:
Code: Private sub setlabel() SetColor() me.label1.forecolor = B1 end sub
[code]...
Again, this is probably some definition of arguments or dimensions that I am not aware oh. How to predefine the colors in a function to give them a "short" code which I can call in any sub in the database?
I have a multi-user database. I would like based on the logged-in user, some buttons and controls in different forms be disabled and the others be enabled.
There is a login form that gets the username; I made also a function to define authorities for users in it.
My plan is that when user enters to the database, on load of the main menu the function calls and disables the defined controls in different forms. I tried to use this kind of codes:
Function User_1() [Forms]![frmMainMenu].[cmdUpdateDatabase].Enabled = False [Forms]![frmChooseReports]![cmdOrdersFollowUpReports].Enabled = False End Function
Function User_2() [Forms]![frmMainMenu].[cmdChooseCharts].Enabled = False End Function
But this code only works for the forms that are opened at the moment and if one of them is closed the system gives Error.
I am trying to define a path to identify a current folder - I'll try and explain the way the following code works first (and it does work 100%)
The following code identifies folders and sub-folder structures and imports them (their structures and folder paths and filenames) into a DB
It also simultaneously retrieve's any xml docs within those respective folders and imports the XML data into the same database ........ and then moves those folders into a "processing folder" location.
Cool yes, but I can only import the XML doc's at the moment, by hard coding the path (like this):-
Code: Select distinct [tbl_DTP/CTP].id_zlecenia, [tbl_DTP/CTP].Folia_na_lakier_UV, [tbl_DTP/CTP].Wykrojnik, [tbl_DTP/CTP].Makieta, [tbl_DTP/CTP].Matryca_do_tloczenia, [tbl_DTP/CTP].Matryca_do_zlocenia, [tbl_DTP/CTP].kalka, tblGoraZleceniaRoboczeLaczenie.NumerZlecenia from [tbl_DTP/CTP] inner join tblGoraZleceniaRoboczeLaczenie on [tbl_DTP/CTP].id_zlecenia = tblGoraZleceniaRoboczeLaczenie.NumerZlecenia where tblGoraZleceniaRoboczeLaczenie.NumerZlecenia = r1;
Where "r1" is a public variable as string.The variable has value e.g. "10/145" But query can't get this value and all the time ask about value from "r1" :/
Is it possible to open another database, set a Public boolean variable in a module?
in db1: Open database by Access.Application, OpenCurrentDatabase, setting obj to db2.CurrentProject, loop through main objects
in db2: Public ByPassCloseVar As Boolean is in a module ModLinkTblReview
I have a process that opens up a database runs through all of its objects to get their properties and values. There is a form that is set up on Unload to close the tool if the variable is set to False. When I open the form to get the properties and their values, then close the form, it closes the database. I would like to set the variable to true in order for the database to stay open.
Thought something like this would work: db2.Modules!ModLinkTblReview.ByPassCloseVar = True
Indicates that method or data member does not exist.
I am trying to read the value of a control on an open form from a Public Function in a Module with no luck. I suspect the issue is syntax.
Code:
Public Function MyFunction(varFormName As String, varControlName As String) If Forms(varFormName).Controls(varControlName) = True Then ' = 0 even when control is -1 Else End If End Function
The variables pass through fine but the If statement equals zero regardless of the state of the form's control. I tried several variations to no avail.
If Forms(varFormName).Form.Controls(varContactControl ) = True Then If Forms(varFormName)!Form.Controls(varContactControl ) = True Then Etc.
I have an issue with this case select below. The DelayStart is time so lets say I put in the debug window
?DatePart("h", #04:00pm#)
The result would be 16 which is correct BUT the issue is my second shift starts at 04:01pm and the result is still 16 untill 5pm. How do I fix this so I get the correct shifts? Im guesing use something other than DatePart but what?
Code: Public Function getShiftForRecord(DelayStart As Variant) On Error Resume Next Select Case DatePart("h", DelayStart) Case 6 To 16 'Daylight 6:00am - 4:00pm
I have a module that has a global variable defined as shown below
Module: initGlobals -----------------------
Code: Option Compare Database Option Explicit Public strGlobalUserId As String Sub initvar() strGlobalUserId = "Myuserid" End Sub
This is invoked in a login form("frmLogin") and it displays the above value before I replace it with the one that is accepted in the form.
Here is the code from the login form
Code: strGlobalUserId = Me.cboEmployee.Value
When the password is successful, I close the login form and open a form called "frmMainForm". In the form_open event of the "frmMainForm", I am trying to display the user id using strGlobalUserId but it just has spaces. What am I doing wrong?
I have a backup subroutine which automatically triggers when the front-end is closed down (it just takes the back-end, makes a copy and compacts it).It's driven off a hidden form which I use to track who is connected to the BE at any given time. This form is opened as part of the AutoExec when the FE is opened and writes some basic user info to a table. When the form is closed, it updates the table and fires the backup process before quitting Access.
Part of that user tracking process checks to see if the same user is already connected - either elsewhere (i.e. on a different machine) or on the same machine (i.e. opening a second instance of the same FE) - which is undesirable (and, frankly, unlikely, but not impossible) A brief prompt appears to explain that they can only be connected once, at which point the application is quit (to enforce the rule) This also works fine.
I would add a public boolean variable, set it to True by default, then switch it to False if the same user is already logged on before quitting Access.Trouble is, the variable doesn't seem to be holding its value? Here is the Load event for the tracking form :
Code: Private Sub Form_Load()
On Error GoTo ErrorHandler
Dim dbs As Database Dim rst As Recordset Dim strSQL As String Call InterfaceInitialise blnPerformBackup = True
[code]...
For some reason, blnPerformBackup is False every time, even though I set it to True at the very start of the Load event (and it is a Public variable, defined in a separate module where I store all my Public constants and variables)
why it is not retaining its value from the Load event? I've checked and it does get set to True - and when I debug, it remains True - but at runtime it reverts back to False by the time it reaches the decision whether to backup or not?
Is it possible (and how...) to declare a module-specific form variable (or any variable for that matter) at the top of said module, so it doesn't need to be set at the start of each subsequent procedure?
I have a module of code specific to one form with a number of procedures, each one of which requires me to Dim / Set the form variable. It would be much neater if I could do it once at the start.
Code: Option Compare Database Option Explicit Public Sub Populate(lngParameter As Long)Dim frm As Form
I have built an access application that contains a set of buttons along the top of every form that serve as navigation.* These buttons each perform the same function on every form they are on. (menu opens the main menu, etc) I have database macros to assign each button the same function but I still have to go through each form and manually assign them. I was wondering if it was possible to define a public function that on db open will look for all buttons with a certain name and assign them the macro. (so all buttons called cmdmainmenu will have the OpenMainMenu macro assigned and so on).Before you go there, I have already tried the navigation form and set all forms as subforms.
