Filtering records with toggle option group

[delgado92651]

Hi

I'm trying to filter a group of records by first name using a group
option control, options in this example are Jim, Mike & All.
The option control is on the detail section of the main form, also in
the same section is the sub form I'm trying to filter. The subform is
in data sheet view (if that makes any difference). When I press the
toggle buttons nothing happens. I tried putting the toggle option
group in the header of the sub form but I can't get it to display
there, I think I remember reading someplace in datasheet view you
can't see the footer or header?


Have the following code in the After Update [Event Procedure] of the
filterOptionGroup control

Private Sub filterOptionGroup_AfterUpdate()
With Forms!frmMain
Select Case Me.filterOptionGroup
Case 1
..RecordSource = "SELECT * FROM frmMain WHERE FirstName Not Like 'Jim'"
Case 2
..RecordSource = "SELECT * FROM frmMain WHERE FirstName Like 'Mike'"
Case 3
..RecordSource = "frmMain"
End Select
End With
End Sub

Thanks
Al

 

[tina]

the option group control is in the main form, correct? and the code is
running in the main form's module, correct? and you're trying to change the
RecordSource of the subform form object, correct? (that's not "filtering",
by the way - filtering is a specific action.)

if the above are all correct, suggest you try the following changes to your
code, as

Private Sub filterOptionGroup_AfterUpdate()

Dim frm as Form

Set frm = Me!NameOfSubformControl.Form

Select Case Me!filterOptionGroup
Case 1
frm.RecordSource = "SELECT * FROM frmMain WHERE FirstName Not
Like 'Jim'"
' the above SQL statement goes all on one line, regardless
' of line-wrap here in the post.
Case 2
frm.RecordSource = "SELECT * FROM frmMain WHERE FirstName Like
'Mike'"
' the above SQL statement goes all on one line, regardless
' of line-wrap here in the post.
Case 3
frm.RecordSource = "frmMain"
End Select

frm.Requery

End Sub

and btw, in looking at the SQL statement you're using as the subform's
RecordSource - do you really have a table or query named "frmMain"? if so,
fine; you can name a table or query anything that suits you. but if not,
then the RecordSource in each Case is not valid; can you explain what you're
trying to do there?

hth

 

[John Vinson]

Hi

I'm trying to filter a group of records by first name using a group
option control, options in this example are Jim, Mike & All.
The option control is on the detail section of the main form, also in
the same section is the sub form I'm trying to filter. The subform is
in data sheet view (if that makes any difference). When I press the
toggle buttons nothing happens. I tried putting the toggle option
group in the header of the sub form but I can't get it to display
there, I think I remember reading someplace in datasheet view you
can't see the footer or header?

A couple of questions:

Private Sub filterOptionGroup_AfterUpdate()
With Forms!frmMain
Select Case Me.filterOptionGroup
Case 1
..RecordSource = "SELECT * FROM frmMain WHERE FirstName Not Like 'Jim'"

Why the "Not"? The first option will select Mike, just as the second
will...

In either case, you don't want to use the LIKE operator. LIKE
recognizes wildcards, and you're not using any wildcards. Just use the
= operator.

Case 2
..RecordSource = "SELECT * FROM frmMain WHERE FirstName Like 'Mike'"
Case 3
..RecordSource = "frmMain"

A Recordsource must be a Table or Query - not the name of a form; is
your table named frmMain perchance?

End Select
End With
End Sub

You may need a Requery after (correctly) doing the selection.

An alternative might be to set the form's Filter property:

Select Case Me.filterOptionGroup
Case 1
Me.Filter = "[FirstName] = 'Mike'"
Me.FilterOn = True
Case 2
Me.Filter = "[FirstName] = 'Jim'"
Me.FilterOn = True
Case 3
Me.Filter = ""
Me.FilterOn = False
End Select


John W. Vinson[MVP]

 

[delgado92651]

Hi

I'm trying to filter a group of records by first name using a group
option control, options in this example are Jim, Mike & All.
The option control is on the detail section of the main form, also in
the same section is the sub form I'm trying to filter. The subform is
in data sheet view (if that makes any difference). When I press the
toggle buttons nothing happens. I tried putting the toggle option
group in the header of the sub form but I can't get it to display
there, I think I remember reading someplace in datasheet view you
can't see the footer or header?A couple of questions:

Private Sub filterOptionGroup_AfterUpdate()
With Forms!frmMain
Select Case Me.filterOptionGroup
Case 1
.RecordSource = "SELECT * FROM frmMain WHERE FirstName Not Like 'Jim'"

Why the "Not"? The first option will select Mike, just as the second
will...

In either case, you don't want to use the LIKE operator. LIKE
recognizes wildcards, and you're not using any wildcards. Just use the
= operator.

Case 2
.RecordSource = "SELECT * FROM frmMain WHERE FirstName Like 'Mike'"
Case 3
.RecordSource = "frmMain"

A Recordsource must be a Table or Query - not the name of a form; is
your table named frmMain perchance?

End Select
End With
End Sub

You may need a Requery after (correctly) doing the selection.

An alternative might be to set the form's Filter property:

Select Case Me.filterOptionGroup
Case 1
Me.Filter = "[FirstName] = 'Mike'"
Me.FilterOn = True
Case 2
Me.Filter = "[FirstName] = 'Jim'"
Me.FilterOn = True
Case 3
Me.Filter = ""
Me.FilterOn = False
End Select

John W. Vinson[MVP]

Thanks for the help hth and John.
hth very good, I get the tina.

I used the alternative method and it worked great.

If I create a second option group that will work on a second field,
will it conflict with the first or will it select records from the
existing record set?

Thanks
Al