Filter datasheet in split form selecting items in combobox

V

vent

Registered User.
Local time
Today, 12:09
Joined
May 5, 2017
Messages
160
Hi everyone

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. I tried doing this in the combo box's on_click property:

Code: 
Private Sub ComboWSIB_Click()
    If (ComboWSIB.value) = "all" Then
        Me.FilterOn = False
    Else
        Me.Filter = "[WSIB Employer Declaration Complete?] = ComboWSIB"
        Me.FilterOn = True
    End If
End Sub

As always if anyone has any guidance. That is much appreciated!
 
Last edited:
Add text delimiters to Else clause

Code: 
= '" & me.comboWSIB &"'"
 
ridders said:
Add text delimiters to Else clause

Code: 
= '" & me.comboWSIB &"'"
Click to expand...

I tried that but nothing shows up in the combo box. I also I just realized, in the column itself (WSIB Employer Declaration Complete?) some of the records have extra text, for example one record it yes "Yes(Jan 18/17)". How can I have the combo box to display Yes,No,ALL without any of the extra text?
 
Last edited:
Adding delimiters won't affect what is shown in the combo box.
If you use a value list, it will be whatever you ask it to be

As for the other point, you either need to edit the data in that field or modify your else clause to extract the text to the left of the bracket.
Might be safest to use instr function to search for yes or no
 
Code: 
Private Sub ComboWSIB_Click()
    If (ComboWSIB.Value) = "all" Then
        Me.FilterOn = False
    Else
        Me.Filter = InStr(1, [WSIB Employer Declaration Complete?], ”Yes”) = Me.ComboWSIB
        Me.FilterOn = True
    End If
End Sub

So I tried implementing this code but I still get the same error
 
Last edited:
1 are you using a value list for the combo box? What are the possible values?
2. You've messed up the text delimiters - see my first reply
3. You haven't allowed for 'No'. You will need an Elseif clause.
Better still replace the if else section with Select Case
4. What error are you getting and is it fixed if you sort our point 2?
 
ridders said:
1 are you using a value list for the combo box? What are the possible values?
2. You've messed up the text delimiters - see my first reply
3. You haven't allowed for 'No'. You will need an Elseif clause.
Better still replace the if else section with Select Case
4. What error are you getting and is it fixed if you sort our point 2?
Click to expand...

Hi I put "Yes" just as a test run. And the following error I get is Compile Error: Variable Not Defined with Yes being highlighted. The combo box is set as a Value List (Row Source: "Yes";"No";"All"). I will keep trying and re-look at your reply with the text delimiters. Thank you.
 
vent said:
Hi I put "Yes" just as a test run. And the following error I get is Compile Error: Variable Not Defined with Yes being highlighted. The combo box is set as a Value List (Row Source: "Yes";"No";"All"). I will keep trying and re-look at your reply with the text delimiters. Thank you.
Click to expand...

The code should be in the After_Update event NOT the On Click event

You could try setting the value list as Yes;No;All i.e. WITHOUT quotes
That may fix the
Compile Error: Variable Not Defined with Yes being highlighted
Click to expand...

One of these should work (not tested though):

Code: 
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

or this may be better:

Code: 
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


NOTE: Avoid spaces and characters like ? in field names
This is better: WSIBEmployerDeclarationComplete
 
Hi Ridders

Thank you for your reply. I tried both methods in the after_update property and I get two errors. The first one:

Code: 
Private Sub ComboWSIB_After_Update()

Select Case Me.ComboWSIB.Value

Case "All"
        Me.FilterOn = False
Case "Yes"
        [B]Me.Filter = InStr(1, [WSIB Employer Declaration Complete?], ”Yes”) = '" & Me.comboWSIB & "'"[/B]
        Me.FilterOn = True

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

End Select

End Sub

The error is the bolded lines appear red and error says "Syntax error".

For the second method:

Code: 
Private Sub ComboWSIB_AfterUpdate()

Select Case Me.ComboWSIB.Value

Case "All"
        Me.FilterOn = False
Case Else
        [b]Me.Filter = "[WSIB Employer Declaration Complete?] Like "*" & Me.ComboWSIB & "*"[/b]
        Me.FilterOn = True

End Select
End Sub

The error is Run-time error '13': Type mismatch and the line

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

If anyone has any advice that would be much appreciated!
 
Last edited:
Hi

Sorry - I said both were untested.

The solution was trickier than I expected.

I even resorted to checking Allen Browne's website http://allenbrowne.com/ser-62code.html

