Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dlookup puzzle

Status
Not open for further replies.

Robbo1974

Technical User
Jun 1, 2001
151
GB
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:

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 ;-)
 
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?
 
wow!

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

[25Invoice] is a number field.

Cheers, Iain :) Robbo ;-)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top