By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,163 Members | 934 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,163 IT Pros & Developers. It's quick & easy.

Auto- Populate from Linked table

P: 78
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub QtyRej_AfterUpdate()
  2.  
  3.    Me.NCRNo = DLookup("[rptNumber]", "tblMaster", "[Date]=" & Me.DATE & " AND [ptNumber]=" & Me.PtNo)
  4.  
  5. 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.
Dec 15 '06 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. Private Sub QtyRej_AfterUpdate()
  2.  
  3.    Me.NCRNo = DLookup("[rptNumber]", "tblMaster", "[Date]=" & Me.DATE & " AND [ptNumber]=" & Me.PtNo)
  4.  
  5. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub QtyRej_AfterUpdate()
  2.  
  3.    Me.NCRNo = DLookup("[rptNumber]", "tblMaster", "[Date]=#" & Me.[DATE] & "# AND [ptNumber]=" & Me.PtNo)
  4.  
  5. End Sub 
Mary
Dec 15 '06 #2

NeoPa
Expert Mod 15k+
P: 31,660
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub QtyRej_AfterUpdate()
  2.  
  3.    Me.NCRNo = DLookup("[rptNumber]", "tblMaster", "[Date]=" & Format(Me.[DATE],'\#m/d/yyyy\#') & " AND [ptNumber]=" & Me.PtNo)
  4.  
  5. End Sub
If you want an explanation of what does what with dates etc in queries, see (Literal DateTimes and Their Delimiters (#).)
Dec 16 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.