Combo box in a subform

[Shairal]

I have a subform that has two combo boxes, box 1 gives a distributor's name and box 2, which is based off box 1, lists the distributor's branches. I want my user to select a distributor from the first combo box, then the second combo box will return only the branches associated with this distributor.

The code in my Distributor box is:
SELECT DISTINCT Distributor.DistName FROM Distributor ORDER BY Distributor.DistName;
The code in the Branch box is:
SELECT DISTINCT Distributor.BranchName, Distributor.DistName FROM Distributor WHERE (((Distributor.DistName)=Forms!subfrmXYZ!Distributor)) ORDER BY Distributor.BranchName;

The issues is … All this works fine while I'm testing it in the subform, however when I go to the main form, I am able to select the Distributor's name, but then I get a parameter prompt – Forms!subfrmXYZ!Distributor, and cannot choose a branch location. I don't understand why it works perfectly as a stand-alone form, but the branch combo box does not work when using it as a subform. Help please!

 

[Crowley16]

a subform when activated as a subform doesn't in fact exist with it's own instance, you will have to access it's instance as part of the main form, i.e.

MainForm!SubFormControlName!ControlName

--------------------
Procrastinate Now!

 

[PHV]

Have a look here:

http://support.microsoft.com/?kbid=209099

Furthermore search the FAQs for cascading combobox.

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

 

[Shairal]

Worked like a charm! Thanks so much!

 

[TheAceMan1]

how are ya Shairal . . .

Fill in proper names in [purple]purple[/purple] below:

[blue]SELECT DISTINCT Distributor.BranchName, Distributor.DistName FROM Distributor WHERE (((Distributor.DistName)=Forms![purple][b]MainFormName[/b][/purple]![purple][b]subFormName[/b][/purple].Form!Distributor)) ORDER BY Distributor.BranchName;[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884: