# access全局变量_在MS Access中使用“全局”变量

access全局变量

As developers, we frequently need to store values for use throughout our application or to pass values from one object (form, report, module) to another.  There are a number of ways to do this within Access and I'll discuss several of the more common methods in this article.

Caveat Emptor: Before delving into this discussion, I want to address a common misconception that "global variables" are bad.  I strongly disagree with this view and regularly use them in my database applications.  The key is that you must be cautious about how you use these variables and understand that changing the variable in one spot will affect other aspects of your application.

For example: You use a global variable as a criteria in a recordset (say the RecordSource of a form).  While that form is open, you somehow navigate to another form or place in your code where the value of that variable is set to a new value.  When you return to the original form, the value of the variable has been reset, but the RecordSource of the form has not been re-queried, so the records that are displayed are not indicative of the current value of the variable.  For this reason, you must be cautious when using "global variables"

1.  Global Variables: I frequently encounter databases where the developer has chosen to use this technique and has simply defined one or more variables as global in a declaration statement.  This is by far the easiest way to define a variable which can be used throughout your application.  Simply define the variable in a code modules declaration section (as below).

1.全局变量：我经常遇到数据库，开发人员选择使用这种技术，并在声明语句中简单地将一个或多个变量定义为全局变量。 到目前为止，这是定义可在整个应用程序中使用的变量的最简单方法。 只需在代码模块声明部分中定义变量（如下所示）。

Global lngMyID as long


This variable can then be referenced in all code modules throughout your application.  Although this value can be set and evaluated from within any code module or the VBA immediate window, it cannot be used directly in a query or as a value in a control source of a control on a form.  However, you can overcome this drawback by defining a set of procedures to set and get the value of these variables:

Public Sub SetMyID (VariableValue as long)
lngMyID = VariableValue
End Sub
Public Function GetMyID() as Long
GetMyID = lngMyID
End Function


An additional drawback of this technique is that variables defined using this method will lose their values when an unhandled error is encountered in an application.  Regardless of how hard we try, most of us fail to include error handling in every procedure in our applications, and if an error occurs in one of these procedures, the variable will lose its value.  When this happens it can cause a myriad of problems that cascade through your application.  For this reason alone, I NEVER use this method.

2.  Public variables:  Public variables are similar to global variables, but are generally used specifically within a single object, whether that object is a form, report, or standard code module.  They have a scope which is generally limited to the object in which they are defined.  They are declared using the "public"  prefix rather than the "dim" prefix which is normally used to declare a variable in a code module.

2.公共变量：公共变量类似于全局变量，但通常专门用于单个对象中，无论该对象是表单，报表还是标准代码模块。 它们的范围通常仅限于定义它们的对象。 使用“公共”前缀而不是通常用于在代码模块中声明变量的“ dim”前缀声明它们。

Public myVariable as integer


One nice aspect of public variables is that they can be referenced from outside their scope by referring to the object (form, report, or module) in which the variable was declared, with a syntax similar to:

Forms("FormName").MyVariable = 3


This is a useful technique for passing a value from one form, maybe a popup form, back to its calling form.

These values cannot be used within a query and are also susceptible to losing their values when an unhandled error is encountered (did I mention how important it is to add error handling to your code).  I frequently place public variables in my form modules as they are very useful for passing multiple values between forms (both to and from).

3.  Storing variables in controls on a form:  A third method of storing variables which can be used throughout your application involves creating a form with numerous textboxes.  When I do this, I generally create a form (frm_Variables) specifically for this purpose and add an unbound textbox to the form for each of the variables I want to keep track of.  This is handy because I can simply open this form during application development, type values into these controls, and then run the query or open the form which depends on these values.

3.在表单上的控件中存储变量：可在整个应用程序中使用的第三种存储变量的方法涉及创建带有大量文本框的表单。 执行此操作时，通常会为此创建一个窗体（frm_Variables），并为要跟踪的每个变量在窗体中添加一个未绑定的文本框。 这很方便，因为我可以在应用程序开发期间简单地打开此表单，在这些控件中键入值，然后运行查询或打开依赖于这些值的表单。

