General discussion

Locked

Access Combo Boxes

By halusaka ·
I have three tables: District, City and State (for instance). The first is dependant on the second and the second on the third. I have corresponding forms to enter the codes and descriptions of the entities.
In the Districts form the user first enters the State Code, then the City Code and then the District Code. I am using combo boxes to restrict entry of the codes to valid ones such that only the City Codes within the specified State will appear in the list of valid values etc. The Row Source entry for the combo box City Code reads something like:

SELECT DISTINCTROW (City.CityCode, City.Description from City WHERE
(((City.StateCode)=[Forms]![Districts]!StateCode]));

The problem is once I have selected/entered one valid State Code in a record and I move on the another new record where I enter a new State Code, it always picks up the City Codes for the previous State in the new (and all subsequent records) until I close the form and open it again. How can I make the program "forget" the previous

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access Combo Boxes

by keith.hall1 In reply to Access Combo Boxes

If these controls are not bound to a control source, set the values of the city and district combos to null. In the AfterUpdate() event for the state combo, requery the City combo, and afterupdate on the City combo requery the District combo.

Regards

Keith Hall

Collapse -

Access Combo Boxes

by halusaka In reply to Access Combo Boxes

The question was auto-closed by TechRepublic

Collapse -

Access Combo Boxes

by klww In reply to Access Combo Boxes

maybe you can set a default value for your state code combo, so whenever you go to the next record it will always show your default state code

Collapse -

Access Combo Boxes

by halusaka In reply to Access Combo Boxes

The question was auto-closed by TechRepublic

Collapse -

Access Combo Boxes

by Bob Sellman In reply to Access Combo Boxes

You must force Access to re-run each query when changes to selection values occur.

Use the following code behind the after update property for State (and see City code below). I'm assuming cboCityCode as the name of the combo box for the city and cboDistrictCode as the name of the combo box for the district.

me.cboCityCode.Requery

me.cboCityCode = Null
me.cboDistrictCode = Null

For the on update property of the combo box for City (cboCityCode), just use this code:

me.cboCityCode.Requery
me.cboDistrictCode = Null

You can also be sure that choices are valid when an existing record is initially displayed by adding this code to the on current property of the detail area of your form so that if a user selects a new city for the state currently displayed (or city or district), the correct list is displayed.

me.cboCityCode.Requery
me.cboDistrictCode.Requery

Good luck.

Collapse -

Access Combo Boxes

by halusaka In reply to Access Combo Boxes

The question was auto-closed by TechRepublic

Collapse -

Access Combo Boxes

by donq In reply to Access Combo Boxes

It sounds as though you MAY have a problem nobody addressed up to this point. IF "Districts" are children of "Cities" which in turn are children of "States" AND your Data is modeled properly using inserting a "city<FOREIGN KEY>State" (or States Table Index Value within the City Table, AND inserting a "city<FOREIGN KEY>Dist" (or City Table Index Value within the District Table. Everything you need would be inherited any you would only select the lowest hierarchical object used (a District?) I always add an "ORDER BY" clause after the code Access builds intuitively to insure I see what I want in a "make-sense" sort order. I also "Requery" everything often (when I add or delete a record) as the other folks mentioned. Use Relationships that enforce Data Integrity and you'll never go wrong dealing with hierarchically structures objects.

Collapse -

Access Combo Boxes

by halusaka In reply to Access Combo Boxes

The question was auto-closed by TechRepublic

Collapse -

Access Combo Boxes

by halusaka In reply to Access Combo Boxes

This question was auto closed due to inactivity

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums