apply filter button not working

[accesslearner]

I have 3 combo controls on my form.

Each combo box row source has a select query

eg. cbocompany
rowsource = SELECT tblcompany.CompanyID, tblcompany.CompanyName FROM tblcompany;

I have a form which has the values based on a query
Recordsource is bound to a query

When i click on company combo then company name should be filtered

when i select the state combo and apply filter than the state records should be filtered

I used the following code .

when I select the fullname combo than the name record should be filtered
I have the following code in my cmdApplyFilter button

Private Sub cmdApplyFilter_Click()
'This code will look to filter records in a subform
'It is using 3 string commands
'The form has 3 combo controls
'It is also using individual if statements
'Finally applying the filter on the subform
'Created by Trevor G January 2010
Dim strcpy As String
Dim strname As String
Dim strstate As String
If IsNull(Me.cbocompany.Value) Then
strcpy = "Like '*'"
Else
strcpy = "='" & Me.cbocompany.Value & "'"
End If
If IsNull(Me.cboname.Value) Then
strname = "Like '*'"
Else
strname = "='" & Me.cboname.Value & "'"
End If
If IsNull(Me.cbostate.Value) Then
strstate = "Like '*'"
Else
strstate = "='" & Me.cbostate.Value & "'"
End If
Me.Form.Filter = " [companyName]" & strcpy & " OR [NameFull]" & strname & " OR [BCMBusinessAddressState]" & strstate
Me.Form.FilterOn = True
End Sub

The apply filter button does not work and i do not get any errors too what am i doing wrong.

 

[boblarson]

