Filtering Records

[eschlep via AccessMonster.com]

I have a form that lists records and i use a combo box in order to filter the
records. When i place the record in the detail section i want it to display
as continous form. When i try this it shows all of the records and does not
filter them. Is there a way to show only the filtered results as a continous
form in the detail section with out using subforms.
Thanks

 

[strive4peace]

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

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, 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 SetFormFilter 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
*

 

[eschlep via AccessMonster.com]

Thanks that fixed one of the problems i was having however i still have one
problem. This fixed it so that it filters the records correctly at the top
of the detail field. Now i have text boxes and labels that i want to display
below the records but i only want it to display once. Right now it displays
it once per record. I tried putting it in the footer however then i dont
have scroll bars so i cant see it all. Any ideas that i can display this
text only once without using the footer. Thanks

 

[strive4peace]

"I tried putting it in the footer however then i dont
have scroll bars so i cant see it all. "

why not make 2 or more lines in the form footer instead of putting it
all on one?

you can also use the form header

if you put it into the detail section, it will display for every record...

or maybe, you meant that the detail takes so much space once you use the
form footer? if so, change the Height property of the detail section
to be smaller ... not really sure what you mean...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*