Cascaded Lookup in a Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form which includes a manufacturer field and a model field. I need
to be able to select a manufacturer (I've already set it as a lookup field to
a table and it's working properly), and then have the model field update to
display only the records which belong to that particular manufacturer. I
know I need to somehow set it in the RowSource property of the model field's
combo, but I'm not sure exactly how to do that, or where the info should be
stored. Should I have a separate table which contains the model info, or can
my manufacturer and model info be stored in the same table? If they are
stored separately, do I need to join them by a manufacturer ID#? Please help
if you can--thanks!
 
MintLes,
By "lookup field" I take it you mean a combo box or list box. In either
case, (I'll use combo boxes) the process is the same.
Let's call the 2 combos cboMfg and cboModel, on a form called
frmYourForm..
CboMfg has a query or table behind it that lists all the Manufacturers.
CboModel should have a query behind it with Manufacturer and Model
columns, and the Manufacturer column (can be hidden) should have this
criteria...
=Forms!frmYourForm!cboMfg
Now, when a Manufacturer is selected in cboMfg, use the AfterUpdate event
of cboMfg to do a Requery of cboModel, and then only Models associated with
that Manufacturer will be displayed.
You're using the value in cboMfg to "filter" the records returned by the
cboModel query.
 
Al Camp,

Sorry I didn't specify, but yes I am using a combo box. The info you
offered was exactly what I needed--thank you so much!!

MintLes :)
 
Ok,
I tried this, but it "filtered" the field on all of the records, not
just the one I was currently on. This caused that field on many of my other
records to go blank if the other records did not have the same primary key.
I'd like to be able to do this on a per record basis, or even better, do it
in the table definition instead of on the form. Any suggestions?

Thanks
Dave H.
 
Back
Top