A way of creating a "constant" in VBA

[HenriM]

There are certain objects which, once created, remain constant and need to be called in more than one procedure.

The messy way of accessing these is to make a query and join the required tables; but this was making some of my procedures unnecessarily complicated and it was very easy to get the joins wrong. So I got to thinking and came up with the following:- Create:-

Function Myconstant(Object as (field type))
Dim Db,Dbs as Database
Dim rst as Recordset
Dim strSQL as String
Set Dbs=CurrentDB
StrSQL = "Select (ObjectName) from [Table containing the Object]"
set rst = dbs.OpenRecordset(strSQL)
With rst
Do until .Fields(Field Number) = Object
MoveNext
Loop
Myconstant = .Fields(Number)
.Close
End With
End Function

You can substitute .Fields(Number) with .(Name of the field) if you wish but I prefer numbers, you can do so much more with them.

The possibilities opene by this method are legion and all sorts of code can be used to extend its use.


Hope this helps someone - it has certainly made my life easier and my procedures simpler.