Help with this filtering.. Please help ?

[RON]

Hello everyone, I am aware that Access has built in filtering but I want to
build my own filter into access using a popup dialog box.

I have a bound form lets call it frmStudentFilter
this form just has entries from my student table like Name, Sex, Class,
etc...

What I want to do is put a button on the frmStudentFilter form that will
open up a popup dialog box that will essentially be a filter. An option
group with radio button selections for ALL and Gender and CLASS will be
there, next to the GENDER and CLASS Radio Buttons will be drop down, next
to Gender the drop down will be either Male or Female and the CLASS Dropdown
will be either freshman, senior.

For example when the Gender button is selected and Male is chosen in the
dropdown and the filter button is pressed, I want frmStudentFilter to pop up
again but only listing the Males.

I have this somewhat working but all on the same form, right now I have an
option group with All and Gender and the dfrop down next to Gender....but it
is on the frmStudentFilter form. Here is the CODE, and it is working fine.

Private Sub Frame6_AfterUpdate()
Select Case Frame6
Case 1
lblfilter.Caption = "Filter by All"
MsgBox "filter by all"
Me.FilterOn = False
Case 2
lblfilter.Caption = "Filter by Gender"
MsgBox "Filter by Gender"
End Select


End Sub

How do I do the same with but with a pop up form that then opens up the
frmStudentFilter form

 

[Guest]

well....my approach is to use a Query.

Assuming each value is a field in a table...then one can enter the filtering
Criteria in the design view of the Query.

Presuming you don't want them directly in a Query's Design View - you set it
up so the values they select or enter in the Form are called by the query.
To do this you put this syntax in the Query's field/column criteria field:

Forms!FormName.Control

where Forms! is literal - you must put that as-is and then FormName is
your form name and Control is whatever textbox,combobox,checkbox name....

ps. The form must be open when you run the query so that the query can call
those values over....

 

[RON]

yes thats my fault, the form that displays the info is based on a query from
the STUDENTS table with NAME, GENER, CLASS, State on the form.

 

[RON]

so is what your saying is I can write a query to do filtering, basically set
the GENDERE to F in the query and then when Female is picked in the combo
box, put in the code that you have?

 

[Guest]

sure...a query is the ultimate filter....

if you had 10 columns i.e. 10 parameters - and you put in a criteria for
each...the query will return only that/those records that correspond....

Put in F in the Gender field it will return all with F

Put >25 in the age field also ....now it will return all F that are also
older than 25

and so on and so on....

Make it a 2 step implementation process....first step is be sure it is all
working as you expect directly in query design view by typing in the values
in the criteria yourself...and sanity checking the resulting records when you
!

then step 2 is calling the values over from a Form's textboxes into the
query....

Sounds like you already have the 'results Form' ie. based on the query
results...and you may need to whip up a preliminary Form to prompt for all
the values that the query is going to need.....not sure - that is just a
guess....

 

[RON]

OK here is what I have for one of my comboboxes, the one that deals with
gender. This works fine because the option group and thecombobox are on the
same form that is being filtered, I guess how would I modify this so that
it works when I put the option groups and combo boxes on a seperate pop up
form and still want it to update the student form

Private Sub cmbGender_AfterUpdate()
If cmbGender.Text = "male" Then
Me.Filter = "studentsex = 'M'"
MsgBox ("male")
Else
Me.Filter = "StudentSex = 'F'"
MsgBox ("female")
End If
Me.FilterOn = True
End Sub

thanks

 

[John Vinson]

OK here is what I have for one of my comboboxes, the one that deals with
gender. This works fine because the option group and thecombobox are on the
same form that is being filtered, I guess how would I modify this so that
it works when I put the option groups and combo boxes on a seperate pop up
form and still want it to update the student form

Private Sub cmbGender_AfterUpdate()
If cmbGender.Text = "male" Then
Me.Filter = "studentsex = 'M'"
MsgBox ("male")
Else
Me.Filter = "StudentSex = 'F'"
MsgBox ("female")
End If
Me.FilterOn = True
End Sub

It is actually simpler, both for you and for the user, to have unbound
controls (combos, textboxes, checkboxes, etc.) on the form that
displays the data; you can revise the form's Filter property in the
afterupdate event, just as you're doing.

If you do want to use a separate form, then you can either build a
complete SQL string and assign it as the Recordsource of your
data-display form, or alter the Filter property of the other form.
Instead of Me.Filter just use

Forms!yourformname.Filter =


John W. Vinson[MVP]