Using One Combo Box for two querries

[SharonInGa]

The user needs the ability to choose names from a combo list which can be one
of two products. Each product has a different set of names associated with
two perspective tables. I currently have two forms that links to two
different tables.

How can I combime the two forms into one form using one combo box since
everything is the same with the exception of the names.

OptionButtons: ProductOneNames ProductTwoNames
cboSelectNames

I tried to use option buttons so the user can select which product he wants,
however, as a novice, I am having difficulty of where to put the code.
Should it go into the 'Name' combo box? Is there a better to do this?

 

[Rob Oldfield]

Do I have this right? ...depending on which one of the products is chosen,
you want to display a different set of names?

I'm not exactly sure I know exactly what you're after, but...

You could set up two forms relating to each of the two sets of names. And
you include on that form a subform control. In the AfterUpdate event of the
combo (or the option group if you prefer it) you do something like...

if me.cbo="Product1" then
me.sfControlName.sourceobject="frmWhatever"
else
me.sfControlName.sourceobject="frmWhichever"
endif

Alternatively (and better) - does the table containing the names also
contain the product that they belong to? If that's the case then you can
just set up one form that lists *all* names and again add that as a subform
onto the form containing your combo/option group. You would then just need
to set the master fields for that subform as the name of the combo/option
group, and the child field as the name of the field containing the product.

(With regard to using an option group... the code would go in the after
update of the box around the options... not any of the controls within it.)

 

[SharonInGa]

The form code is exactly the same for both products. The Customer Name Combo
box needs to direct its query to the appropriate customer name table
depending on which product the user selects. My supervisor wants me to use
one form to perform this procedure.

 

[Rob Oldfield]

In that case you could use the same form object but then, in the If command
just change the RecordSource property of the form to link to the relevant
table. (...though it does sound as if the db structure that has the two
sets of names in two different tables is not normalised...)

 

[SharonInGa]

nRob

Can you give me a example of what code to use in changing the recordsource?

If ProductType = Product1 then
set rst = ?????? , querydef ????

 

[Rob Oldfield]

It's basically the SQL of the query to pick out product 1 or 2... something
like...

Me.RecordSource = "SELECT * FROM Names WHERE [ProductName]='Product1';