automated "filter via form"

[Twas]

I'd like to add command buttons that allows the user to define and perform a
filter of the current database by just entering selection criteria in
something that looks mcuh like their normal data entry form; the fiilter
would return all records that matched all fields with entries "Like" those
the user entered, but ignored fields where the user did not enter data. I
would also like to display a text box on the forms that indicates if the
dataset has been filtered or not. It seems simple, and is a simplification of
the capabilities I've used in other programs, but I haven't found a way to do
it in Access.

Any suggestions or references would be appreciated...

 

[Barry Gilbert]

Assuming your form has three fields you want to filter by, add three text
boxes and the following code in the command button's click event:

Dim strFilter As String
If Not IsNull(Me.Text1) Then
strFilter = "[Field1] = '" & Me.Text1 & "' AND "
End If
If Not IsNull(Me.Text2) Then
strFilter = strFilter & "[Field2] = '" & Me.Text2 & "' AND "
End If
If Not Isnull(Me.Text3) Then
strFilter = strFilter & "[Field3] = '" & Me.Text3
End If
If Right$(strFilter,4)="AND " Then
strFilter = Left$(strFilter,Len(strFilter)-4)
End If
Me.FilterOn = True
Me.Filter = strFilter

I hope this helps,
Barry

 

[Twas]

it's a start - but I've got about 30 fields, some text, some numeric, and
some only make sense as options. Still, it's a better start than I had.
To make this respond to Like request, I'll something try similiar to
If Not IsNull(Me.Text2) Then

strFilter = strFilter & "[Field2] Like '" & Me.Text2 & "' AND "

but this doesn't handle the cases where the user places quotes in the search
string. I could take care of those by searching the Text strings for a quote
character.

Is there an object where I can iterate through the fields and do this in a
loop?
Do I need different tests for numeric data and for text data?

-- although all this does make it remember FileMaker fondly, where I could
very easily do all this with a trivial macro that was something like
if <current mode> is find
then perform find
else
enter find mode
and that would filter on any conbination of data entries in the form,
returning results where all entered fields matched the condition (and the
user could add "or" conditions by creating additional records).

Twas

--
Twas

Assuming your form has three fields you want to filter by, add three text
boxes and the following code in the command button's click event:

Dim strFilter As String
If Not IsNull(Me.Text1) Then
strFilter = "[Field1] = '" & Me.Text1 & "' AND "
End If
If Not IsNull(Me.Text2) Then
strFilter = strFilter & "[Field2] = '" & Me.Text2 & "' AND "
End If
If Not Isnull(Me.Text3) Then
strFilter = strFilter & "[Field3] = '" & Me.Text3
End If
If Right$(strFilter,4)="AND " Then
strFilter = Left$(strFilter,Len(strFilter)-4)
End If
Me.FilterOn = True
Me.Filter = strFilter

I hope this helps,
Barry

I'd like to add command buttons that allows the user to define and perform a
filter of the current database by just entering selection criteria in
something that looks mcuh like their normal data entry form; the fiilter
would return all records that matched all fields with entries "Like" those
the user entered, but ignored fields where the user did not enter data. I
would also like to display a text box on the forms that indicates if the
dataset has been filtered or not. It seems simple, and is a simplification of
the capabilities I've used in other programs, but I haven't found a way to do
it in Access.

Any suggestions or references would be appreciated...

 

[Barry Gilbert]

You could loop through all the controls and assemble the filter string:

Dim ctlThis as Control
For Each ctlThis In Me.Controls
If Type Of CtlThis Is TextBox Then
If Not IsNull(ctlThis) Then
strFilter = strFilter & ctlThis.ControlSource & "' = '" &
ctlThis & "' AND '"
End If
End If
Loop

You could use each control's Tag property to indicate whether each text
box's underlying field is numeric. Maybe:

Dim ctlThis as Control
For Each ctlThis In Me.Controls
If Type Of CtlThis Is TextBox Then
If Not IsNull(ctlThis) Then
If ctlThis.Tag = "num" Then
strFilter = strFilter & ctlThis.ControlSource & "' = " &
ctlThis & " AND '"
Else
strFilter = strFilter & ctlThis.ControlSource & "' = '" &
ctlThis & "' AND '"
End If
End If
End If
Loop

I would consider, though, moving away from the 30 textbox model and maybe
look into a Datasheet view form and query-by-example.

Barry

