combo box filter (1 Viewer)

[vickiwells]

I have a combo box on a form that I use to filter the records. It's set up using ApplyFilter in a macro in the AfterUpdate property of the box, and the Filter property of the form itself is set to match what's in the box. My problem is that it works fine until the filter is removed to see all records, and then the form's filter property gets changed and the box doesn't work anymore. How can I fix this so the form's filter property doesn't get reset?

 

[Matthew Snook]

Along with ApplyFilter (just before it, actually), set the "Filter" property of the form right there in the afterupdate event of the box. Apparently it is erased when the filter is disabled.

Matt

 

[vickiwells]

Thanks, but could you be more specific? I pretty much have to follow examples. How do I set the filter before the ApplyFilter?

 

[Matthew Snook]

Instead of the macro, you'll have something like this in the update event:

Sub ComboBox_AfterUpdate()
Me.Filter = "[SearchField] = " & [ComboBox]
Me.FilterOn = True
End Sub

I'm not certain of the syntax, but the setup should work.

Matt

 

[vickiwells]

Something's not right, the combo box still stops working after I show all the records.

 

[Matthew Snook]

OK, I wrote a similar form for a table that I have with some frog location data. At first, it was giving me the same behavior as you had noticed, ie.; when I clicked the funnel button to remove the filter, the form would no longer respond when the ComboBox selection was made. Once I got the syntax correct, so that when you look at the form properties after the combobox has been changed it reads something like "[Creek] = 'Clark'" (Clark creek is one of my choices), then everything proceeds as planned. I can select creeks, use the funnel button to apply or disable the filter, and everything works as expected. The problem seemed to be in getting the "Filter" to read correctly when the box was changed. Notice what I did with the double quotes around the single quotes in the "Me.Filter =" statement. And notice that you have to put the "Column(1)" on the combobox identifier, so that it's reading the displayed data and not the record ID number...

On my combobox "afterUpdate" event I have the following code:

Private Sub Combo4_AfterUpdate()
Me.Filter = "[Creek] = " & "'" & [Combo4].Column(1) & "'"
Me.FilterOn = True
End Sub

So yours should read something like:
Private Sub ComboBox_AfterUpdate()
Me.Filter = "[YourSearchField] = " & "'" & [ComboBox].Column(1) & "'"
Me.FilterOn = True
End Sub

After you select something in the combobox, look at form properties in the "Filter" property, and it should resemble:

[YourSearchField] = 'Selection'

Try it, let me know if it works. Good luck,

Matt

 

[vickiwells]

Here's what I've got in there, I copy-pasted. I'm getting an error saying I'm missing an operator. Can you find it?

Private Sub cboJob_AfterUpdate()
Me.Filter = "[job] = " & "'" & [cboJob].Column(1) & "'"
Me.FilterOn = True
End Sub

 

[Matthew Snook]

Sorry, I'm at a loss. I tried misspelling each of the components in turn, and I get other errors but not that one. It sure looks just like what I did. Now I'm wondering, are the records on the form itself or in a subform? The example I gave you is sorting records on the form itself, not on a subform. I'll look some more...

Matt

 

[vickiwells]

It's filtering on the main form. My form filter ended up looking like this: [job]= '

 

[Matthew Snook]

Somehow it has cut off the end of the statement! This is at least a lead... A couple more questions:

Is [job] a number or text? and;

I was assuming that the bound column of the cboJob was the first,(column(0)), and that the visible field is column(1). Is that correct?

I'm thinking that the filter property can't assign the cboJob value to [job] because they are different data types, or something like that. I think it can assign a number into a text field but not visa versa. If the field you're pulling from the cboJob is not the one you want, that may be the problem. So many different ways to be wrong...

Matt

 

[vickiwells]

Would it be easier to send you a copy of the file? It's not a very big database, if you'd have time to look at it.