Can I use a 'global variables' table to hold persistent values?

[JeremyI]

This relates to Microsoft Access 2003, file format Access 2000.

I am trying to create VBA code that stores values in a table called 1_GlobalVariabl es, then more code--tied to a different form--that retrieves these values for further actions. The specific goal at the moment is a command button next to a combobox that will take the user to a detailed 'add new' form and then simply pop the new ID into the combobox when the new record is saved. This sort of thing was requested in one of my other projects here (it was thought unreasonable to have to manually select the new record after just having created it, which is a fair point, as it should be the default) and I would expect to use it all the time.

Here's my first effort as it stands:

    ' First, note where the new name should be added.
    
    Dim stSQL As String
    Dim stGlobalValue As String
    Dim stGlobalType As String
    
    DoCmd.SetWarnings False
    
    stGlobalValue = "Forms!frm3ViewEditAddProperties"
    stGlobalType = "LocationForm"
    
    stSQL = "UPDATE 1_GlobalVariables "
    stSQL = stSQL & "SET GlobalValue = '" & stGlobalValue & "' "
    stSQL = stSQL & "WHERE GlobalType = '" & stGlobalType & "';"

    DoCmd.RunSQL stSQL
    
    stGlobalValue = "cboMgtIndex"
    stGlobalType = "LocationControl"
    
    stSQL = "UPDATE 1_GlobalVariables "
    stSQL = stSQL & "SET GlobalValue = '" & stGlobalValue & "' "
    stSQL = stSQL & "WHERE GlobalType = '" & stGlobalType & "';"

    DoCmd.RunSQL stSQL
    
    DoCmd.SetWarnings True

That part works great. My two parameters are placed in the table exactly where I want them, ready to bring in the new value from the second form whenever the user has finished with it, requery the combobox and set the focus there. However, I cannot work out how to use VBA to look up the values I've stored in this global table. I would have guessed that would be simple, but after repeated attempts to research it, I'm still stuck.

If I can get this to work, I'll expand it to include the most recently viewed record for a form, the form last in use, possibly tied to the user, etc, with an eye to facilitating the data entry and management.

Sorry to have to post this as a new question, as I'm sure it's been addressed before, but somehow I'm having trouble finding anything I can use; links to relevant threads would be absolutely brilliant to get me started. I thought I'd seen an approach like this described somewhere, but can't track it down again. And any results for web searches like 'microsoft access global variables' are way over my head at this point. But since information is so hard to find, I wonder if I am attempting a method that doesn't work well with Access.

Am I going about this the right way, and if so, what next?

[TheSmileyCoder]

Im not sure your doing it the best way, but I can tell you how to retrieve data from a table.
Use the Dlookup("myFiel d","myTable","m yCrit")

In your example it would probably look like:

Dlookup("GlobalValue","1_GlobalVariables","GlobalType = '" & stGlobalType & "'")

Remember the criteria expresion should NOT be prefixed by WHERE.

[TheSmileyCoder]

Having read your post more careful, I have another suggestion.

First in a Module not bound to a form or report add a public variable for example:
Public strStorehouse as String

Then in your commandbutton do:

'Make sure to empty variable
strStoreHouse=""
DoCmd.OpenForm "frm_Test", acNormal, , , , acDialog
me.cmb_Test.requery
me.cmb_Test=strStoreHouse

Then set some code in the OnClose event on your form, for instance

strStoreHouse=me.ID

Opening the form as acDialog means your code will be paused until the form is closed again.

[JeremyI]

Thanks, Smiley--I'll give it a try and let you know.

[JeremyI]

Very well explained, Smiley--I've had a chance to try it out this morning, and it works perfectly. The dialog mode is exactly what I was looking for so the user can't wander off while adding the new entry, and it's just what people are used to seeing. I'll adapt it for my own tables and sprinkle it in everywhere.

I've never worked with unbound modules before, but I have seen them in others' code and not been sure when they were needed. Do I understand correctly that the value of these public variables only lasts until the session ends? If so, I'll see about a table for including anything that might be needed when the application is next used. I feel stupid for not realising that DLookup can be used in VBA.

One more thing, just to make sure I understand the logic: why does the requery come before setting the combobox value?

Thanks again!

[missinglinq]

That's correct, once the database is closed the global variable value is lost. You also need to be aware that if your app bombs out on an unhandled error the value also will be lost, which is why many developers shun them.

Welcome to Bytes!

Linq ;0)>

[TheSmileyCoder]

I was working under the assumption that you needed to add a new entry into the table being displayed in the combobox. A combobox is queried when a form is opened, and not requery'ed unless you ask it to.

Since you want the combobox to allready have selected the item you added in your dialog form, you must requery it first (because it doesnt contain the last entry you just made). Once required your telling it to select the new value.

[JeremyI]

Yes, new values are being added to the table by the second form. I thought the need for the requery was probably what you've described, but wanted to make sure.

Appreciate the notes from both of you, Smiley and missinglinq--I'm getting a clearer picture all the time. :-)