it's a start - but I've got about 30 fields, some text, some numeric, and
some only make sense as options. Still, it's a better start than I had.
To make this respond to Like request, I'll something try similiar to
If Not IsNull(Me.Text2) Then

strFilter = strFilter & "[Field2] Like '" & Me.Text2 & "' AND "

but this doesn't handle the cases where the user places quotes in the search
string. I could take care of those by searching the Text strings for a quote
character.

Is there an object where I can iterate through the fields and do this in a
loop?
Do I need different tests for numeric data and for text data?

-- although all this does make it remember FileMaker fondly, where I could
very easily do all this with a trivial macro that was something like
if <current mode> is find
then perform find
else
enter find mode
and that would filter on any conbination of data entries in the form,
returning results where all entered fields matched the condition (and the
user could add "or" conditions by creating additional records).

Twas

--
Twas

Assuming your form has three fields you want to filter by, add three text
boxes and the following code in the command button's click event:

Dim strFilter As String
If Not IsNull(Me.Text1) Then
strFilter = "[Field1] = '" & Me.Text1 & "' AND "
End If
If Not IsNull(Me.Text2) Then
strFilter = strFilter & "[Field2] = '" & Me.Text2 & "' AND "
End If
If Not Isnull(Me.Text3) Then
strFilter = strFilter & "[Field3] = '" & Me.Text3
End If
If Right$(strFilter,4)="AND " Then
strFilter = Left$(strFilter,Len(strFilter)-4)
End If
Me.FilterOn = True
Me.Filter = strFilter

I hope this helps,
Barry

I'd like to add command buttons that allows the user to define and perform a
filter of the current database by just entering selection criteria in
something that looks mcuh like their normal data entry form; the fiilter
would return all records that matched all fields with entries "Like" those
the user entered, but ignored fields where the user did not enter data. I
would also like to display a text box on the forms that indicates if the
dataset has been filtered or not. It seems simple, and is a simplification of
the capabilities I've used in other programs, but I haven't found a way to do
it in Access.

Any suggestions or references would be appreciated...

 

[Twas]

A datasheet view approach with query-by-example seems like it might work, but
I'm not the result would be remotely user-friendly with 30+ fields. That many
fields fit fine on a form, but not so well on a tabular datsheet vew.

Is there a way to define command buttons that would turn on filter by
example or filter by form? I'm sure that it would be easy to define a command
button that shows all records, e.g., effectively turns off all filtering.
--
Twas

You could loop through all the controls and assemble the filter string:

Dim ctlThis as Control
For Each ctlThis In Me.Controls
If Type Of CtlThis Is TextBox Then
If Not IsNull(ctlThis) Then
strFilter = strFilter & ctlThis.ControlSource & "' = '" &
ctlThis & "' AND '"
End If
End If
Loop

You could use each control's Tag property to indicate whether each text
box's underlying field is numeric. Maybe:

Dim ctlThis as Control
For Each ctlThis In Me.Controls
If Type Of CtlThis Is TextBox Then
If Not IsNull(ctlThis) Then
If ctlThis.Tag = "num" Then
strFilter = strFilter & ctlThis.ControlSource & "' = " &
ctlThis & " AND '"
Else
strFilter = strFilter & ctlThis.ControlSource & "' = '" &
ctlThis & "' AND '"
End If
End If
End If
Loop

I would consider, though, moving away from the 30 textbox model and maybe
look into a Datasheet view form and query-by-example.

Barry

it's a start - but I've got about 30 fields, some text, some numeric, and
some only make sense as options. Still, it's a better start than I had.
To make this respond to Like request, I'll something try similiar to
If Not IsNull(Me.Text2) Then

strFilter = strFilter & "[Field2] Like '" & Me.Text2 & "' AND "

but this doesn't handle the cases where the user places quotes in the search
string. I could take care of those by searching the Text strings for a quote
character.

Is there an object where I can iterate through the fields and do this in a
loop?
Do I need different tests for numeric data and for text data?

-- although all this does make it remember FileMaker fondly, where I could
very easily do all this with a trivial macro that was something like
if <current mode> is find
then perform find
else
enter find mode
and that would filter on any conbination of data entries in the form,
returning results where all entered fields matched the condition (and the
user could add "or" conditions by creating additional records).

Twas

--
Twas

Assuming your form has three fields you want to filter by, add three text
boxes and the following code in the command button's click event:

