UtterAccess Discussion Forums > Defining Variables 101

SixtyBucks

Apr 28 2012, 01:02 AM

How do I assign values to my public variables based on a single record in a table?

I have a table called "UserSettings" containing a list of user defined variables. There are two fields in the table: one called "VariableName" and the other called "VariableValue".

Three examples of user defined variables listed in the table are stImportCriteria, stClubName, and dblMarkup.

I have declared these variables at the beginning of a module as follows:

CODE

Option Compare Database

Public stImportCriteria As String
Public stClubName As String
Public dblMarkup As Double

There is a simple form called "UserSettingsForm". The VariableName field is locked, but the VariableValue field is ready for user input.

How would I now define the variables in VBA? I just need to filter the records and select the correct field, but I don't know the VBA language for doing this. Here is my failed attempt:

CODE

Public Sub GetParameters()
    Dim strSQL As String
    strSQL = "SELECT UserSettings.VariableName, UserSettings.VariableValue " & vbCrLf & _
"FROM UserSettings " & vbCrLf & _
"WHERE (((UserSettings.VariableName)=""ClubName""));"
    With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

    stClubName = .Fields(2)

    End With
End Sub

Kamulegeya

Apr 28 2012, 01:17 AM

QUOTE (SixtyBucks @ Apr 28 2012, 09:02 AM)

How do I assign values to my public variables based on a single record in a table?

I have a table called "UserSettings" containing a list of user defined variables. There are two fields in the table: one called "VariableName" and the other called "VariableValue".

Three examples of user defined variables listed in the table are stImportCriteria, stClubName, and dblMarkup.

I have declared these variables at the beginning of a module as follows:

CODE

Option Compare Database

Public stImportCriteria As String
Public stClubName As String
Public dblMarkup As Double

There is a simple form called "UserSettingsForm". The VariableName field is locked, but the VariableValue field is ready for user input.

How would I now define the variables in VBA? I just need to filter the records and select the correct field, but I don't know the VBA language for doing this. Here is my failed attempt:

CODE

Public Sub GetParameters()
    Dim strSQL As String
    strSQL = "SELECT UserSettings.VariableName, UserSettings.VariableValue " & vbCrLf & _
"FROM UserSettings " & vbCrLf & _
"WHERE (((UserSettings.VariableName)=""ClubName""));"
    With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

    stClubName = .Fields(2)

    End With
End Sub

Hello

Have you tried using a Dlookup function?

Ronald

NevilleT

Apr 28 2012, 07:01 AM

For screen or program customisation features, it is better to store it in the registry.

Here are bits of code I use to display a full window or floating window

First a form that determines the user selection through a checkbox

CODE

SaveSetting appname:=CurrentDb.Properties("AppTitle"), Section:="Settings", _
Key:="Windows", Setting:=Nz(Me.chkWindows, 0)

On the menu form - frmTitlePage - which remains open at all times I have a hidden checkbox

CODE

chkWindows = funGetRegKey(CurrentDb.Properties("AppTitle"), "Settings", "Windows", 0)

CODE

Public Function funGetRegKey(strAppName As String, strSection As String, strKey As String, Optional strDefault As String)
    Dim blnRecordHistory As Boolean
    Dim strOldValue As String
    Dim strNewValue As String
    Dim dbs As Database
    Dim rstHistTable As TableDef

    On Error GoTo Error_funGetRegKey

    '--------------------------------------------------------------
    ' Check the registry key exists.  If not Create the entry the first time it is checked
    If funRegKeyExists(strAppName, strSection, strKey) = False Then
        SaveSetting strAppName, Section:=strSection, Key:=strKey, Setting:=strDefault       ' Update the registry key
    End If

    '--------------------------------------------------------------
    ' Retrieve the key
    funGetRegKey = GetSetting(appname:=strAppName, Section:="Settings", Key:=strKey, Default:=strDefault)

Exit_funGetRegKey:
    On Error GoTo 0
    Exit Function

Error_funGetRegKey:

    MsgBox "An unexpected situation arose in your program." & vbCrLf & _
           "Please write down the following details:" & vbCrLf & vbCrLf & _
           "Module Name: modGeneric" & vbCrLf & _
           "Type: Module" & vbCrLf & _
           "Calling Procedure: funGetRegKey" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description

    Resume Exit_funGetRegKey
    Resume
End Function

Then on opening a from I use the following to check the hidden checkbox on frmTitlePage

CODE

    ' Full screen or restore
    If Forms![frmTitlePage]![chkWindows] = True Then
        DoCmd.Maximize
      Else
        DoCmd.Restore
        With Forms(strFormName)
            .InsideHeight = lngFormHeightTwips
            .InsideWidth = lngFormWidthTwips
        End With
    End If

datAdrenaline

Apr 28 2012, 08:50 AM

>> For screen or program customisation features, it is better to store it in the registry. <<

That is debatable in my opinion. I personally hate to store things in the registry. I find it a pain to manage and "junk" is left behind when the db application is removed from the PC because there is no setup/unistall to clean things up with most Access based applications.

I often use database properties or a table.

datAdrenaline

Apr 28 2012, 08:57 AM

