Filter datasheet in split form selecting items in combobox

Question

So I have a datasheet in split form view. One of the columns is called "WSIB Employer Declaration Complete?" and is has values yes or no. I'd like to add a combo box where the user can select either yes, no or all and based on their selection retrieves either, the records where WSIB Employer Declaration = yes, no or all the records. The column itself (WSIB Employer Declaration Complete?) has some extra text in the records, for example one record it yes "Yes(Jan 18/17)". How can I have the combo box to display the options Yes,No,ALL and retrieve whatever the user selects without the extra text?

I tried two methods for the combo box's after_update property but both produce two errors.

Method One

Private Sub ComboWSIB_After_Update()

Select Case Me.ComboWSIB.Value

Case "All"

       Me.FilterOn = False

Case "Yes"

       Me.Filter = InStr(1, [WSIB Employer Declaration Complete?], ”Yes”) = '" & Me.comboWSIB & "'"

       Me.FilterOn = True

Case "No"

       Me.Filter = InStr(1, [WSIB Employer Declaration Complete?], ”No”) ='" & Me.comboWSIB & "'"

       Me.FilterOn = True

End Select

End Sub

The error here is that lines ***Me.Filter = InStr(1, [WSIB Employer Declaration Complete?], ”Yes”) = '" & Me.comboWSIB & "'"*and Me.Filter = InStr(1, [WSIB Employer Declaration Complete?], ”No”) = '" & Me.comboWSIB & "'" say there is a syntax error

Method Two

Private Sub ComboWSIB_AfterUpdate()

Select Case Me.ComboWSIB.Value

Case "All"

       Me.FilterOn = False

Case Else

       Me.Filter = "[WSIB Employer Declaration Complete?] Like "*" & Me.ComboWSIB & "*"

       Me.FilterOn = True

End Select

End Sub

The error here is I get an error saying Run-Time error '13': Mismatch Type with **Me.Filter = "[WSIB Employer Declaration Complete?] Like "*" & Me.ComboWSIB & "*"**highlighted yellow.

If anyone has any insight as to how to solve these errors. As always, that would be much appreciated!

Answer 1

You said this is a Split Form? So why not use the DataSheet view's filtering capabilities to do this?

Answer 2

Hmm, is that a BIT Data Type?  If yes try True or False no quotation marks.  Also, what is the Row Source for the Combo Box?

Answer 3

Hi everyone, I got it working with the help of another access resource. Thank you!

Answer 4

It's true I could use that however this little project is for users who have very little experience working with access. Right clicking and selecting "Text Filters" for example might not be intuitive for everyone so I'm trying to make it more obvious on how to filter. Now I could make a label somewhere saying "To filter, place cursor over column, right click....etc" but I worry that that will annoy the user as they might have to do more work than is necessary.

Thank you the response

Answer 5

Hmm, is that a BIT Data Type?  If yes try True or False no quotation marks.  Also, what is the Row Source for the Combo Box?

Not sure what BIT data type means but I tried the following code without quotation marks:

Private Sub ComboWSIB_AfterUpdate ()

Select Case Me.ComboWSIB.Value

Case "All"

        Me.FilterOn = False

Case "Yes"

        Me.Filter = InStr(1, [WSIB Employer Declaration Complete?], ”Yes”) = '" & Me.comboWSIB & "'"

        Me.FilterOn = True

Case "No"

        Me.Filter = InStr(1, [WSIB Employer Declaration Complete?], ”No”) ='" & Me.comboWSIB & "'"

        Me.FilterOn = True

End Select

End Sub

And I still get an error saying Compile Error: Variable not defined and the world Yes is highlighted yellow:

Me.Filter = InStr(1, [WSIB Employer Declaration Complete?], Yes) = " & Me.comboWSIB & "

Also the Row Source is set as a value list with the items "Yes";"No";"All"

Any more information is much appreciated!