After Update event, loop function - Update two tables from a form

[luv2birdie]

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

[June7]

Why does that form show 7 rows of blank boxes?

One way to carry forward data to next record is to programmatically set DefaultValue property. http://access.mvps.org/access/forms/frm0012.htm

[luv2birdie]

I just clipped a portion of the form so whoever was reading my post had a visual of what I was doing. Forgive me, I am a novice at programming so after reading the link I don't quite understand how that will do what I need. When i enter the serial number in the first text box of each row I want it to automatically fill in the part number. I have the aforementioned look up code in the first textbox as an event procedure on "After Update" and when i enter a serial number it auto fills the part number - works great. But now I want to enter a code that will have all 20 rows do the same thing (loop?) if there is a serial number entered into the first text box on each row. The entries will vary maybe one record maybe up to 20, depends on how many products the customer sends in under warranty.

I also wanted some feedback as to whether this was a good way of doing this procedure or if there were some other suggestions.

[June7]

That's what I don't understand. Why are there 20 rows of blank boxes? Are these all UNBOUND? Bound or Unbound, this will NOT create 20 records in the table without VBA code.

There should be one row of boxes bound to fields. Enter data to create record. Move to new record row to create next record.

Displaying the data related to the serial number is different issue. Why don't you use a combobox to select serial number? The combobox can have columns for the associated info from the serial numbers table. The textboxes can have expression in ControlSource that references combobox column index. Index begins with 0.

=[comboboxname].Column(1)

[luv2birdie]

I have nearly 20,000 records and will grow larger I didn't think a combo box would be a good option because of that. The first two boxes are unbound but I was going to bound the second two to table b. Maybe this one is too difficult to get forum help. It is a bit confusing. Oh, and one of the reasons the form is this way is because after I fill it out I have to create a report to send to the manufacturer.

[June7]

There is a way to programmatically handle long list in combo and list box. Review: http://allenbrowne.com/ser-32.html

How will this form structure help to create a report? How does the form commit records to table? A report should be based on records in table. Apply filter as needed to the report RecordSource.

[luv2birdie]

I think I need to do more research before I can get this worked out. I appreciate the help!