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.

作为开发人员,我们经常需要存储值以在整个应用程序中使用,或将值从一个对象(窗体,报表,模块)传递到另一个对象。 在Access中有许多方法可以做到这一点,我将在本文中讨论几种较常见的方法。

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.  

告诫Emptor:在深入讨论之前,我想解决一个普遍的误解,即“全局变量”是不好的。 我强烈不同意这种观点,并定期在我的数据库应用程序中使用它们。 关键是您必须谨慎使用这些变量,并了解在一处更改变量会影响应用程序的其他方面。

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"

例如:您将全局变量用作记录集中的条件(例如表单的RecordSource)。 打开该表单后,您将以某种方式导航到代码中该变量的值设置为新值的另一种表单或位置。 当您返回到原始窗体时,该变量的值已被重置,但是尚未重新查询该窗体的RecordSource,因此显示的记录并不表示该变量的当前值。 因此,使用“全局变量”时必须谨慎

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:

然后可以在整个应用程序的所有代码模块中引用此变量。 尽管可以从任何代码模块或VBA立即窗口中设置和评估该值,但不能将其直接用于查询或用作窗体上控件的控件源中的值。 但是,您可以通过定义一组过程来设置和获取这些变量的值来克服此缺点:

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.

这是我在A2007发行之前最常使用的方法,但仍然偶尔使用。 我发现在查询中使用此技术时,定义一个参数以确保Access / Jet正确解释值的数据类型会很有帮助。

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.

我喜欢使用TempVars,因为它们在发生未处理的错误时不会丢失其值,并且因为可以在查询中直接引用它们。 此外,我可以在立即窗口中设置tempvar项目的值,并可以立即测试查询,表单或报表的性能。

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

一个。 创建TempVar:您可以通过以下几种方式创建Tempvar:

         (1)  tempvars.Add VarName, varValue

(1)tempvars.Add VarName,varValue

         (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"

注意 :tempvar无法保存对象,它们仅保存值,这就是上面(3)中显示的方法专门使用控件的“ .Value”属性的原因。 如果您未能指定控件的“ .Value”属性,则此行代码将引发运行时错误#32538:“临时变量只能存储数据,而不能存储对象”

    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).

注意 :为了直接在查询中使用TempVar,必须使用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:

然后,在Forms Load或Timer事件中,我将解析该值并检索Client和ContactID值,例如:

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.       

不幸的是,您不能使用此方法将值传递回调用表单。 因此,如上所述,我通常将以调用形式设置公共变量的值或设置tempvar变量的值。

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.

一个。 数据库属性:在这里我宽松地使用术语“数据库”,因为尽管我通常只为currentdb创建属性,但是您也可以为应用程序中的其他对象(表单,报表)创建属性。 在将值另存为数据库属性之前,必须首先创建该属性并为其分配数据类型和初始值。 您可以在立即窗口中执行此操作,如下所示,也可以使用与此类似的代码创建自己的过程。

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.

注意 :所有这四个参数都是字符串,因此,如果您实际上存储的是数字值(我使用表单位置进行此操作),则必须将GetSetting()函数调用包装在转换函数中。

     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的过程。

I hope this article is helpful when you are contemplating how to store values for use within your database applications.

我希望本文在考虑如何存储在数据库应用程序中使用的值时会有所帮助。

Dale Fye

戴尔·费

翻译自: https://www.experts-exchange.com/articles/33220/Using-global-variables-in-MS-Access.html

access全局变量

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

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值