Let's see if I can explain what I want here.
I have a program that stores user settings in an access database table. When the program loads, all of the user settings are stored into variables by being read from the database. i.e. Below is how the settings are called- I also included the GetStoreSettings procedure below. I know there must be a faster way to retrieve the settings on a database end instead of opening a recordset... Secondly- is there a way to quesry the database to assign the values to the variables all at once? The variable name is the same as the name stored in the datbase. Let me know if clarification is needed. Thanls in advance!
BName = GetStoreSettings("BName")
BLocation = GetStoreSettings("BLocation")
BAddress = GetStoreSettings("BAddress")
BAddress2 = GetStoreSettings("BAddress2")
BCity = GetStoreSettings("BCity")
....And on and on and on
Public Function GetStoreSettings(WhatToGet As String) As Variant
On Error GoTo FixIt:
Dim TriedOnce As Boolean
Dim TranDet As New ADODB.Recordset
Dim strSQL As String
ADOConTransfer = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatSource & ";Persist Security Info=False"
strSQL = "Select * FROM StoreSettings WHERE (StoreSettings.Key)='" & WhatToGet & "' AND (StoreSettings.Reg)=" & RegNum
TranDet.Open strSQL, ADOConTransfer, adOpenStatic, adLockReadOnly
If TranDet.RecordCount <> 0 Then
GetStoreSettings = TranDet!value
GetStoreSettings = CheckForDefault(WhatToGet)
If Err <> 0 Then
If RegNum = "" And TriedOnce = False Then
RegNum = 1
TriedOnce = True
MsgBox "Error retrieving store setting- Key: " & WhatToGet