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:
Thanks in advance, Iain Robbo ;-)
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 ;-)