Can Combo Box A influence the choices in Combo Box B

  • Thread starter Thread starter Paul Warnack
  • Start date Start date
P

Paul Warnack

Hello,

I have a form which has the following combo boxes on it.

Truck A
Truck B
Truck C

All of these retrieve their data from the same query, but point to different
fields in the underlaying table. Is it possible to remove, for instance,
truck 10 from the list that populates Truck B if it has been choosen for
Truck A and the choices for Truck A and B from Truck C?

If there is a better way to accomplish this I am open to suggestions. I
briefly searched the newsgroups and web this morning, but there is just to
much info out there.

Thank you in advance,
Paul Warnack
 
Paul,

This can be done. Make a query based on the table that lists your
trucks, and in the criteria of the appropriate field, put the equivalent
of...
<>[Forms]![NameOfYourForm]![Truck A]
.... and then use this query as the Row Source of the Truck B combobox.
Similarly, a query for the Truck C combobox would have the criteria like
this...
<>[Forms]![NameOfYourForm]![Truck A] And
<>[Forms]![NameOfYourForm]![Truck B]

To make this work properly, you would need to put some code like this on
the After Update event of Truck A...
Me.Truck_B.Requery
Me.Truck_C.Requery

Having said this, it seems likely that your basic database design could
be improved. Probably it is not a good idea to have the Trucks in 3
separate fields in the main table. Probably it would be better (and
less work and headaches ultimately) to have a separate table, related
one-to-many with the main table, which contains your trucks A, B, and C
entries as separate records.
 
I agree with you. Originally I used the following relationship:

tblOrder_info
ID
DOS
Ordnum
....

tblTruck_job_hist
ID
ID_Link
Unit_Number
--ID from tblOrder_info and ID_Link from tblTruck_job_hist had a one-to-many
relationship. This would allow one job to have many trucks.

tblDrv_job_hist
ID
ID_Link
Unit_Number
Last_Name
First_Name
--ID from tblOrder_info and ID_Link from tblDrv_job_hist had a one-to-many
relationship. This would allow one job to have many drivers.

This all made sense to me until I built the form. I wanted to use combo
boxes to display the list of trucks and have the values written to
tblTruck_job_hist. The combo box would not allow that. The fields on
frmOrder_Info were the only fields I could write data into.

That is why I have a DrvA, DrvB, & DrvC field on tblOrder_info

Paul Warnack

Trimmed
Having said this, it seems likely that your basic database design could
be improved. Probably it is not a good idea to have the Trucks in 3
separate fields in the main table. Probably it would be better (and
less work and headaches ultimately) to have a separate table, related
one-to-many with the main table, which contains your trucks A, B, and C
entries as separate records.
Click to expand...

Trimmed
 
Paul,

The way to do handle the data management, with your original table
schema, would have been to use a continuous view subform (complete with
combobox for truck selection) for the multiple trucks per job.
 
Back
Top