Setting forms record source from combo box selection

[dmahn]

Hi all,

I am having problems setting a forms record source from a combo box selection on a seperate form.

I am developing a stock/quotes db to track all stock/quotes from several branches around europe which once completed I'm hoping to have hosted on a central server to allow all branches access to the db.

On startup the db displays a simple switchboard (frmSwitchBoard) containing 1 combo box (cbobranchselect) where the user is to select their branch either UK,MOC,Sweden,Germany,Italy,France and Spain.

After update of (cbobranchselect) a form opens with 3 buttons. This allows the user to open edit stock (frmEditStock), edit quotes (frmEditQuotes) both data entry forms, and a catalogue (frmDigitalCatalogue)used to filter and display the contents of the various tables.

What i would like to do is set the record source of (frmEditStock) and (frmEditQuotes) dependant upon the selection made in (cbobranchselect).

Each branch has 2 tables eg tblMOCStock and tblMOCQuotes etc etc,
If (cbobranchselect)= "UK" i would like to set frmEditStock and frmEditQuotes record source to tblUKStock and tblUKQuotes.

I have managed to do this on (frmDigitalCatalogue) by using a combo box on the actual form with the following code:

Select Case Me.cboBranchSelect

Case Is = "MOC"

Me.RecordSource = "tblMOCQuotes"

Having read what seems like 1000's of forums etc i cannot get this to work if the combo box is on a seperate form. Obviously i could add the combo box to each form and use the code above, however that would allow each branch to edit each others quotes/stock.

Is it possible to do this in a similar manner to above or am I barking up the wrong tree?

 

[MajP]

If you close the switchboard form then you would have to save the values in a global variable. In a public module:

public glblEditStock as string
public glblEditQuote as string

On your switchboard. In the combos after update event

Select Case Me.cboBranchSelect
Case "UK"
glblEditStock = "tblUKStocks"
glblEditQuotes = "tblUKQuotes"
....

Now in your forms you can set the recordsource equal to your glbl variables.

 

[dmahn]

Thanks for the help MajP

Please forgive my ignorance but I still cant seem to make it work.

When i set the forms record source as the glbl variable i get the error:

The record source specified on this form does not exist.

What code should i be using in the Module?

Thanks in advance

 

[PHV]

When i set the forms record source
How did you set it ?
In the Open event procedure of frmEditStock:
Me.RecordSource = glblEditStock

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dmahn]

Sorry ppl (School boy error time)

Have been looking at the code for so long I didn't notice the minor spelling mistake in the table names.

Everything works fine now
Thanks Again!