Dlookup puzzle

[Robbo1974]

Hi all,

I want to look up a form record for a specific customer using Dlookup and an invoice number and then move through some subform records to the order which that invoice number relates to.

I did something similar using an order number (different field, text data type) which works perfectly and so adapted the code slightly using a couple of If statements - it now breaks on the line indicated with the following message:

Syntax error (missing operator) in query expression '25Invoice = "14039" ' (space added for clarity.)

I think this is probably one of those simple errors that you need someone else to spot - possibly something to do with using the right quotes? Help?

Mainform - [frmContactInformation]
Subform - [frmOrders1Subform]
Link Field [CustomerID]

The forms are based on tables and the fields referenced are:

tblContactInformation - [CustomerID] (autonum data type)
tblOrders1 - [25Invoice]&[CustomerID] (both num data type)
tblOrderDetails - [FullRemInvoice]&[CustomerID] (both num data type)

We sometimes invoice a 25% deposit and then the remainder, sometimes in full immediately. It should be obvious which number goes in which field

I want to be able to enter any invoice number into the field [txtInvoiceNum] on my search form [Form1] and automatically find the [CustomerID] value, no matter whether the invoice number is in [25Invoice] or [FullRemInvoice]

Heres the code:

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click


Dim intCustomer1 As Integer
Dim intCustomer2 As Integer
Dim intInvoiceNum As Integer

Dim strCriteria1 As String
Dim strCriteria2 As String

intInvoiceNum = Me.txtInvoiceNum

strCriteria1 = "25Invoice = " & intInvoiceNum

strCriteria2 = "FullRemInvoice =" & intInvoiceNum

' find the customer ID corresponding to the 25% or full/remainder invoice number

intCustomer1 = DLookup("CustomerID", "tblOrders1", strCriteria1) '***breaks here***

intCustomer2 = DLookup("CustomerID", "tblOrderDetails", strCriteria2)

If intCustomer1 = Null Then

'which it will if there is no matching 25% invoice number

GoTo No_25_Invoice:

Else:

    ' open the main form at the right record
    
   DoCmd.OpenForm "frmContactInformation", , , "[CustomerID]=" & intCustomer2

    ' close the search form

    DoCmd.Close acForm, "Form1"

    ' restrict the subform to show only the order requested

    Forms![frmContactInformation]![frmOrders1subform].Form.Filter = strCriteria2

    Forms![frmContactInformation]![frmOrders1subform].Form.FilterOn = True
    
End If

    GoTo Exit_command6_click


No_25_Invoice:

If intCustomer2 = Null Then

GoTo Both_No_Invoice:

Else:

    ' open the main form at the right record

    DoCmd.OpenForm "frmContactInformation", , , "[CustomerID]=" & intCustomer1

    ' close the search form

    DoCmd.Close acForm, "Form1"

    ' restrict the subform to show only the order requested

    Forms![frmContactInformation]![frmOrders1subform].Form.Filter = strCriteria1

    Forms![frmContactInformation]![frmOrders1subform].Form.FilterOn = True
    
End If

    GoTo Exit_command6_click


Both_No_Invoice:

    MsgBox "There is no order corresponding to the invoice number you have entered." & vbCrLf & vbCrLf & "Please check  and try again.", vbExclamation, "Invoice Number Not Found"

    Me!txtInvoiceNum.Value = Null

    Me!txtInvoiceNum.SetFocus

Err_Command6_Click:

    MsgBox Err.Number & Err.Description

Exit_command6_click:

End Sub

Thanks in advance, Iain Robbo ;-)

 

[jimmythegeek]

You mentioned the criteria field was a text field. If I am reading it right, change this line:

strCriteria1 = "25Invoice = " & intInvoiceNum

To this:

strCriteria1 = "25Invoice = '" & intInvoiceNum & "'"
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[nickjar2]

wow!

is 25invoice a text or numeric field???? Nick (Everton Rool OK!)

 

[Robbo1974]

Hi Guys,

[25Invoice] is a number field.

Cheers, Iain Robbo ;-)