Problem with lookup

[laura525]

I was instructed to build a database to keep track of scrapped parts. The company will also be filling out a scrap ticket form manually (not sure why but I have no control over that).

In the database, employees want the Part Description to pop in the form once the part number is entered. I accomplished this using the lookup wizard in the table. The main table looks in the part table for the description once the part number is typed.

The problem I am having is that the scrap ticket they will be using allows for up to four part numbers for each ticket number (primary key in main table). I am not quite sure how to accomadate for the multiple part numbers in the database or how to lookup each. Hopefully I have explained myself clearly. Any help is appreciated.

Thank you.

 

[laura525]

Let me explain my set up a little, maybe that will help.

-Main Table-
Ticket No
Part No
Defect Code
Work Cener No
Clock No

-Supervisor Table-
Clock No
Supervisor
Employee

-Work Center Table-
Work Center No
Work Cener Desc

-Part Table-
Part No
Part Desc

-Defect Code-
Defect Code
Defect Code Desc

They want one form to enter all info, and they want things to automatically fill in from the other tables, for example, when Part No is typed, the Description should fill in; when clock number is typed - the supervisor's name should fill in, etc. The trouble I am running into is that each ticket No can have up to 4 part numbers and four defect codes. How do I create the form to have four part no and 4 defect codes to fill in and how is each looked up and the description is filled in?

 

[webcats]

Have you tried DLookup?

Example:

Me.Escrow_Officer = DLookup("Escrow_Officer", "tlkpTitle", "[Title_Co_ID] = 1")
Me.Escrow_Phone = DLookup("Escrow_Phone", "tlkpTitle", "[Title_Co_ID] = 1")
Me.Escrow_Fax = DLookup("Escrow_Fax", "tlkpTitle", "[Title_Co_ID] = 1")

 

[Apollo13a]

Do you have relationships between your tables?

 

[laura525]

Yes there are relationships.

Main and Supervisor tables are related by Clock No

Main and Part table by Part No

Main and Work Center by Work Center No

Main and Defect Code tables by Defect Code