The nice thing about this method is that you can refer directly to the controls on this form from anywhere in your application (queries, reports, forms, macros, and code modules), and they do not lose their values when an unhandled error is encountered.  The syntax for referring to these controls is similar to:

Forms("frm_Variables").txtMyID

or

Forms!frm_Variables.txtMyID

or, in a query as:

SELECT * FROM yourTable WHERE ID = [Forms]![frm_Variables].txtMyID


This is the method I used most frequently prior to the release of A2007, and which I still use occasionally.   I've found that when I use this technique in a query, it is helpful to define a parameter to ensure that Access/Jet correctly interprets the data type of the value.

4.  Tempvars:  When Access 2007 was released, one of the new features was tempvars.  Tempvars are a built-in collection which can contain up to 255 items.  As with any other collection, tempvars have Add, Remove, and RemoveAll methods and Count, Item, and Parent properties.

4. Tempvars： Access 2007发布时，tempvars是新功能之一。 Tempvar是一个内置集合，最多可以包含255个项目。 与其他任何集合一样，tempvar具有Add，Remove和RemoveAll方法以及Count，Item和Parent属性。

I like using TempVars because they do not lose their value when an unhandled error occurs and because they can be referred to directly from within queries. Additionally, I can set the value of a tempvar item in the immediate window and can immediately test the performance of a query, form, or report.

a.  Creating TempVars:  You can create a Tempvars in several ways:

(2)  tempvars!VarName = varValue

（2）tempvars！VarName = varValue

(3)  tempvars!VarName = forms!formname.Controlname.Value

（3）tempvars！VarName =表单！formname.Controlname.Value

Note: tempvars cannot save objects, they only save values, which is why the method shown in (3) above specifically uses the ".Value" property of the control.  If you fail to specify the ".Value" property of the control, this line of code will raise run-time error #32538: "Tempvars can only store data they cannot store objects"

b.  Retrieving TempVar values:  You can retrieve a value from the TempVars collection with the following syntax:

b。 检索TempVar值：您可以使用以下语法从TempVars集合中检索值：

(1) tempvars!VarName

（1）临时变量！

(2) tempvars(VarName)

（2）tempvars（VarName）

(3) [tempvars]![VarName]

（3）[tempvars]！[VarName]

Note: in order to use the TempVar directly in a query, you must use the syntax referred to in 4b(3).

c. Removing TempVars:  You can remove a TempVar from the collection with the following syntax:

C。 删除TempVar：您可以使用以下语法从集合中删除TempVar：

(1) tempvars.Remove VarName

（1）tempvars。删除变量名

d.  Tempvar values: You can even add TempVars to the VBE watch list if you want, but I generally just add a procedure to my applications to list the values of the current variables:

d。 Tempvar值：您甚至可以根据需要将TempVars添加到VBE监视列表，但是我通常只向我的应用程序添加一个过程以列出当前变量的值：

Public Sub ListTempvars()
Dim intLoop As Integer
For intLoop = 0 To TempVars.Count - 1
Debug.Print TempVars(intLoop).Name, TempVars(intLoop).Value
Next
End Sub


5.  OpenForm / OpenReport method OpenArgs argument:  You can also pass values into a form or report using the OpenArgs argument (the last argument of these methods).  This is a string argument and can pass multiple values separated by any delimiter.  When I do this, I generally pass the argument with a name, something like:

5. OpenForm / OpenReport方法OpenArgs参数：您还可以使用OpenArgs参数（这些方法的最后一个参数）将值传递到表单或报表中。 这是一个字符串参数，可以传递由任何定界符分隔的多个值。 执行此操作时，通常会给参数传递名称，例如：

strOpenArgs = "ClientID=" & me.cboClientID & ";ContactID=" & me.cboContactID
docmd.OpenForm "formname", acNormal, , , , acDialog, strOpenArgs


Then, in the Forms Load or Timer event, I will parse that value and retrieve the Client and ContactID values like:

