using a value from a unjoined table in query (1 Viewer)

[sloth]

Hi there, within my DB i have a table called tbloptions, and a variable called store, which determines the store which the prog is run in ie. 1 2 or 3
now instead of the query asking the user for the store id with a parameter i want to pull the value out of this table automatically

i tried :
LIKE [tblOptions]![Store]
and
[tblOptions]![Store]
but these dont work

any ideas?, sounds simple...
by the way this table has no relationships, this is what seems to stuff the query up. i thought one of the above statements would have worked though.

any help would be appreciated

 

[The_Doc_Man]

In general, the reason that Access doesn't return the right answer is because you asked it (a) the wrong question, or (b) no question at all.

In order to decide which of stores 1, 2, or 3 you are at, you must either ask a question or have an external way to decide this question. Like, perhaps do a once-only lookup of your machine name from your registry. Get these names from each machine on which you could run and make a table with [MachineID],[StoreID] as the only two fields.

Then, you could make a simple call in some code somewhere to determine which machine you are on.

The help topics you seek are "GetSetting" as a way to read the registry, "SaveSetting" as a way to write to the registry.

Perhaps you could make an entry to the registry for each machine using something under the HKEY_LOCAL_MACHINE options. Make this a HIDDEN FORM if you want to do it interactively once and forget about it.

In fact, you could take this even farther by finding a name not currently used in you HKEY_LOCAL_MACHINE subkeys, like "MyStoreNumber" and just do a GetSettings of this entry (of course, after having first defined it one time for each machine.)

WARNING: Read up on this topic in the help files A LOT before even STARTING to think along these lines. Because like anything else that mucks about in the registry, you invite trouble if you are careless.

 

[Pat Hartman]

Start the query again. Add both tables to the QBE grid. Select all the columns you want from the main table. Draw a join line connecting the store field in both tables. Only rows in the main table that contain store numbers found in tblOptions will be selected.

 

[sloth]

thanks pat, that was perfect, so simple,
thanks for both of the responses