Syntax error (missing operator)

[aamer]

When I run the following code I get SYNTAX ERROR (MISSING OPERATOR) In a query expression 'Delivered To Party'

How Do I Fix The Following

Please note:
GP Dialog is a query
Purchase Description is a field
Delivered To Party is a field


Private Sub CboD2Pty_AfterUpdate()
Dim strSource As String


strSource = "SELECT Delivered To Party " & _
"FROM GP Dialog " & _
"WHERE Purchase Description = '" & Me.CboD2Pty & "' ORDER BY Delivered To Party"
Me.cboPdiscr.RowSource = strSource
Me.cboPdiscr = vbNullString
End Sub

[June7]

Field and table names have spaces. Enclose them in []. Advise to avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be Delivered_To_Party or DeliveredToParty.

Why use code to set RowSource? Why not just put static SQL statement directly in the RowSource property?

SELECT [Delivered To Party] FROM [GP Dialog] WHERE [Purchase Description] = [CboD2Pty] ORDER BY [Delivered To Party];

Need code to requery the RowSource. Suggest the combobox GotFocus event:

Me.CboD2Pty.Requery

[aamer]

thx for the help I got the spaces issue fixed but a bigger problem came up

I am using the following code. This time I am using TABLE: Purchases


Private Sub CboPtyNme_AfterUpdate()
Dim strSource As String
strSource = "SELECT [Purchase Description] " & _
"FROM Purchases " & _
"WHERE [Delivered To Party] = '" & Me.CboPtyNme & "' ORDER BY [Purchase Description]"
Me.Cboitems.RowSource = strSource
Me.Cboitems = vbNullString
End Sub



When I select the Customer Title in CboPtyNme & Item Description in Cboitems I am getting multiple items in Cboitems




where am I making a mistake

[June7]

Use DISTINCT keyword:

SELECT DISTINCT ...

These controls are UNBOUND and used just to input filter criteria, not to enter/edit data into record?

[ssanfu]

I really hate changing a combo box row source "on-the-fly".
Set the combo box row source once in design view - then you are down to two lines.

Private Sub CboPtyNme_AfterUpdate()
  Me.Cboitems.Requery
  Me.Cboitems = vbNullString
End Sub

I know there are lots of examples demonstrating the change row source technique, but (to me) it is like changing the form recordsource every time you want to filter records.

OK, I'm off my soap box.....

[aamer]

Thanks for the help. Have fixed it as follows


Private Sub CboPtyNme_AfterUpdate()
Dim strSource As String
strSource = "SELECT DISTINCT [Purchase Description] " & _
"FROM Purchases " & _
"WHERE [Delivered To Party] = '" & Me.CboPtyNme & "' ORDER BY [Purchase Description]"
Me.Cboitems.RowSource = strSource
Me.Cboitems = vbNullString
End Sub






Private Sub cmdOpenReportSingle_Click()
On Error GoTo Err_Handler

Const REPORTNAME = "Delivered2Party"
Const MESSAGETEXT = "No Item Selected."
Dim strCriteria As String

' build string expression to filter report
' to selected customer
strCriteria = "[Delivered To Party] = """ & Me.CboPtyNme & """"


' make sure a customer is selected
If Not IsNull(Me.CboPtyNme) Then
' open report filtered to selected customer
DoCmd.OpenReport REPORTNAME, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub