What I have is a database of records with unique serial numbers (primary key in table A). I have a second table for warranty returns (serial number is foreign key table B). I need to record products that come back under warranty. We qualify them first then if they meet qualifications they are forwarded on to the manufacturer for repair or replacement. There may be one or multiple products at a time.
I have created a form – I have attached a view of the format. In the first row I have inserted the following to lookup up the serial number and return its applicable part number from a field within table A.
Private Sub txtSN_AfterUpdate()
Me.txtPN = DLookUp("[PN]", "Products", "[SN]='" & Me.txtSN & "'")
End Sub
The problem I am running into is how to loop this (if I can) to complete the lookup function if there is a serial number entered into any of the following rows, minimizing programming.
Just as additional info:
As you can see in the image, I am also going to need to update table B with the other columns “Reason and Warranty” once I enter the data in.
I will also need to insert a date and RMA number in the form which I have not built into the form yet and have it update all the records that will be put into table B.
Is a form the right option? Should I use some kind of query with this? I am just kind of stumped on the design of this process and what the right way to do it is.
Thanks,
Jen