Personally I would rewrite this a bit (TrevorG is getting there but there's some stuff I would change):

Private Sub cmdApplyFilter_Click()
    Dim strWHERE As String
    If Len(Me.cbocompany & "") > 0 Then
        strWHERE = [CompanyName] = " & Chr(34) & Me.cbocompany & Chr(34) & " Or ""
    End If
 
    If Len(Me.cboname & "") > 0 Then
        strWHERE = strWHERE & [NameFull] = " & Chr(34) & Me.cboname & Chr(34) & " Or ""
    End If
 
    If Len(Me.cbostate & "") > 0 Then
        strWHERE = strWHERE & [BCMBusinessAddressState] = " & Chr(34) &  Me.cbostate & Chr(34) "
    End If
 
    If Right(strWHERE, 4) = " OR " Then
        strWHERE = Left(strWHERE, Len(strWHERE) - 4)
    End If
 
    Me.SubformControlNameHere.Form.Filter = strWHERE
    Me.SubformControlNameHere.Form.FilterOn = True
 
End Sub

You could also have a way to select whether you wanted to use OR or AND instead.

Now, while some of what TrevorG wrote is workable, a lot of it is not necessarily the best way to get at it. So, I tried to give you some better ways. One of them is that you don't set a filter on something which isn't selected. So you don't use the LIKE "*" where you don't need to. Just filter on the applicable fields. Second, you don't need .Value everywhere (just extra typing). Also instead of checking for nulls, my code checks for nulls AND empty strings. Now that may not be necessary in a combo (it could though if a user is allowed to type in their own values - limit to list is not set to yes). It also helps for text boxes.

So, anyway - hopefully this SHORTENED code will help and also if this is referring to a subform like it was saying it needed to have more than me.form - it needed the subform control name.

 

[accesslearner]

Thanks Bob for taking the time to reply

I am getting a run time error 2465
Microsoft office access cant find ' | ' referred in your expression

also what does Chr(34) mean in your code.

what does
if Right(strWHERE, 4) = " OR " Then
strWHERE = Left(strWHERE, Len(strWHERE) - 4) mean.

your response is much appreciated.

 

[boblarson]

Thanks Bob for taking the time to reply

I am getting a run time error 2465
Microsoft office access cant find ' | ' referred in your expression

Something isn't named right - check to see if I got your fields and such named properly.

also what does Chr(34) mean in your code.

That is the ASCII character for a double quote (") and I use those instead of single quotes because many times we'll run into name problems (like O'Brien or Ben's Automotive, etc.)

what does
if Right(strWHERE, 4) = " OR " Then
strWHERE = Left(strWHERE, Len(strWHERE) - 4) mean.

That is removing the last OR if a selection was only made for the first or second combo box so it doesn't barf when you apply the filter.

 

[accesslearner]

I think i am not referring to the form or sub form correctly.

I dont know if i have a sub form.

I have 3 combo boxes placed on a form and i dragged a form into the main form.

I clicked the little black top square on the form and am checking the properties and there is no child form listed. My form view is datasheet view and record souce is bound to a query.I am checking only for company name but still getting the error.

My combo box row source has a SELECT tblcompany.CompanyID, tblcompany.CompanyName FROM tblcompany; that displayes the company name.

when i debug i have me.cbocompany = '3' which is the company id it is choosing.

as you can see i have company id and comany name i have hidden the company id and displayed the company name could this be the problem.

 

[boblarson]

First of all you would need to refer to a subform using the name of the control which houses the subform on the main form and not the subform name itself unless they are the exact same.

Second, if you want the company name instead of ID (I would just use the company id field and match to that) you can use
Me.cboCompany.Column(1)
to refer to the second column, which probably is the name.

 

[accesslearner]

Thanks for all the patience.

I am still getting a run time error 2465 at this line

If Len(Me.cbocompany.Column(1) & "") > 0 Then
strWHERE = [CompanyName] = " & Chr(34) & Me.cbocompany.column(1) & Chr(34) & " Or ""
End If


The name in the parenthesis refers to the table feild name is that right. I have added the Column(1) and when i debug i am getting the name of the company but

when i reach this statement below it highlights yellow and gives me the run time error.

strWHERE = [CompanyName] = " & Chr(34) & Me.cbocompany.column(1) & Chr(34) & " Or ""

i am not sure what is wrong here.

I have commented all other statements and i was just working with the company name combo for now.

Private Sub cmdApplyFilter_Click()
Dim strWHERE As String

If Len(Me.cbocompany.Column(1) & "") > 0 Then
strWHERE = [CompanyName] = " & Chr(34) & Me.cbocompany.column(1) & Chr(34) & " Or ""
End If

'If Len(Me.cboname & "") > 0 Then
' strWHERE = strWHERE & [NameFull] = " & Chr(34) & Me.cboname & Chr(34) & " Or ""
'End If
'If Len(Me.cbostate & "") > 0 Then
' strWHERE = strWHERE & [BCMBusinessAddressState] = " & Chr(34) & Me.cbostate & Chr(34) "
'End If
'If Right(strWHERE, 4) = " OR " Then
' strWHERE = Left(strWHERE, Len(strWHERE) - 4)
'End If
Me.contactsubform.Form.Filter = strWHERE
Me.Form.FilterOn = True
End Sub

 

[Rick_A]

Change this:
If Len(Me.cbocompany.Column(1) & "") > 0 Then
strWHERE = [CompanyName] = " & Chr(34) & Me.cbocompany.column(1) & Chr(34) & " Or ""
End If

To this:
If Len(Me.cbocompany.Column(1) & "") > 0 Then
strWHERE = "[CompanyName] = " & Chr(34) & Me.cbocompany.column(1) & Chr(34) & " Or "
End If

 

[DCrake]

Why are you not using the id's instead of the descriptions?

 

[accesslearner]

i changed the syntax but now i am getting a run time error 3075

syntax error(missing operator) in query expression
'[CompanyName]="Queen Core" or'

I had selected the company name Queen Core in my combo box


I am getting this pop up at the second last statement

Me.contactsubform.Form.Filter = strWHERE

to reply to Dcrake i was comparing the company names i will try using companyid and see if it works.

thanks again for all your inputs.

 

[accesslearner]

Private Sub cmdApplyFilter_Click()
Dim strWHERE As String

If Len(Me.cbocompany & "") > 0 Then
strWHERE =" [CompanyID] = " & '& Chr(34)& Me.cbocompany & Chr(34) & " Or ""
End If

Me.contactsubform.Form.Filter = strWHERE
Me.contactsubform.Form.FilterOn = True
End Sub


i am trying to use companyid and since it is numeric i am pretty sure this syntax is not right i am again getting a run time error.

 

[Rick_A]

Use this instead:

If Len(Me.cbocompany) > 0 Then
strWHERE = " [CompanyID] = " & Me.cbocompany & " Or "
End If

 

[accesslearner]

The following code gave me the results i need but now i need to work with 3 combo boxes and use the OR option how can i make it work. I use the company id and removed the or from the query.


Private Sub cmdApplyFilter_Click()
Dim strWHERE As String

If Len(Me.cbocompany) > 0 Then
strWHERE = " [CompanyID] = " & Me.cbocompany & ""
End If
'If Len(Me.cbocompany.Column(1) & "") > 0 Then
'strWHERE = "[CompanyName] = " & Chr(34) & Me.cbocompany.Column(1) & Chr(34) & " Or "
'End If

'If Len(Me.cboname & "") > 0 Then
' strWHERE = strWHERE & [NameFull] = " & Chr(34) & Me.cboname & Chr(34) & " Or ""
'End If
'If Len(Me.cbostate & "") > 0 Then
' strWHERE = strWHERE & [BCMBusinessAddressState] = " & Chr(34) & Me.cbostate & Chr(34) "
'End If
'If Right(strWHERE, 4) = " OR " Then
' strWHERE = Left(strWHERE, Len(strWHERE) - 4)
'End If

Me.contactsubform.Form.Filter = strWHERE
Me.contactsubform.Form.FilterOn = True
End Sub

 

[Rick_A]

Like so:

Private Sub cmdApplyFilter_Click()
Dim strWHERE As String

If Len(Me.cbocompany) > 0 Then
strWHERE = " [CompanyID] = " & Me.cbocompany & " OR "
End If

If Len(Me.cboname) > 0 Then
strWHERE = strWHERE & " [NameFull] = '" & Me.cboname & "' OR "
End If

If Len(Me.cbostate) > 0 Then
strWHERE = strWHERE & " [BCMBusinessAddressState] = '" & Me.cbostate & "' OR "
End If

If Right(strWHERE, 4) = " OR " Then
strWHERE = Left(strWHERE, Len(strWHERE) - 4)
End If

Me.contactsubform.Form.Filter = strWHERE
Me.contactsubform.Form.FilterOn = True
End Sub

 

[accesslearner]

This code works like a charm. Thanks to Bob , Rick and everyone who helped me with this problem.

The code works perfectly for company.
I need to clear the combo control before going to the next combo because the value is still stored and the records remain filtered for the first selection.

Meaning if i select company A all records with company A are diplayed i click the clear filter i get all records but the combo still shows Company A and when i click on any name the company again is filtered.

I have
Private Sub clear_Click()
Me.contactsubform.Form.FilterOn = False
End Sub

I think i need to clear the values from all the combos when i click the clear filter can this be done.

 

[Rick_A]

If I'm understanding you correctly, then this should do the trick.

Private Sub clear_Click()
Me.contactsubform.Form.FilterOn = False
cbocompany = ""
cboname = ""
cbostate = ""
End Sub

 

[accesslearner]

Thanks again to everyone who helped out with this. I used a combination of Bob and Ricks code and it woked out well. I appreciate all your help, patience and time.