Subform Query

  • Thread starter Thread starter cmichaud
  • Start date Start date
C

cmichaud

I have a form that has three unbond combo boxes (age, sex, city).
These combo boxes have a select all option and are used to fill the
parameters of a query. The query sql is
SELECT tblClubInfo.Name, tblClubInfo.Leader, tblClubInfo.ContactPhone,
tblClubInfo.ContactEmail, tblClubInfo.MeetingPlace,
tblClubInfo.MeetingOccurance, tblClubInfo.SexesID, tblClubInfo.AgesID,
tblClubInfo.CityID
FROM tblClubInfo
WHERE
(((IIf(forms!frmclubsearch!agesID="(All)","",tblClubInfo.AgesID=forms!frmcl­ubsearch!AgesID))<>False)

And
((IIf(forms!frmclubsearch!CityID="(All)","",tblClubInfo.CityID=forms!frmclu­bsearch!CityID))<>False)

And
((IIf(forms!frmclubsearch!SexesID="(All)","",tblClubInfo.SexesID=forms!frmc­lubsearch!SexesID))<>False));


i took this query and dragged it onto the form, making a query subform.
The thing is i dont know how to make it possible for me to enter
values on the form, submit it to the query, and have the results show
in the query subform....and then have the capability to do it all over
again without closing it.
Can someone give me an idea, i would appreciate it. Thanks.
 
There is an easier way. You don't need a form and subform. Make the query
form the main form (make sure it is set to default view continuous forms).
Change the query to get rid of the where statement (ie. show all data). Add
the drop-downs to the form header (or footer). Add a button for "Filter". Add
code as follows:

Filter button code:
Private Sub btnFilter_Click()
Dim strFilter as String

strFilter=""

If Not(drpAges.Value="(All)")Then
strFilter="AgesID = " & drpAges.Value
End If

If Not(drpCity.Value="(All)")Then
If (strFilter="")Then
strFilter="CityID = " & drpCity.Value
Else
strFilter=strFilter & " And CityID = " & drpCity.Value
End If
End If

If Not(drpSexes.Value="(All)")Then
If (strFilter="")Then
strFilter="SexesID = " & drpSexes.Value
Else
strFilter=strFilter & " And SexesID = " & drpSexes.Value
End If
End If

If (strFilter="")Then
Me.FilterOn = False
Else
Me.Filter = strFilter
Me.FilterOn = True
End If
End Sub

If you try this, don't forget to make a back-up copy of you db.
 
Back
Top