More Than One Filter On A Form

[bgreer5050]

I have a form with various fields with a double click event to bring
up a filter via a query. Is it possible to goto another field and
apply another filter only to the records that were found after the
first filter.

i.e.

Double Click "Vendor" and "qryfltrDblClickVendor" runs and returns
records.

Double Click "Mfr" and "qryflterMfr" runs and returns records (I do
not want a complete new filter, I want the previous "Vendor" event to
still apply)

Thanks

 

[strive4peace]

Code: AddToFilter -- Add value of current control to form filter
---

on the double-click event of the controls you wish to filter on:

'for text control
=AddTofilter("'")

'for numeric control
=AddTofilter("")

'for date control
=AddTofilter("#")

'~~~~~~~~~
Private Function AddToFilter(pDeli as string)

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare variables

dim mWhere as string
, mRecordID as long

'set value to look up by what is selected
mRecordID = Me.PrimaryKey_fieldname

mFilter = me.activecontrol.name & "=" _
& pDeli _
& me.activecontrol & pDeli

if len(trim(nz(me.filter,""))) > 0 then
me.filter = me.filter & " AND " & mwhere
else
me.filter = mwhere
end if

me.filteron = true
me.requery

'find the record you were on before
Me.RecordsetClone.FindFirst _
"PrimaryKey_fieldname= " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

end function

'~~~~~~~~~

where

PrimaryKey_fieldname is the primary key fieldname (assumeing it is long
integer data type)

pDeli is a delimiter -->
"" for numbers
"'" for text
"#" for dates

another assumption is that the NAME of the each control is the same as
the ControlSource and ytou have not used spaces or special characters in
your fieldnames (except _ is ok)

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