Filtering Via A Form

B

bgreer5050

I have a subform that I use in datasheet view. The fields are as
follow:

Mfr
PartNo
Machine


Is there a way to have a double click event on each one to filter
based on the content of the field double clicked? I would also like
to
be able to keep this filter in place and goto the field and filter
based on its content (Parametric Filter).
 
S

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 (assuming 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 you have not used spaces or special characters in
your fieldnames (except _ is ok)



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