Two textboxes autopopulate one based on entry of the other - same record

[luv2birdie]

Need Help!
I have created a RMA (return merchandise authorization) form. I have several unbound textboxes. In the first textbox I want to enter in a serial number which is already in an existing table in my db. I want the second textbox to populate with its associated part number which is located in a field in the same record. We use a scanner to enter the serial number from a barcode on the widget. The remaining textboxes are for updating a separate table but I will get to that later once I figure this part out.
I will have twenty rows of this same scenario in one form (the customer may send multiple units back and we want to process them all in one transaction).
I tried a Dlookup function but it is not working, if that is even an applicable function for my needs.
I tried variations of this:
=DLookUp("[PN]","Products","[SN]=" & [Forms]![frmATS-RMA]![txtSN] & "'")
I do not have any experience in vba. I know where to put it just not how to create it.
Any help would be greatly appreciated.

Thanks,
J

 

[BlueIshDan]

Honestly I have no history in what it is that you're working with. it would make it easier for me to help if you could break it down into simple terms that anyone that works in access would know.

Simple is better!

 

[luv2birdie]

I have a table with fields - two of them being
SN - Serial number
PN - Part number

In a form I want to enter the SN in one textbox and have the the second textbox auto populate the PN from the same record.

I am sorry if the first post was not clear - this is my first time posting to the forum.

Thanks,
J

 

[bob fitz]

Perhaps something like:
Me.txtPN=DLookUp("[PN]","Products","[SN]="' & Me.txtSN & '")
as the After Update event of txtPN

 

[luv2birdie]

Gave that a try. It still doesn't work. It was highlighted red after pasting it. I don't know if that indicates a problem. This is how I entered it...

Private Sub txtPN_AfterUpdate()
Me.txtPN=DLookUp("[PN]","Products","[SN]="' & Me.txtSN & '")
End Sub

 

[bob fitz]

Gave that a try. It still doesn't work. It was highlighted red after pasting it. I don't know if that indicates a problem. This is how I entered it...

Private Sub txtPN_AfterUpdate()
Me.txtPN=DLookUp("[PN]","Products","[SN]="' & Me.txtSN & '")
End Sub

Are you sure that you pasted it into the forms module rather than a general module

 

[luv2birdie]

I opened the form in design mode, selected the "txtPN" textbox, opened properties, selected After update, clicked on the button with three dots, chose "code builder" then pasted the code in the middle of "Private Sub" and "End Sub". When I open the VBA application window and put my cursor within this code the top drop down boxes show "txtPN" and "After Update" - if that helps at all. Does it appear I did this correctly?

Thank you for your continued assistance, I really appreciate it.

 

[bob fitz]

I opened the form in design mode, selected the "txtPN" textbox, opened properties, selected After update, clicked on the button with three dots, chose "code builder" then pasted the code in the middle of "Private Sub" and "End Sub". When I open the VBA application window and put my cursor within this code the top drop down boxes show "txtPN" and "After Update" - if that helps at all. Does it appear I did this correctly?

Thank you for your continued assistance, I really appreciate it.

Yes and no, you appear to have entered the code correctly as I requested but I think I should have asked you to put it in the After Update event of txtSN not txtPN.

 

[luv2birdie]

I changed it as you noted. I received a "Compile Error: Syntax Error"
I attached a view of the code.

 

[bob fitz]

Can you post a copy of the db

 

[luv2birdie]

I realize seeing the db would be much more insightful to help me but our db has quite a lot of information in it so I am not comfortable posting it in a public environment. I understand if this prohibits you from helping me any further. I do appreciate your help so far.

 

[luv2birdie]

After looking over your dlookup function and the ones I previously tried I made a small change swapping the quote and apostrophe after [SN]= and added a set of quotes before the apostrophe at the end of the string, it worked. This is what I have...
Me.txtPN = DLookUp("[PN]", "Products", "[SN]='" & Me.txtSN & "'")

Now I just have to get it to do the same thing for all the rows in the form, isn't that a loop???

Thanks again!!!