Set Form Filter
---
Hi Aurora,
you can use a form filter
here is a generic example:
put comboboxes, textboxes, listboxes, etc on the form (i put then in
the header). Assign this to the AfterUpdate event of each one...
=SetFormFilter()
then put this code behind the form
'~~~~~~~~~~~~~~~
Private Function SetFormFilter()
dim mFilter as string
mFilter = ""
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" _
& me.controlname_for_text & "'"
end if
If not IsNull(me.date_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if
If not IsNull(me.numeric_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if
'--------------- Filter form you are behind
if len(mfilter) > 0 then
me.filter = mfilter
me.FilterOn = true
else
me.FilterOn = false
end if
me.requery
End Function
'~~~~~~~~~~~~~~~
me.controlname_for_number refers to the NAME property of a control on
the form you are behind (Me. represents the form -- kinda like "me" for
me is not "me" for you
)
delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates
mfilter is a string that is being built for each condition -- but if
nothing is specified in the filter control (IsNull), then that addition
to the filter string is skipped.
finally, when the filter string is done, it is applied to your form.
That means that as you flip through records, ONLY records matching that
filter will show
Then, if desired, put another command button on the form
Name --> btnShowAll
OnClick --> [Event Procedure]
'~~~~~~~~~~~~~~~
me.filteron = false
me.requery
'~~~~~~~~~~~~~~~
a requery of the form will also reset the record pointer back to the
first record.
If you want to capture the record you are on and set it back, you can do
this:
'~~~~~~~~~~~~~~~
'put this at the top of the code
'save value of primary key
dim mPrimaryKey as long
mPrimaryKey = 0
if not me.newrecord then
mPrimaryKey = nz(me.mPrimaryKey_controlname)
end if
'then, after the code sets the filter...
if mPrimaryKey = 0 then exit function
'go back to record you were on
Me.RecordsetClone.FindFirst _
"mPrimaryKey_fieldname = " & mPrimaryKey
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
'~~~~~~~~~~~~~~~
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
