Create dynamic filter in a tabular form.

[jake7363]

Hello,

I'm a bit shy on the experience level, perhaps slightly past novice. So, the question I have might seem peculiar, but here goes...

I am trying to create a filter within a tabular form that allows the user to enter the criteria in a text box for a given field. Example: Calling the field "Vendor" and a list of vendors populate that field. I would like the user to enter "Amazon" within the text box so the tabular form shows the result of all records for which the Vendor is Amazon. The filter can be cleared, allowing a return to the full dataset. Perhaps the next time, the Vendor is "Home Depot," and so on.

Is this possible? If so, how is it done?
I'll be grateful for all replies...

Cheers,


Jake

[ranman256]

in the header part of the continuous form (or other)
put in the unbound boxes to filter by.
then when user clicks FIND (or other word), set the filter:

Public Sub btnFIND_Click()
Dim sSql As String, sWhere As String


sWhere = "1=1"


    'the query is built depending on the various filters the user picks...
If Not IsNull(cboState) Then sWhere = sWhere & " and [state]='" & cboState & "'"
If Not IsNull(txtName) Then sWhere = sWhere & " and [Name]='" & txtName & "'"
If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value


 'use it to filer the form records
if sWhere = "1=1" then
  me.filterOn = false
else
  me.filter = sWhere
  me.filterOn = true
endif
END SUB

[jake7363]

Thanks for the quick post! I'll give it a run and see how it does...

Cheers.