Filter by Customer

[TJC]

I have a quotations form (Access 97)with two combo boxes. The first is a two column combo box listing all quotations by quote no. and Title. The following is the code.
Private Sub CbQuoteNo_AfterUpdate()
Dim myDynaset As Recordset, myCriteria As String
Set myDynaset = Me.RecordsetClone
myCriteria = "[QuoteNo] = " & Str$(CbQuoteNo.Column(0))
myDynaset.FindFirst myCriteria
If Not myDynaset.NoMatch Then
Me.Bookmark = myDynaset.Bookmark
End If
myDynaset.Close
Call Form_Current

End Sub

I want the second combo box to filter by customer. After you select the customer you want in this combo box I want it to limit only those quote nos. assigned to that customer in the first combo box. The following is the code I currently have setup for this combo box.

Private Sub CbSelectCustomer_AfterUpdate()
Dim SQL As String

SQL = "SELECT [QuoteNo], [Title] "
SQL = SQL & "FROM Quotations "
If Filled(Me!CbSelectCustomer) Then
SQL = SQL & "WHERE CustomerID='" & Me!CbSelectCustomer & "' "
End If
SQL = SQL & "ORDER BY QuoteNo;"
Me!CbQuoteNo.RowSource = SQL
End Sub

Here is the code for the "Filled" function.
Public Function Filled(Field As Variant) As Boolean
Filled = True
If Len(Trim(Field & " &quot) = 0 Then Filled = False
End Function

CustomerID and QuoteNo are numeric fields. These are the autonumbers in their respective tables. After I select the customer I want to view the quotations for and then select the first combo box I get a "Data type mismatch in criteria expression" error message.

I am new to Access programming. What am I doing wrong?? Thanks in advance for any help you can provide.

 

[DougP]

try adding a space here

SQL = SQL & "[red]_[/color]ORDER BY QuoteNo;"
Also you have the CbSelectCustomer inside single quotes.
Is CbSelectCustomer a Text value or numeric
If numeric remove single quotes like so
SQL = SQL & "WHERE CustomerID=" & Me!CbSelectCustomer



DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[gol4]

Remove the quotes it makes access think you are looking for text

SQL = SQL & "WHERE CustomerID='" & Me!CbSelectCustomer & "' "
needs to read

SQL = SQL & "WHERE CustomerID=" & Me!CbSelectCustomer

 

[TJC]

I updated the code for the select customer combo box as follows:
Private Sub CbSelectCustomer_AfterUpdate()
Dim SQL As String

SQL = "SELECT [QuoteNo], [Title] "
SQL = SQL & "FROM Quotations "
If Filled(Me!CbSelectCustomer) Then
SQL = SQL & "WHERE CustomerID=" & Me!CbSelectCustomer
End If
SQL = SQL & "ORDER BY QuoteNo;"
Me!CbQuoteNo.RowSource = SQL
End Sub

I receive the following error message.
"Syntax error (missing operator) in query expression 'CustomerID=2 ORDER BY QuoteNo.'."

Any ideas??