Filtering a subform

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

I have run into problems in creating a records database for a scout group I
work with.
I have a form allowing me to select any number of people via a yes/no box.
The lists of people appear in a subform named subActiveMembers
The people are grouped in sections and I have tabs across the top of the
form to permit switching between sections.
I tried to do this by having some VBA that applied a filter on the section
depending upon the tab clicked.
My problem is that I cannot seem to get the filter to work.
I can insert the SQL in the subform (despite getting an error saying that I
cannot assign a value to the object) but I cannot activate the filter.
I have spent all day at this and it is driving me nuts.
VBA is listed below.
Any help would be very gratefully received.

Regards

Andy


Private Sub TabCtl0_Change()

'On selection of a tab, apply a filter based upon the value of that tab

Dim strSQL As String

strSQL = " SELECT tblMemberSelection.* " _
& " FROM tblMemberSelection " _
& " WHERE ((tblMemberSelection.Section)="

Select Case Me.TabCtl0
Case 0: strSQL = strSQL & """Beavers - Hudson"""
Case 1: strSQL = strSQL & """Beavers - McKenzie"""
Case 2: strSQL = strSQL & """Cubs - Arran"""
Case 3: strSQL = strSQL & """Cubs - Skye"""
Case 4: strSQL = strSQL & """Scouts"""
Case 5: strSQL = strSQL & """Explorers"""
Case 6: strSQL = strSQL & """Network"""
Case 7: strSQL = strSQL & """Group : Leader"""
Case 8: strSQL = strSQL & """Group : Fellowship"""
Case 9: strSQL = strSQL & """Group Executive"""
End Select

strSQL = strSQL & ");"

Me!subActiveMembers.Form.Filter = strSQL
Me!subActiveMembers.Form.FilterOn = True

Me.Refresh

End Sub
 
Andy said:
I have run into problems in creating a records database for a scout group I
work with.
I have a form allowing me to select any number of people via a yes/no box.
The lists of people appear in a subform named subActiveMembers
The people are grouped in sections and I have tabs across the top of the
form to permit switching between sections.
I tried to do this by having some VBA that applied a filter on the section
depending upon the tab clicked.
My problem is that I cannot seem to get the filter to work.
I can insert the SQL in the subform (despite getting an error saying that
I cannot assign a value to the object) but I cannot activate the filter.
I have spent all day at this and it is driving me nuts.
VBA is listed below.
Any help would be very gratefully received.

Regards

Andy


Private Sub TabCtl0_Change()

'On selection of a tab, apply a filter based upon the value of that tab

Dim strSQL As String

strSQL = " SELECT tblMemberSelection.* " _
& " FROM tblMemberSelection " _
& " WHERE ((tblMemberSelection.Section)="

Select Case Me.TabCtl0
Case 0: strSQL = strSQL & """Beavers - Hudson"""
Case 1: strSQL = strSQL & """Beavers - McKenzie"""
Case 2: strSQL = strSQL & """Cubs - Arran"""
Case 3: strSQL = strSQL & """Cubs - Skye"""
Case 4: strSQL = strSQL & """Scouts"""
Case 5: strSQL = strSQL & """Explorers"""
Case 6: strSQL = strSQL & """Network"""
Case 7: strSQL = strSQL & """Group : Leader"""
Case 8: strSQL = strSQL & """Group : Fellowship"""
Case 9: strSQL = strSQL & """Group Executive"""
End Select

strSQL = strSQL & ");"

Me!subActiveMembers.Form.Filter = strSQL
Me!subActiveMembers.Form.FilterOn = True

Me.Refresh

End Sub
Click to expand...

A filter should be just the WHERE clause of an SQL statement (without the
word WHERE). Remove everything up to and including WHERE and try that.

An alternative is to actually change the RecordSource of the subform. In
that case, the full SQL statement would be used.

Carl Rapson
 
Carl Rapson said:
A filter should be just the WHERE clause of an SQL statement (without the
word WHERE). Remove everything up to and including WHERE and try that.

An alternative is to actually change the RecordSource of the subform. In
that case, the full SQL statement would be used.
Click to expand...

Thanks for the reply.

I did try to change the child item of the subform to a query an
prgramatically changing that underlying query depending upon the tab clicked
but, although the query changes the subform does not refresh.

The extra SQL in the filter is a hangover from that. Bad programming on my
part and a bit embarassing to post it to a global forum for all to see
without deleting it. Oooops.

Andy
 
Back
Top