However, even that didn't have a solution to fit your weird example.

So, because I don't like being defeated, I worked it out for you ...
Example db attached with combo cboFilter
Text field 'Complete' including values with Yes / No (as part of text) & blanks

This code WORKS!

Code: 
Private Sub cboFilter_AfterUpdate()

Select Case Me.cboFilter.Value

Case "All"
        Me.FilterOn = False
        
Case Else
      'filter all values where cboFilter value is in Complete field string
       Me.Filter = "InStr([Complete],""" & Me.cboFilter.Value & """)>0"
       ' Debug.Print Me.Filter
        Me.FilterOn = True

End Select

End Sub
 

Attachments

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

would have worked but the quotes are wrong...

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

If a field should only be yes or no, why is it text? There is a yes/no field datatype.
 
static said:
Me.Filter = "[WSIB Employer Declaration Complete?] Like "*" & Me.ComboWSIB & "*"

would have worked but the quotes are wrong...

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

If a field should only be yes or no, why is it text? There is a yes/no field datatype.
Click to expand...

@Static
I agree that I got the first statement wrong - I hadn't tested my suggestions.

However I'm wondering whether you checked as your second statement is incorrect as well.
I'd already tried this & it doesn't work.

When using a wildcard you do NOT use single quotes

See Allen Browne's website for more info http://allenbrowne.com/ser-62code.html

Unfortunately nothing on that site worked for this particular example either!
That was why I gave the solution using InStr in post #10

It would be MUCH easier if it was a Yes/No field but as the OP explained in post #3 ....

some of the records have extra text, for example one record it yes "Yes(Jan 18/17)"
Click to expand...
 
If the field is a boolean then you should just be able to do...
Code: 
Private Sub ComboWSIB_After_Update()
   Select Case Me.ComboWSIB
      Case "Yes"
         Me.Filter = "[WSIB Employer Declaration Complete?]"
         Me.FilterOn = True
      Case "No"
         Me.Filter = "Not [WSIB Employer Declaration Complete?]"
         Me.FilterOn = True
      Case Else
         Me.FilterOn = False
   End Select
End Sub
The filter is just a boolean expression that evaluates for every row. Rows where the expression is true appear, rows where the expression is false do not. With a boolean field, all you need to use is the value of the field, and to invert that list, negate the expression.
hth
Mark
 
All true Mark - but the OP made clear that it isn't a boolean field
 
But then wouldn't the code be...
Code: 
Private Sub ComboWSIB_After_Update()
   Select Case Me.ComboWSIB
      Case "Yes", "No"
         Me.Filter = "[WSIB Employer Declaration Complete?] = '" & Me.ComboWSIB & "'"
         Me.FilterOn = True
      Case Else
         Me.FilterOn = False
   End Select
End Sub
Mark
 
No as it's a text field which sometimes includes extra words. See post 3


Sent from my iPhone using Tapatalk
 
Lol, so I'm just not reading the thread very well am I? :o
 
ridders said:
When using a wildcard you do NOT use single quotes
Click to expand...

Not true. There's no difference between single quotes or double quotes in Access. SQL Server requires single.

You might want to use double quotes if the text is more likely to contain apostrophes to save doubling them up - but that's just lazy, you should always check for and format them both.

ridders said:
It would be MUCH easier if it was a Yes/No field but as the OP explained in post #3 ....
Click to expand...

Yes but should the date be there or did somebody just key a date into the wrong field because they miss hit the tab key?
Are they adding notes wherever they can because there's no provision for them elsewhere?

:confused:

Whatever the case I'm sure it ain't right.
 
Static

I agree with the general point about single quotes but not when using wildcards.

Nor is it laziness to use double double quotes to manage apostrophes

Try your version including wildcards with my sample db which was made to match the data offered by the OP. It will NOT work.

At least we both agree that the datatype should be Boolean but the OP is managing what he was given.
 
The database you posted has SQL Server Syntax switched on.
SQL server uses % not *.
 
You must log in or register to reply here.

Similar threads

Q
Solved combo box after update event
Replies
17
Views
1,657
June7
June7
A
How to Properly Query and Search Multi-Valued Fields in a Form?
2
Replies
20
Views
1,593
MajP
MajP
tmd63
How to use a combo box to filter a forms data
Replies
2
Views
657
theDBguy
theDBguy
F
Solved Form with subform filtered by two cascading combo boxes and a checkbox
2
Replies
26
Views
1,863
Faoineag
F
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
1,171
Mike Krailo
Mike Krailo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom