Here is the info I have on the topic compliments of mvp Dirk Goldgar.
One way to deal with this is to define it as a public property, by
creating a Property Get statement in a standard module, and having the
procedure return the literal value.
By creating a Property Get/Let/Set procedures in standard modules in
your database, you effectively create user-defined properties for your
database's VB project. And because they are public procedures, you can
refer to these properties via function expressions in controlsources if
you want. You can also use Property procedures to cope neatly with the
problem of global variables losing their values when the VB project is
reset (as, for example, due to an unhandled error).
Here's a simple example of what I was talking about. Consider wayne's
desire to have a global variable that returns the CR/LF combination.
Yes, I know there's already a defined VB constant for that -- two, in
fact -- but suppose there weren't? He could do as someone esle suggested
and
define a global variable, then use some event to set its value:
' In a standard module's Declarations section:
Public CarRet As String
' In some event:
CarRet = Chr(13) & Chr(10)
But (a) you have to rely on that event to run, and (b) there's the risk
of an unhandled error causing the VB project to be reset and the
assigned value to be lost.
Instead, you could define a public Property Get procedure in a standard
module:
Public Property Get CarRet() As String
CarRet = Chr(13) & Chr(10)
End Property
Then anywhere you want to use a carriage return/line feed combination,
you can use this property; e.g.,
strFullAddress = _
strAddr1 & CarRet & strAddr2 & CarRet & strCityStateZip
Here's a more complex example. Suppose you have a configuration
value -- say, LicenseeName -- that is frequently referred to in your
app. You want to store this value in a table, but you don't want the
cost of doing a DLookup every time you need it. You could write a pair
of Property Get and Let procedures for this value:
'----- start of module code -----
Dim mvarLicenseeName As Variant ' private, defined at module level
Property Let LicenseeName(pstrNewValue As String)
If Len(pstrNewValue) = 0 Then
Err.Raise 380 ' Invalid property value
Else
mvarLicenseeName = pstrNewValue
CurrentDb.Execute _
"Update tblConfiguration Set LicenseeName = " & _
Chr(34) & mvarLicenseeName & Chr(34),
dbFailOnError
End If
End Property
Property Get LicenseeName() As String
' Lookup the value if it isn't in memory.
If IsEmpty(mvarLicenseeName) Then
mvarLicenseeName = DLookup("LicenseeName", "tblConfiguration")
End If
LicenseeName = mvarLicenseeName
End Property
'----- end of module code -----
Now you can have code in various places like this:
Me!lblLicenseeName.Caption = LicenseeName
and
LicenseeName = Me!txtEnterNewLicenseeName
and a controlsource for a text box like this:
=LicenseeName()
So then if I have a table for default values such as
tblDefaultValue
txtDefaultValueName
txtDefaultValue
txtDefaultValueType
I could use LET/GET to set the default value ONCE and forgo doing
multiple DLookups() each time I used the default value. Then if I update
a value I would simply do what? call the LET function to change the
value in memory?
Right.
You wouldn't have to explicitly call the Let function; just assigning
to the property would do that. Then, in your Property Let procedure, it
would be up to you and your code whether you just wanted to change the
in-memory value, or whether you wanted to change the in-memory value
*and* update the corresponding value in tblDefaultValue.
Dirk Goldgar, MS Access MVP
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australis
