dlookup using variable as criteria

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
 
R

RonaldoOneNil

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top