Cascading Combo Boxes... a final plea.

[jgi]

I've tried asking a few questions about this topic to no answer. I've fought my way through the problem, discovering things for myself (the best way, I suppose) but I am now stuck. Here is my problem.

I have three fields in my Form: Firm, City, Address 1. The user chooses a Firm from a lookup list, then a City from a list that is formatted by which Firm is chosen, and then an Address from which City is chosen (by this time in the user's choices, there is only one choice). CompExperiment is my Form, FirmTable is my Table. My RowSources are the following:

Firm:
SELECT DISTINCT [FirmTable].[Firm] FROM [FirmTable]

City:
SELECT DISTINCTROW [FirmTable].[City] FROM FirmTable WHERE((([FirmTable].[Firm]) Like [Forms]![CompExperiment]![Firm]));

Address 1:
SELECT DISTINCTROW [FirmTable].[Address 1] FROM FirmTable WHERE((([FirmTable].[City]) Like [Forms]![CompExperiment]![City]));

This all works fine except when I move on to the next record. The user can choose a new Firm, but the City and Address 1 lookup fields conform to the previous Firm and City chosen. My City and Address 1 RowSources seem to only be reading from the first record entered. This must have something to do with PrimaryKey but I'm not sure what and I'm quite stuck. Can somebody please help me?

jgi.

 

[SQLBI]

Hi,

In the Change event of the 'Firm' combobox, add the following code;

With Me

    .cboComboCity.Requery 'Change to the names of your controls.
    .cboComboAddr.Requery

End With

This will then re-run the query's for the City and address fields each time the Firm is changed.

Hope this helps, let me know how you get on.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[jgi]

That seems to be working great, actually. Thank you. If I have any questions in the near future, I'll post them.

 

[JeremyNYC]

Why do you make the user choose, if there's only one choice? Will there eventually be other choices? If not, I would suggest using a text box and just displaying the company's addy.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[jgi]

Jeremy.

In the beginning there will be about 2000 choices (different firms) and then the specific cities/offices of the firms (any where from one choice to 20) and some firms have numerous offices in the same city. It should actually be a rather large endeavor. But I'm well on my way. Thanks for the input.

jgi.

 

[HansB]

Hi,

I'm facing the same problem, but the solution doesn't work for me.
The tables are updated correctly but the screen isn't.
If the 'Default View' of the form is Single Form everything is working quite well. If the 'Default View' is either datasheet or continuous form my form is reacting strange on the selections in the different comboboxes.

Can somebody please help me.

HansB