Re-posted DLookup query - syntax problem?

[Robbo1974]

Hi all,

Apologies for re-posting, but I can't get this to work and it's making mey head spin!

I want to look up a form record for a specific customer using Dlookup via an invoice number which is stored in a field which is on a subform which displays customer orders.

I do something exactly the same using the order number for the order (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
Robbo ;-)

 

[Gazer44]

Sorry, my eyes started to glaze over as I read this.

If i've got this correctly can't you just setup a query that contains all the relevant fields from the 3 tables and point your search form to that.

So when you enter an Invoice no. it'll always show the corresponding Customer ID.

 

[Robbo1974]

Gazer (or should that be Glazer?),

The problem is with retrieving the CustomerID value in the first place. Whether I retrieve that from the original table or from a query is irrelevant.

I then need to use the CustomerID value to move to the correct form record, and the invoice number value to restrict the subform records to the order requested - it's not just about 'showing' it somewhere. Robbo ;-)

 

[JJOHNS]

OK. I think maybe you were right in saying this is one of those simple things you just need someone else to spot. Let's hope so anyway. I just pulled a similar line out of a book. Try this:

strCriteria1 = "25Invoice = '" & intInvoiceNum & "'"