Dependent combo boxes

[Skippymouse]

I know I should not be having a problem with this, but......

I have a combo box on a form wherein the user selects a Provider (based on a Provider table). I also have a Location combo box (based on a related Location table). Each Provider may have several locations. The client would like me to restrict the choices in the Location combo box to only those directly linked to a given Provider when that Provider is selected. I have tried running the Location combo box through a query, which (theorectically) produces a list of Locations based on the Provider name in [Forms]![Form].[ProviderName]. The query does not produce any results even though I have correctly addressed the field. I have used a similar technique, but that involved a subform which was based on the query itself.
I am absolutely certain I am making a very simple mistake, but my mind went to Jamaica without me and I am having to make do. Any help on this dilema would be very much appreciated.

 

[tc3of4]

Here is something similar to another person which I helped...

-----------------------------
This is probably a very easy question (but hey its late in the week).

I have a table [TblKit] which has two fields, floor and platform. On my form i have two combo boxes (named floor and platform). What i want is for the user to choose a floor and then to list the platforms found on that floor in the second combo box. Does anyone know how to populate a combo based on selection in another combo?

-------------------------------------
Hello,

assuming the following:

combo_Floor and combo_Platform

Within the Floor combobox Event tab, select the AfterUpdate event.

You can use this as the code

Private Sub combo_Floor_AfterUpdate()

combo_Platform.RowSourceType = "Table/Query"
combo_Platform.RecordSource =
"SELECT TblKit.platform " & _
"FROM TblKit " & _
"WHERE (TblKit.floor = '" & combo_Floor.Value & "')"

'/** Note: If the floor value is stored as a number and not as text then remove the left and right single quotes. If not, then keep the single quotes for text values.

combo_Platform.Requery

Exit Sub

What this does is that every time you select a value from the Floor combo box, you will have combo_Platform recordsource requery with the new values from the SQL.

Hope this helps. If you need more clarification just ask again or e-mail me.

tc3of4

[This message has been edited by tc3of4 (edited 01-18-2001).]

 

[Skippymouse]

I tried the code you sent but got a message stating that I had not defined a variable. What did I do wrong?

 

[Chris RR]

I'd put the SQL into the Control Source for the Location combo box. Also, sometimes using "like" gives better results:

Like "*" & [Forms]![fMyForm]![Provider] & "*"

Then,in the After Update for Provider, you just need:
me.Location.requery

Another hint...Do an "Option Explicit" and you will catch undefined variables faster.