Form/SubForm Lookup

  • Thread starter Thread starter GSR
  • Start date Start date
G

GSR

Hi,

I have a form call Part Request. On the main form is a
filed which contain the Model number of the equipment,
(i.e. C3089A). The sub-form contains a drop down box
called Part Number. The part Number filed only displays
parts that have an association to the model number. This
is working fine.

The problem is that some parts for a given printer may be
used on more than one model number. The same applies to
say a mouse, hard drive, etc...

Is it possible to have those parts which are associated
to more than one model to appear in the same list
displayed by the part Number Drop down field. For
instance Part Number ABC123 can be used on Printer Model
C01 and C03. However, the part ABC123 is associate to
C01. In this case, I need it to appear in the listing
for Model number C03, even though it is not associated to
the device. I need it to appear in the listing for C03.

Any ideas, suggestions, etc... will be appreciated.

Thanks in advance
Greg
 
GSR said:
Hi,

I have a form call Part Request. On the main form is a
filed which contain the Model number of the equipment,
(i.e. C3089A). The sub-form contains a drop down box
called Part Number. The part Number filed only displays
parts that have an association to the model number. This
is working fine.

The problem is that some parts for a given printer may be
used on more than one model number. The same applies to
say a mouse, hard drive, etc...

Is it possible to have those parts which are associated
to more than one model to appear in the same list
displayed by the part Number Drop down field. For
instance Part Number ABC123 can be used on Printer Model
C01 and C03. However, the part ABC123 is associate to
C01. In this case, I need it to appear in the listing
for Model number C03, even though it is not associated to
the device. I need it to appear in the listing for C03.

Any ideas, suggestions, etc... will be appreciated.

Thanks in advance
Greg
Click to expand...

You need to do this with a third table
Model
C01
C02
C03
C04

Model/Part Number or Bill of Materials table
C01 ABC123
C01 DEF456
C03 ABC123
C04 GHI 789
C04 DEF456

Parts
ABC123
DEF456
GHI789

The Model table has a relationship with the model number in
the Model/Part Number table. The Part Number in the
Model/Part Number table has a relationship with the part number
in the parts table. This allow you to only have 1 unique model number
and 1 unique part number without having duplicate parts records for
the same part in other models.

From this you can build the queries you need.
Build a query using the Bill of Materials table and the Parts table.
Call this query 2. This query can be in your subform
The create query1 using the Model table. This is your main form.

You can also create a query 3 using query 1 and query 2.

Ron
 
Hi,

Thanks for the advice and guidance. Been out of office,
but will be working on this today, and can't wait to see
the results. I had the concept, but did not even come
close to the correct setup.

Thanks again for your help.
Greg
 
Back
Top