combo Problems

[Guest]

I'm building a access search, now the problem is that there are a number of
combo boxs and txt boxs, the txt part I got working but having trouble with
the combo boxes, I did this same query in Query builder, but found I had
troble with it, as the whole lot had to be rewritten when, another search
field was added, when I found this, I though I could use it instead.

I can run the query no problem when the combo are left empty, but as soon as
the combos are used, I get Run-time error '2001'
You canceled the previous operation
when debug is run it is stoped on "Me.FilterOn = True"

this is the code I'm using, "I'm trying to Adapt it from the code found on
Allen Brown's site" currently working with one combo, get it workin then move
onto the others

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.cboFilterCustomer) Then
strWhere = strWhere & "([Customer] = """ & Me.cboFilterCustomer &
""") AND "
End If


If Not IsNull(Me.txtFilterOrderNo) Then
strWhere = strWhere & "([Order Number] Like ""*" &
Me.txtFilterOrderNo & "*"") AND "
End If


'If Not IsNull(Me.cboFilterMaterial) Then
' strWhere = strWhere & "([Material] = """ & Me.cboFilterMaterial &
""") AND "
'End If

If Not IsNull(Me.txtFilterPart) Then
strWhere = strWhere & "([Part Number] Like ""*" & Me.txtFilterPart &
"*"") AND "
End If


'If Not IsNull(Me.cboFilterProducts) Then
' strWhere = strWhere & "([Product] = """ & Me.cboFilterProducts &
""") AND "
'End If


'If Not IsNull(Me.cboFilterBelt) Then
'strWhere = strWhere & "([Belt Width] = """ & Me.cboFilterBelt &
""") AND "
'End If

If Not IsNull(Me.txtFilterTitle) Then
strWhere = strWhere & "([Drawing Title] Like ""*" &
Me.txtFilterTitle & "*"") AND "
End If

If Not IsNull(Me.txtFilterNumber) Then
strWhere = strWhere & "([Drawing Number] Like ""*" &
Me.txtFilterNumber & "*"") AND "
End If




lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[Guest]

When running the immediate,

this is what came up there
([Customer] = "" & Me.cboFilterCustomer & "")
which I think tells me I got problems on the combo it's self, these are
running off a table that have look up fields

 

[johngo]

When running the immediate,

this is what came up there
([Customer] = "" & Me.cboFilterCustomer & "")
which I think tells me I got problems on the combo it's self, these are
running off a table that have look up fields

The Immediate window output comes from, Debug.Print strWhere
You should be able to copy and paste that output in the form filter
property to test it.
The output you are seeing, ([Customer] = "" & Me.cboFilterCustomer &
"") should read
([Customer] = "CustomerName") when working.

It is not accessing the cboFilterCustomer, it is treating
Me.cboFilterCustomer like a piece of text.

This may not solve your problem, but try using an apostrophe instead of
two adjacent double quotes, so instead of:
"([Customer] = """ & Me.cboFilterCustomer & """) AND "
have
"([Customer] = "'& Me.cboFilterCustomer & "') AND "
The Immediate window would be, ([Customer] = 'CustomerName')

 

[Guest]

I mis read the instructions, it's a number field as it's a look up table, so
it's looking to the id instead, it was to have single quote and not double or
triple, it's now working, it's intresting that I got it working by using a
text box hidden, and that was with the trible quotes,

Thanks for the help

When running the immediate,

this is what came up there
([Customer] = "" & Me.cboFilterCustomer & "")
which I think tells me I got problems on the combo it's self, these are
running off a table that have look up fields

The Immediate window output comes from, Debug.Print strWhere
You should be able to copy and paste that output in the form filter
property to test it.
The output you are seeing, ([Customer] = "" & Me.cboFilterCustomer &
"") should read
([Customer] = "CustomerName") when working.

It is not accessing the cboFilterCustomer, it is treating
Me.cboFilterCustomer like a piece of text.

This may not solve your problem, but try using an apostrophe instead of
two adjacent double quotes, so instead of:
"([Customer] = """ & Me.cboFilterCustomer & """) AND "
have
"([Customer] = "'& Me.cboFilterCustomer & "') AND "
The Immediate window would be, ([Customer] = 'CustomerName')