dlookup using variable as criteria

  • Thread starter Thread starter Marianne
  • Start date Start date
M

Marianne

I'm trying to use the dlookup function using a variable as the criteria but
keep getting Error 2001 You cancelled the previous operation. Could someone
tell me why my code won't work. Code as follows:

Private Sub Qty_AfterUpdate()
Dim strcoID As String

strcoID = DLookup("CompID", "tblContracts", "[Service No] =
Forms!frmMiscInv!ServiceNo")

If Forms!frmMiscInv!ServiceType = "TRA" Then

Me.Rate = DLookup("[TravelRate]", "[tblCustomer_Details]", "[CompID] =
strcoID")

End If
End Sub
 
Your variable needs to be outside the quotes
strcoID = DLookup("CompID", "tblContracts", "[Service No] = " &
Forms!frmMiscInv!ServiceNo)

Me.Rate = DLookup("[TravelRate]", "[tblCustomer_Details]", "[CompID] = " &
strcoID)
 
Back
Top