Auto- Populate from Linked table

[alphaomega3]

OKay here is the situation. I am creating an inspection log database and have previously created an Non-conformance database. I am trying to get the NCRno field to auto-populate after I update the qtyrej field. the procedure is that we receive the material, inspect it, then log it. The Inspector will get to the point of accepting or rejecting and click a control button that takes them to a form that is has a linked table to enter the non-conformance in the other db. Okay here is the code I am using to autopopulate the NCR number field.

Private Sub QtyRej_AfterUpdate()
 
   Me.NCRNo = DLookup("[rptNumber]", "tblMaster", "[Date]=" & Me.DATE & " AND [ptNumber]=" & Me.PtNo)
 
End Sub 

when I do this I receive the following error: Run-Time Error '3464': Data Type Mis-match in criteria expression. don't have a clue what this means or how to fix it. Tried to give as much info as possible. tblMaster is the linked table.

[MMcCarthy]

Private Sub QtyRej_AfterUpdate()
 
   Me.NCRNo = DLookup("[rptNumber]", "tblMaster", "[Date]=" & Me.DATE & " AND [ptNumber]=" & Me.PtNo)
 
End Sub 

Assuming Me.Date is a Control on the form you need to enclose it in # characters. Also as Date is a VBA function you need to surround it with square brackets. (Ideally change date to something else in the table and form). If ptNo is not a number but a text field then you will have to enclose it with single quotes like the date example.

Private Sub QtyRej_AfterUpdate()
 
   Me.NCRNo = DLookup("[rptNumber]", "tblMaster", "[Date]=#" & Me.[DATE] & "# AND [ptNumber]=" & Me.PtNo)
 
End Sub 

Mary

[NeoPa]

There is also a requirement to format the 'literal' date as m/d/y (Only SQL supported Standard).
Shamelessly plagiarising Mary's code, here is a converted version.

Private Sub QtyRej_AfterUpdate()
 
   Me.NCRNo = DLookup("[rptNumber]", "tblMaster", "[Date]=" & Format(Me.[DATE],'\#m/d/yyyy\#') & " AND [ptNumber]=" & Me.PtNo)
 
End Sub

If you want an explanation of what does what with dates etc in queries, see (Literal DateTimes and Their Delimiters (#).)