Wildcard within a combobox

[T_Tronix]

I have a combobox with 2 columns where I manually set the following criteria:



"E";"English"
"F";"French"
"EF";"Bilingual"

In my employee table under the Languages field I have E,F or EF

I wanted to display in a sub-form a list of employees who speak a specific language but I can't use a wildcard in the combo-box properties. Basically when someone is looking for an English speaking employee they should get both E and EF employees not only E.

I tried doing "E*";"English" but it doesn't display anything.

[ssanfu]

If you are storing the letters ("E", "F" ,"EF") in the table, then you should be able to use

in a query
WHERE YourTableName.Languages Like "E*";

-------------

as a filter
Me.Filter = "Languages Like 'E*'"
Me.FilterOn = TRUE

Here is the code I used for an unbound combo box to filter the form

Private Sub Combo3_AfterUpdate()
   'sets a filter
   If Me.Combo3 = "E" Then
      'All English
      Me.Filter = "Languages Like 'E*'"
      Me.FilterOn = True
   ElseIf Me.Combo3 = "F" Then
      'All French
      Me.Filter = "Languages Like 'F*'"
      Me.FilterOn = True
   ElseIf Me.Combo3 = "EF" Then
     ' Bilingual
      Me.Filter = "Languages Like 'EF'"
      Me.FilterOn = True
   End If

End Sub

Code for a button to clear the filter

Private Sub btnClearFilter_Click()
   Me.Filter = ""
   Me.FilterOn = False
End Sub

[T_Tronix]

Well its a combobox that displays results in a subform. How do I get into the qury of the combo-box? I initialized the values from its properties window so I did not actually write the code.

If you are storing the letters ("E", "F" ,"EF") in the table, then you should be able to use

in a query
WHERE YourTableName.Languages Like "E*";

-------------

as a filter
Me.Filter = "Languages Like 'E*'"
Me.FilterOn = TRUE

Here is the code I used for an unbound combo box to filter the form

Private Sub Combo3_AfterUpdate()
   'sets a filter
   If Me.Combo3 = "E" Then
      'All English
      Me.Filter = "Languages Like 'E*'"
      Me.FilterOn = True
   ElseIf Me.Combo3 = "F" Then
      'All French
      Me.Filter = "Languages Like 'F*'"
      Me.FilterOn = True
   ElseIf Me.Combo3 = "EF" Then
     ' Bilingual
      Me.Filter = "Languages Like 'EF'"
      Me.FilterOn = True
   End If

End Sub

Code for a button to clear the filter

Private Sub btnClearFilter_Click()
   Me.Filter = ""
   Me.FilterOn = False
End Sub

[T_Tronix]

Not sure I follow the functionality of "filter" how does it update the query result in my subform?

I inserted the following into my combobox code:

Option Compare Database

Private Sub cmbLanguage_Click()
Me.Filter = "Languages Like 'E*'"
Me.FilterOn = True
End Sub


Private Sub cmbLanguage_AfterUpdate()
   'sets a filter
   If Me.cmbLanguage = "E" Then
      'All English
      Me.Filter = "Languages Like 'E*'"
      Me.FilterOn = True
   ElseIf Me.cmbLanguage = "F" Then
      'All French
      Me.Filter = "Languages Like 'F*'"
      Me.FilterOn = True
   ElseIf Me.cmbLanguage = "EF" Then
     ' Bilingual
      Me.Filter = "Languages Like 'EF'"
      Me.FilterOn = True
   End If


End Sub

[June7]

Is the combobox on the main form or on the subform?

If it's on the main form that makes it harder to set the Filter and FilterOn properties of subform.

Is the main form a bound form? Is the subform in datasheet view?

An alternative to the If Then structure:

Me.Filter = "Languages Like '*" & Me.combobox & "*'"
Me.FilterOn

[T_Tronix]

This is what it looks like, 3 combo-boxes that define what is seen in a subform.

[ssanfu]

Delete this code:

Private Sub cmbLanguage_Click()
Me.Filter = "Languages Like 'E*'"
Me.FilterOn = True
End Sub

I was giving you an example of code to filter a form. If you have the combo box on the main form and you want to filter a sub-form, you would have to change the code in the combo box after update event to refer to the sub form.

So, if the name of the combo box is "cmbLanguage", and the name of the sub form is "Subform1", then the code would be:

Option Compare Database  ' these two lines should be at the top of every module
Option Explicit     ' these two lines should be at the top of every module

Private Sub cmbLanguage_AfterUpdate()
   'sets a filter
   If Me.cmbLanguage = "E" Then
      'All English
      Me!Subform1.Form.Filter = "[Languages] Like 'E*'"
      Me!Subform1.Form.FilterOn = True
   ElseIf Me.cmbLanguage = "F" Then
      'All French
      Me!Subform1.Form.Filter = "[Languages] Like 'F*'"
      Me!Subform1.Form.FilterOn = True
   ElseIf Me.cmbLanguage = "EF" Then
     ' Bilingual
      Me!Subform1.Form.Filter = "[Languages] Like 'EF'"
      Me!Subform1.Form.FilterOn = True
   End If


End Sub

Private Sub btnClearFilter_Click()
   Me.Filter = ""
   Me.FilterOn = False
End Sub

See attached mdb.

[T_Tronix]

The problem I'm getting my subform seems to be updated before the filter comes on. Like when I choose English it does not show the EF as part of the list so the filter doesn't even have data to filter.

[T_Tronix]

Ok got it, I had to remove the cmbLanguage -> Languages connection from the Link Master to Link Child fields. Now it works

And a minor fix since French is either EF or F it should be "*F"

THANKS!

[ssanfu]

Good catch.
Glad you got it functioning

[June7]

Simpler, more flexible code in post 5.