Dim arrArgs() as string
Dim intLoop as integer
Dim lngClientID as long, lngContactID as long
arrArgs = Split(me.openargs, ";")
for intLoop = lbound(arrArgs) to ubound(arrArgs)
if instr(arrArgs(intLoop), "ClientID") <> 0 then
lngClientID = clng(Split(arrArgs(intLoop), "=")(1))
elseif instr(arrArgs(intLoop), "ContactID") <> 0 then
lngContactID = clng(Split(arrArgs(intLoop), "=")(1))
end if
Next


Unfortunately, you cannot pass values back to the calling form using this method.  So, as I mentioned above, I will generally either set the value of a public variable in the calling form or set the value of a tempvar variable.

6.  Persisting values across multiple sessions:  There are several other ways to store variable values which can be saved and recalled between application sessions:

6.跨多个会话持久存储值：还有其他几种存储变量值的方法，可以在应用程序会话之间进行保存和调用：

a.  Database properties:  I use the term "database" loosely here because although I generally only create properties for the currentdb, you can also create properties for other objects (forms, reports) within your application.  Before you can save a value as a database property, you must first create that property and assign it a data type and initial value.  You can do this in the immediate window, like below, or you can create your own procedure with code similar to this.

set db = currentdb
set prp = db.CreateProperty("MyID", dblong, 12345)
db.properties.append prp


After instantiating the property, you can then refer to the property with syntax similar to:

currentdb.properties("PropertyName") = 23
or
X = currentdb.properties("PropertyName")


One downside of using database properties is that uses will lose the values stored using this technique whenever you deploy a new application front end.  Another down-side is that you cannot use the above syntax to refer to one of these database properties in a query, so you will have to store this value using one of the methods mentioned above if you want to display the value in a query.

b.  Registry entries:  You can use the SaveSetting( ) and GetSetting( ) functions to save and retrieve settings from the HKCU (current user) section of the Windows registry.  This technique is useful because it allows you to save settings that persist across application sessions (I use it to store the left, top, height, and width of sizable forms to accommodate the sizes of user monitors) and which are also available when new versions of the application FE are deployed.  The syntax for these two functions are:

b。 注册表项：您可以使用SaveSetting（）和GetSetting（）   功能可从Windows注册表的HKCU（当前用户）部分保存和检索设置。 这项技术很有用，因为它允许您保存在应用程序会话中保持不变的设置（我使用它来存储可调整窗体的左侧，顶部，高度和宽度，以适应用户监视器的大小），并且在新版本中也可用部署应用程序FE。 这两个函数的语法为：

SaveSetting(AppName, Section, Key, Value)

SaveSetting（应用程序名称，部分，键，值）

GetSetting(AppName, Section, Key, Value)

GetSetting（应用程序名称，部分，键，值）

Note: All four of these arguments as strings, so if you are actually storing a numeric value (I do this with form positions), you will have to wrap the GetSetting( ) function call within a conversion function.

c.  Database table: You can also store these values in a database table (you are using a database after all).  If you choose to use a local table for this functionality, you must understand that you will lose those values when you deploy a new front-end.  If you choose to store these values in your database back-end, then you will need to include a field for the UserID, along with fields for VarName, VarType, and VarValue.  When I do this, I generally set the data type of each of those fields to a string, and then use either a Case statement or an If statement to convert the value from the VarValue field into the appropriate data type.  With this technique, you will need to include procedures to SetVarValue and GetVarValue.

C。 数据库表：您也可以将这些值存储在数据库表中（毕竟您正在使用数据库）。 如果选择使用本地表来实现此功能，则必须了解在部署新前端时将会丢失这些值。 如果选择将这些值存储在数据库后端，则需要包括UserID字段以及VarName，VarType和VarValue字段。 这样做时，通常将每个字段的数据类型设置为字符串，然后使用Case语句或If语句将值从VarValue字段转换为适当的数据类型。 使用这种技术，您将需要包括SetVarValue和GetVarValue的过程。

Dale Fye

access全局变量

• 0
点赞
• 0
评论
• 2
收藏
• 扫一扫，分享海报

10-12 1961

03-06 67
04-27 4418
12-21 158
07-18 3245