QUOTE (SixtyBucks @ Apr 28 2012, 02:02 AM)

How do I assign values to my public variables based on a single record in a table?

Why use multiple variables in this circumstance? ... you could just use one variable and declare it as a DAO.Recordset, then set your Public variable (declared in a Standard Module) to the recordset you open. Then you have access to all the fields of that single row recordset.

QUOTE (SixtyBucks @ Apr 28 2012, 02:02 AM)

I have a table called "UserSettings" containing a list of user defined variables. There are two fields in the table: one called "VariableName" and the other called "VariableValue".
...
There is a simple form called "UserSettingsForm". The VariableName field is locked, but the VariableValue field is ready for user input.

Why use variables at all really. You can Open the Form upon start up in Hidden mode. If the user has the ability to change the setting the control the .Visible property of the Form object. Then when you need a value from those settings, simply refer to the control on the Form object that holds that value ...

Forms("UserSettingsForm").Controls("theControlNameYouNeed")

SixtyBucks

Apr 28 2012, 09:18 AM

The DLookup function worked. It is so straight forward. Thanks for the suggestion!

Looking into it a little, I am finding so many applications for DLookup.

Here is my functioning Dlookup statement:

CODE

Private Sub Command8_Enter()

stClubName = DLookup("Value", "UserSettings", "Key = 'ClubName'")

MsgBox "Club Name is " & stClubName

End Sub

Here are some good resource pages that I found regarding my new toy:

DLookup info

More Dlookup basic info

SixtyBucks

Apr 28 2012, 09:19 AM

Thanks for the registry suggestion. It sounds kool but I think it is a little over my head right now.

datAdrenaline

Apr 28 2012, 09:47 AM

>> Looking into it a little, I am finding so many applications for DLookup. <<

Be careful!!!! ... DLookup() may be easy to use, but it can be used unwisely. For example, if you use DLookup() 6 times to get 6 values from a single record of a table, then you "waste" time and energy because you open up the table 6 times and search the table 6 times. However, if you open a recordset to that one record, then read its fields, you decrease the database efforts by only opening the table once to get all six of your field values.

Also, DLookup() use in a Query object is highly discouraged.

SixtyBucks

May 19 2012, 01:19 AM

QUOTE (datAdrenaline @ Apr 28 2012, 07:57 AM)

Why use variables at all really. You can Open the Form upon start up in Hidden mode. If the user has the ability to change the setting the control the .Visible property of the Form object. Then when you need a value from those settings, simply refer to the control on the Form object that holds that value ...

Forms("UserSettingsForm").Controls("theControlNameYouNeed")

This ended up being the method that I used. It works very well. Thanks.

For other newbies like me who may read this I will try to explain this method in detail.

1. Created a linked table called UserSettingsTable (the table is linked to a separate database so that I can update the main database without deleting the user settings). This table contains these three fields: ID (autonumber), Key (text), and Value (text). It is basically the same as an .ini file. And it probably doesn't need the ID field.

2. Created a form called "UserSettings" that opens automatically on startup.

3. Specified this form to open in hidden mode upon startup. My main form opens first upon startup as specified in Tools-> Startup. The OnOpen event in the main form includes the line

CODE

DoCmd.OpenForm "UserSettings", , , , , acHidden

.

4. When a user wants to change a setting they hit the "Settings" button that I made on the main form which makes the form visible. I used

CODE

DoCmd.OpenForm "UserSettings", acNormal

5. The detail section of the form includes the Key and Value fields of the UserSettings table. The Key field is disabled and locked because I don't want the user to ever edit that. The value field is for them to change at will.

6. In the footer of the form are text boxes corresponding to each value. Labels exist for each key. For example, the key ClubName has a label saying "ClubName". Right next to the label is a text box that displays the value for ClubName. This text box is what makes the whole thing sing. The text box gets it's information from typing the following expression directly into the ControlSource field of the text box: =DLookUp("Value","UserSettings","Key = 'Club Name'")

I muted the colors of the fields in the footer because they are not for the user.

7. Now that you have this value expressed in a text box on an open form, you can reference it anywhere in your database. For example, I used this expression to put the name of the club at the top of a report: =([Forms]![UserSettings]![ClubName])

I used this expression successfully to incorporate a cost markup on a form: =FormatCurrency([CasePrice]+[CasePrice]*([Forms]![UserSettings]![MarkUp]))

Here is the same "variable" successfully used as an expression in a query: ClubCase: [CasePrice]+[casePrice]*Forms!UserSettings!Markup

And finally, here it is in VBA code:

CODE

stClubName = Forms!UserSettings!ClubName

8. Of course, you have to prevent the user from ever closing the form because a closed form cannot be referenced at all. To accomplish this, I disabled the close button on the form (the red X), then created my own "close" button that hides the window without closing it

CODE

DoCmd.OpenForm "UserSettings", , , , , acHidden

9. The other important task that the close button performs is to requery the form data

CODE

DoCmd.Requery

otherwise the user's fresh input would not be transferred to the text box in the footer until the next time the database was opened. To ensure that the user will always use my "Close" button when they are done updating settings, I removed the min and max window options as well.