Combo Box - Conditional and saved to a table

[mromyn]

Hi -
Does anyone have an example of a form with two combo box, the second combo
box possible values are determined by the first combo box entery. The records
need to be saved in a table. I would like to do this with a little code as
possible.

I have been struggling with this and have tried various methods and great
ideas I have received through this discussion board but nothing has
completely worked as I need it to.

With one example I had the combo boxes working, but the record values
wouldn't save.
With the update of a new records all of the previous values entered would
change.

Specifically what I need to do is have ComboBox1, when entered, determine
the possible values for ComboBox2. When the record is entered it needs to be
saved to a table. This table will then be used for reports.

I wouldn't think it's that difficult, but I cannot get this to work. If you
have a working example that would be so very helpful.

Thanks!
Mel

 

[Ken Sheridan]

Mel:

Provided the combo boxes are bound controls then the values will be saved to
the underlying table. For Example if you have a control cboState bound to a
State field and a control cboCity bound to a CityID field then the RowSource
for cboCity would be:

SELECT CityID, City FROM Cities WHERE State = Form!cboState ORDER BY City;

Its other properties would be as follows:

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

In cboState's AfterUpdate event procedure put:

Me.cboCity = Null
Me.cboCity.Requery

Note that in this case cboCity is bound to the CityID foreign key column in
the form's underlying table or query, but shows the City name from the Cities
table. City names can be duplicated so should not be used as keys. This
means, however, that the above approach can only be used in a single form
view form, not in a continuous form as can be done where a 'natural' key is
used.

The above also means that the form's underlying table containing the CityID
and State fields is not correctly normalized (to Third Normal Form) as CityID
implies State, so the State column is redundant and leaves the door open to
the possibility of inconsistent data being entered.

Both of these drawbacks can be avoided and you'll find a solution, using the
local administrative units of County, District and Parish in my area, at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[mromyn]

This was exactly what I needed! Thank you so much!