Dim strFilter As String
If Not IsNull(Me.Text1) Then
strFilter = "[Field1] = '" & Me.Text1 & "' AND "
End If
If Not IsNull(Me.Text2) Then
strFilter = strFilter & "[Field2] = '" & Me.Text2 & "' AND "
End If
If Not Isnull(Me.Text3) Then
strFilter = strFilter & "[Field3] = '" & Me.Text3
End If
If Right$(strFilter,4)="AND " Then
strFilter = Left$(strFilter,Len(strFilter)-4)
End If
Me.FilterOn = True
Me.Filter = strFilter

I hope this helps,
Barry
:

I'd like to add command buttons that allows the user to define and perform a
filter of the current database by just entering selection criteria in
something that looks mcuh like their normal data entry form; the fiilter
would return all records that matched all fields with entries "Like" those
the user entered, but ignored fields where the user did not enter data. I
would also like to display a text box on the forms that indicates if the
dataset has been filtered or not. It seems simple, and is a simplification of
the capabilities I've used in other programs, but I haven't found a way to do
it in Access.

Any suggestions or references would be appreciated...

 

[Barry Gilbert]

You can turn this stuff on and off programmatically. Look into
DoCmd.RunCommand.

Barry

A datasheet view approach with query-by-example seems like it might work, but
I'm not the result would be remotely user-friendly with 30+ fields. That many
fields fit fine on a form, but not so well on a tabular datsheet vew.

Is there a way to define command buttons that would turn on filter by
example or filter by form? I'm sure that it would be easy to define a command
button that shows all records, e.g., effectively turns off all filtering.
--
Twas

You could loop through all the controls and assemble the filter string:

Dim ctlThis as Control
For Each ctlThis In Me.Controls
If Type Of CtlThis Is TextBox Then
If Not IsNull(ctlThis) Then
strFilter = strFilter & ctlThis.ControlSource & "' = '" &
ctlThis & "' AND '"
End If
End If
Loop

You could use each control's Tag property to indicate whether each text
box's underlying field is numeric. Maybe:

Dim ctlThis as Control
For Each ctlThis In Me.Controls
If Type Of CtlThis Is TextBox Then
If Not IsNull(ctlThis) Then
If ctlThis.Tag = "num" Then
strFilter = strFilter & ctlThis.ControlSource & "' = " &
ctlThis & " AND '"
Else
strFilter = strFilter & ctlThis.ControlSource & "' = '" &
ctlThis & "' AND '"
End If
End If
End If
Loop

I would consider, though, moving away from the 30 textbox model and maybe
look into a Datasheet view form and query-by-example.

Barry

it's a start - but I've got about 30 fields, some text, some numeric, and
some only make sense as options. Still, it's a better start than I had.
To make this respond to Like request, I'll something try similiar to
If Not IsNull(Me.Text2) Then
strFilter = strFilter & "[Field2] Like '" & Me.Text2 & "' AND "
but this doesn't handle the cases where the user places quotes in the search
string. I could take care of those by searching the Text strings for a quote
character.

Is there an object where I can iterate through the fields and do this in a
loop?
Do I need different tests for numeric data and for text data?

-- although all this does make it remember FileMaker fondly, where I could
very easily do all this with a trivial macro that was something like
if <current mode> is find
then perform find
else
enter find mode
and that would filter on any conbination of data entries in the form,
returning results where all entered fields matched the condition (and the
user could add "or" conditions by creating additional records).

Twas

--
Twas


:

Assuming your form has three fields you want to filter by, add three text
boxes and the following code in the command button's click event:

Dim strFilter As String
If Not IsNull(Me.Text1) Then
strFilter = "[Field1] = '" & Me.Text1 & "' AND "
End If
If Not IsNull(Me.Text2) Then
strFilter = strFilter & "[Field2] = '" & Me.Text2 & "' AND "
End If
If Not Isnull(Me.Text3) Then
strFilter = strFilter & "[Field3] = '" & Me.Text3
End If
If Right$(strFilter,4)="AND " Then
strFilter = Left$(strFilter,Len(strFilter)-4)
End If
Me.FilterOn = True
Me.Filter = strFilter

I hope this helps,
Barry
:

I'd like to add command buttons that allows the user to define and perform a
filter of the current database by just entering selection criteria in
something that looks mcuh like their normal data entry form; the fiilter
would return all records that matched all fields with entries "Like" those
the user entered, but ignored fields where the user did not enter data. I
would also like to display a text box on the forms that indicates if the
dataset has been filtered or not. It seems simple, and is a simplification of
the capabilities I've used in other programs, but I haven't found a way to do
it in Access.

Any suggestions or references would be appreciated...