Search facility

[Guest]

I am trying to provide a user with a search facility on a customer form.
Nothing fancy, just the ability to enter multiple criteria on the form and
then hit a button that basically says Search.
How, please, do I do this???
Has anyone got a good example from start to end.
In the Tools>Startup I have tried to switch off all menus etc so that the
user cannot do anything silly.
I have created a customized toolbar for 'search by form' called Search but
when the form opens, my search icon is innactive.
Can someone please help as this is really causing me problems.

 

[Allen Browne]

The inferface I like to give my users consists of several unbound controls
in the Form Header section of the form, along with a pair of command buttons
for Filter and Remove Filter. This Filter button's Click event procedure
builds up a string to use as the Filter of the form, based on the controls
where the user entered something.

The code for the Click event of the command button looks something like
this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first

'Build up there filter string from the non-blank text boxes.
If Not IsNull(Me.txtFilterSurname) Then 'text field example.
strWhere = strWhere & "([Surname] = """ & _
Me.txtFilterSurname & """) AND "
End If

If Not IsNull(Me.txtFilterAmount) Then 'number field example
strWhere = strWhere & "([Amount] = " & _
Me.txtFilterAmount & ") AND "
End If

If Not IsNull(Me.txtFilterBirthdate) Then 'date field example
strWhere = strWhere & "([Birthdate] = " & _
Format(Me.txtFilterBirthdate, conJetDateFormat) & ") AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Enter some criteria first."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = False
End If
End Sub

 

[Guest]

Hi Allen,
I do not want to do this in VB code.
I would rather use the Access routine Filter by Form if I can.
I have an example of how to do the search 'long hand' but I thought it would
be easy to open the form in Filter by Form mode and let Access do the rest.
My Filter by Form icon seems to be disabled????
The user wants to do a filter on a filter, narrowing down the data until
they find what they want.
Am I asking too much???
So far, I have spent all day trying to get, what seems so simple, to work!!!!
Is the only solution to this to create my own search in VB???
According to the manuals that I have, Access should eat this.

The inferface I like to give my users consists of several unbound controls
in the Form Header section of the form, along with a pair of command buttons
for Filter and Remove Filter. This Filter button's Click event procedure
builds up a string to use as the Filter of the form, based on the controls
where the user entered something.

The code for the Click event of the command button looks something like
this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first

'Build up there filter string from the non-blank text boxes.
If Not IsNull(Me.txtFilterSurname) Then 'text field example.
strWhere = strWhere & "([Surname] = """ & _
Me.txtFilterSurname & """) AND "
End If

If Not IsNull(Me.txtFilterAmount) Then 'number field example
strWhere = strWhere & "([Amount] = " & _
Me.txtFilterAmount & ") AND "
End If

If Not IsNull(Me.txtFilterBirthdate) Then 'date field example
strWhere = strWhere & "([Birthdate] = " & _
Format(Me.txtFilterBirthdate, conJetDateFormat) & ") AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Enter some criteria first."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = False
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to provide a user with a search facility on a customer form.
Nothing fancy, just the ability to enter multiple criteria on the form and
then hit a button that basically says Search.
How, please, do I do this???
Has anyone got a good example from start to end.
In the Tools>Startup I have tried to switch off all menus etc so that the
user cannot do anything silly.
I have created a customized toolbar for 'search by form' called Search but
when the form opens, my search icon is innactive.
Can someone please help as this is really causing me problems.

 

[Allen Browne]

The FilterByForm button on the toolbar should be enabled unless the form is
unbound or you have set its AllowFilters property to No.

If you want to use a command button to enter Filter by Form, use:
RunCommand acCmdFilterByForm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I do not want to do this in VB code.
I would rather use the Access routine Filter by Form if I can.
I have an example of how to do the search 'long hand' but I thought it
would
be easy to open the form in Filter by Form mode and let Access do the
rest.
My Filter by Form icon seems to be disabled????
The user wants to do a filter on a filter, narrowing down the data until
they find what they want.
Am I asking too much???
So far, I have spent all day trying to get, what seems so simple, to
work!!!!
Is the only solution to this to create my own search in VB???
According to the manuals that I have, Access should eat this.

The inferface I like to give my users consists of several unbound
controls
in the Form Header section of the form, along with a pair of command
buttons
for Filter and Remove Filter. This Filter button's Click event procedure
builds up a string to use as the Filter of the form, based on the
controls
where the user entered something.

The code for the Click event of the command button looks something like
this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first

'Build up there filter string from the non-blank text boxes.
If Not IsNull(Me.txtFilterSurname) Then 'text field example.
strWhere = strWhere & "([Surname] = """ & _
Me.txtFilterSurname & """) AND "
End If

If Not IsNull(Me.txtFilterAmount) Then 'number field example
strWhere = strWhere & "([Amount] = " & _
Me.txtFilterAmount & ") AND "
End If

If Not IsNull(Me.txtFilterBirthdate) Then 'date field example
strWhere = strWhere & "([Birthdate] = " & _
Format(Me.txtFilterBirthdate, conJetDateFormat) & ") AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Enter some criteria first."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = False
End If
End Sub

I am trying to provide a user with a search facility on a customer form.
Nothing fancy, just the ability to enter multiple criteria on the form
and
then hit a button that basically says Search.
How, please, do I do this???
Has anyone got a good example from start to end.
In the Tools>Startup I have tried to switch off all menus etc so that
the
user cannot do anything silly.
I have created a customized toolbar for 'search by form' called Search
but
when the form opens, my search icon is innactive.
Can someone please help as this is really causing me problems.

 

[Guest]

Thanks for trying to help me Allen.
I have a Main Form and on it is a button named 'Search Customers'.
When this is clicked I have an event procedure that opens the customer form
and also runs the Filter command.
This is the code.....
DoCmd.OpenForm "Find Customers"
RunCommand acCmdFilterByForm
I have created a menu bar icon for 'apply filter' which is invoked on the
loading of the Find Customers form.
When the form loads, all the fields are blank, I can enter criteria into
any/all of the fields, but the icon for applying the filter is grayed out.
I cannot click on it to produce the results based on the entered criteria.
I have checked and the form is bound to the Customers table and the Allow
Filters is set to Yes.
What am I doing wrong????
Good job I am on the ground floor cos this is driving me mad
Help!!

The FilterByForm button on the toolbar should be enabled unless the form is
unbound or you have set its AllowFilters property to No.

If you want to use a command button to enter Filter by Form, use:
RunCommand acCmdFilterByForm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I do not want to do this in VB code.
I would rather use the Access routine Filter by Form if I can.
I have an example of how to do the search 'long hand' but I thought it
would
be easy to open the form in Filter by Form mode and let Access do the
rest.
My Filter by Form icon seems to be disabled????
The user wants to do a filter on a filter, narrowing down the data until
they find what they want.
Am I asking too much???
So far, I have spent all day trying to get, what seems so simple, to
work!!!!
Is the only solution to this to create my own search in VB???
According to the manuals that I have, Access should eat this.

The inferface I like to give my users consists of several unbound
controls
in the Form Header section of the form, along with a pair of command
buttons
for Filter and Remove Filter. This Filter button's Click event procedure
builds up a string to use as the Filter of the form, based on the
controls
where the user entered something.

The code for the Click event of the command button looks something like
this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first

'Build up there filter string from the non-blank text boxes.
If Not IsNull(Me.txtFilterSurname) Then 'text field example.
strWhere = strWhere & "([Surname] = """ & _
Me.txtFilterSurname & """) AND "
End If

If Not IsNull(Me.txtFilterAmount) Then 'number field example
strWhere = strWhere & "([Amount] = " & _
Me.txtFilterAmount & ") AND "
End If

If Not IsNull(Me.txtFilterBirthdate) Then 'date field example
strWhere = strWhere & "([Birthdate] = " & _
Format(Me.txtFilterBirthdate, conJetDateFormat) & ") AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Enter some criteria first."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = False
End If
End Sub

I am trying to provide a user with a search facility on a customer form.
Nothing fancy, just the ability to enter multiple criteria on the form
and
then hit a button that basically says Search.
How, please, do I do this???
Has anyone got a good example from start to end.
In the Tools>Startup I have tried to switch off all menus etc so that
the
user cannot do anything silly.
I have created a customized toolbar for 'search by form' called Search
but
when the form opens, my search icon is innactive.
Can someone please help as this is really causing me problems.

 

[Allen Browne]

So far, you have succeeded in opening a form named "Find Customers" in
Filter By Form mode.

This form has some controls, where you are able to enter some values, but
your button is greyed out. If this button is on the toolbar, it would be
greyed out if the form is unbound, i.e. if it has nothing in its
RecordSource property. If this button is on the form, it will always be
greyed out in Filter By Form, because no code runs in this mode, so the
button's Click event procedure cannot possibly work.

I'm not sure why you opened a Find Customers form. If you did not want to
write any code, you could have just used:
RunCommand acCmdFilterByForm
on the original form to switch it to filter by form mode. Since it was
(presumably) a bound form, the standard form toolbar button would then have
allowed you to apply the filter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for trying to help me Allen.
I have a Main Form and on it is a button named 'Search Customers'.
When this is clicked I have an event procedure that opens the customer
form
and also runs the Filter command.
This is the code.....
DoCmd.OpenForm "Find Customers"
RunCommand acCmdFilterByForm
I have created a menu bar icon for 'apply filter' which is invoked on the
loading of the Find Customers form.
When the form loads, all the fields are blank, I can enter criteria into
any/all of the fields, but the icon for applying the filter is grayed out.
I cannot click on it to produce the results based on the entered criteria.
I have checked and the form is bound to the Customers table and the Allow
Filters is set to Yes.
What am I doing wrong????
Good job I am on the ground floor cos this is driving me mad
Help!!

The FilterByForm button on the toolbar should be enabled unless the form
is
unbound or you have set its AllowFilters property to No.

If you want to use a command button to enter Filter by Form, use:
RunCommand acCmdFilterByForm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I do not want to do this in VB code.
I would rather use the Access routine Filter by Form if I can.
I have an example of how to do the search 'long hand' but I thought it
would
be easy to open the form in Filter by Form mode and let Access do the
rest.
My Filter by Form icon seems to be disabled????
The user wants to do a filter on a filter, narrowing down the data
until
they find what they want.
Am I asking too much???
So far, I have spent all day trying to get, what seems so simple, to
work!!!!
Is the only solution to this to create my own search in VB???
According to the manuals that I have, Access should eat this.

:

The inferface I like to give my users consists of several unbound
controls
in the Form Header section of the form, along with a pair of command
buttons
for Filter and Remove Filter. This Filter button's Click event
procedure
builds up a string to use as the Filter of the form, based on the
controls
where the user entered something.

The code for the Click event of the command button looks something
like
this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first

'Build up there filter string from the non-blank text boxes.
If Not IsNull(Me.txtFilterSurname) Then 'text field example.
strWhere = strWhere & "([Surname] = """ & _
Me.txtFilterSurname & """) AND "
End If

If Not IsNull(Me.txtFilterAmount) Then 'number field example
strWhere = strWhere & "([Amount] = " & _
Me.txtFilterAmount & ") AND "
End If

If Not IsNull(Me.txtFilterBirthdate) Then 'date field example
strWhere = strWhere & "([Birthdate] = " & _
Format(Me.txtFilterBirthdate, conJetDateFormat) & ") AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Enter some criteria first."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = False
End If
End Sub

I am trying to provide a user with a search facility on a customer
form.
Nothing fancy, just the ability to enter multiple criteria on the
form
and
then hit a button that basically says Search.
How, please, do I do this???
Has anyone got a good example from start to end.
In the Tools>Startup I have tried to switch off all menus etc so
that
the
user cannot do anything silly.
I have created a customized toolbar for 'search by form' called
Search
but
when the form opens, my search icon is innactive.
Can someone please help as this is really causing me problems.

 

[Guest]

So far, you have succeeded in opening a form named "Find Customers" in
Filter By Form mode.

This form has some controls, where you are able to enter some values, but
your button is greyed out. If this button is on the toolbar, it would be
greyed out if the form is unbound, i.e. if it has nothing in its
RecordSource property. If this button is on the form, it will always be
greyed out in Filter By Form, because no code runs in this mode, so the
button's Click event procedure cannot possibly work.

I'm not sure why you opened a Find Customers form. If you did not want to
write any code, you could have just used:
RunCommand acCmdFilterByForm
on the original form to switch it to filter by form mode. Since it was
(presumably) a bound form, the standard form toolbar button would then have
allowed you to apply the filter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for trying to help me Allen.
I have a Main Form and on it is a button named 'Search Customers'.
When this is clicked I have an event procedure that opens the customer
form
and also runs the Filter command.
This is the code.....
DoCmd.OpenForm "Find Customers"
RunCommand acCmdFilterByForm
I have created a menu bar icon for 'apply filter' which is invoked on the
loading of the Find Customers form.
When the form loads, all the fields are blank, I can enter criteria into
any/all of the fields, but the icon for applying the filter is grayed out.
I cannot click on it to produce the results based on the entered criteria.
I have checked and the form is bound to the Customers table and the Allow
Filters is set to Yes.
What am I doing wrong????
Good job I am on the ground floor cos this is driving me mad
Help!!

The FilterByForm button on the toolbar should be enabled unless the form
is
unbound or you have set its AllowFilters property to No.

If you want to use a command button to enter Filter by Form, use:
RunCommand acCmdFilterByForm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I do not want to do this in VB code.
I would rather use the Access routine Filter by Form if I can.
I have an example of how to do the search 'long hand' but I thought it
would
be easy to open the form in Filter by Form mode and let Access do the
rest.
My Filter by Form icon seems to be disabled????
The user wants to do a filter on a filter, narrowing down the data
until
they find what they want.
Am I asking too much???
So far, I have spent all day trying to get, what seems so simple, to
work!!!!
Is the only solution to this to create my own search in VB???
According to the manuals that I have, Access should eat this.

:

The inferface I like to give my users consists of several unbound
controls
in the Form Header section of the form, along with a pair of command
buttons
for Filter and Remove Filter. This Filter button's Click event
procedure
builds up a string to use as the Filter of the form, based on the
controls
where the user entered something.

The code for the Click event of the command button looks something
like
this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first

'Build up there filter string from the non-blank text boxes.
If Not IsNull(Me.txtFilterSurname) Then 'text field example.
strWhere = strWhere & "([Surname] = """ & _
Me.txtFilterSurname & """) AND "
End If

If Not IsNull(Me.txtFilterAmount) Then 'number field example
strWhere = strWhere & "([Amount] = " & _
Me.txtFilterAmount & ") AND "
End If

If Not IsNull(Me.txtFilterBirthdate) Then 'date field example
strWhere = strWhere & "([Birthdate] = " & _
Format(Me.txtFilterBirthdate, conJetDateFormat) & ") AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Enter some criteria first."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = False
End If
End Sub

I am trying to provide a user with a search facility on a customer
form.
Nothing fancy, just the ability to enter multiple criteria on the
form
and
then hit a button that basically says Search.
How, please, do I do this???
Has anyone got a good example from start to end.
In the Tools>Startup I have tried to switch off all menus etc so
that
the
user cannot do anything silly.
I have created a customized toolbar for 'search by form' called
Search
but
when the form opens, my search icon is innactive.
Can someone please help as this is really causing me problems.

 

[Guest]

Allen,
The main form contains a button that invokes that opens up a customer form,
blanks it out and allows the use to enter criteria.
When they have finished, I want them to be able to be able to do the search
by clicking on the apply filter icon on the toolbar.
This icon is greyed out.
My Record Source is the table Customers. Allow filters is set to Yes.
My button is not on the form because as you quite rightly say...it will be
innactive.
My search form is slightly different to the Add/amend/Delete Customer form
as not eveything is included in the search screen.
Why is the Apply Filter icon greyed out?

So far, you have succeeded in opening a form named "Find Customers" in
Filter By Form mode.

This form has some controls, where you are able to enter some values, but
your button is greyed out. If this button is on the toolbar, it would be
greyed out if the form is unbound, i.e. if it has nothing in its
RecordSource property. If this button is on the form, it will always be
greyed out in Filter By Form, because no code runs in this mode, so the
button's Click event procedure cannot possibly work.

I'm not sure why you opened a Find Customers form. If you did not want to
write any code, you could have just used:
RunCommand acCmdFilterByForm
on the original form to switch it to filter by form mode. Since it was
(presumably) a bound form, the standard form toolbar button would then have
allowed you to apply the filter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for trying to help me Allen.
I have a Main Form and on it is a button named 'Search Customers'.
When this is clicked I have an event procedure that opens the customer
form
and also runs the Filter command.
This is the code.....
DoCmd.OpenForm "Find Customers"
RunCommand acCmdFilterByForm
I have created a menu bar icon for 'apply filter' which is invoked on the
loading of the Find Customers form.
When the form loads, all the fields are blank, I can enter criteria into
any/all of the fields, but the icon for applying the filter is grayed out.
I cannot click on it to produce the results based on the entered criteria.
I have checked and the form is bound to the Customers table and the Allow
Filters is set to Yes.
What am I doing wrong????
Good job I am on the ground floor cos this is driving me mad
Help!!

The FilterByForm button on the toolbar should be enabled unless the form
is
unbound or you have set its AllowFilters property to No.

If you want to use a command button to enter Filter by Form, use:
RunCommand acCmdFilterByForm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I do not want to do this in VB code.
I would rather use the Access routine Filter by Form if I can.
I have an example of how to do the search 'long hand' but I thought it
would
be easy to open the form in Filter by Form mode and let Access do the
rest.
My Filter by Form icon seems to be disabled????
The user wants to do a filter on a filter, narrowing down the data
until
they find what they want.
Am I asking too much???
So far, I have spent all day trying to get, what seems so simple, to
work!!!!
Is the only solution to this to create my own search in VB???
According to the manuals that I have, Access should eat this.

:

The inferface I like to give my users consists of several unbound
controls
in the Form Header section of the form, along with a pair of command
buttons
for Filter and Remove Filter. This Filter button's Click event
procedure
builds up a string to use as the Filter of the form, based on the
controls
where the user entered something.

The code for the Click event of the command button looks something
like
this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first

'Build up there filter string from the non-blank text boxes.
If Not IsNull(Me.txtFilterSurname) Then 'text field example.
strWhere = strWhere & "([Surname] = """ & _
Me.txtFilterSurname & """) AND "
End If

If Not IsNull(Me.txtFilterAmount) Then 'number field example
strWhere = strWhere & "([Amount] = " & _
Me.txtFilterAmount & ") AND "
End If

If Not IsNull(Me.txtFilterBirthdate) Then 'date field example
strWhere = strWhere & "([Birthdate] = " & _
Format(Me.txtFilterBirthdate, conJetDateFormat) & ") AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Enter some criteria first."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = False
End If
End Sub

I am trying to provide a user with a search facility on a customer
form.
Nothing fancy, just the ability to enter multiple criteria on the
form
and
then hit a button that basically says Search.
How, please, do I do this???
Has anyone got a good example from start to end.
In the Tools>Startup I have tried to switch off all menus etc so
that
the
user cannot do anything silly.
I have created a customized toolbar for 'search by form' called
Search
but
when the form opens, my search icon is innactive.
Can someone please help as this is really causing me problems.

 

[Allen Browne]

The obvious reasons would be if:
- the form were a popup form;
- the form is unbound; or
- the form's AllowFilters property is No.

 

[Guest]

Thanks Allen.
It was a popup form.
Cheers

The obvious reasons would be if:
- the form were a popup form;
- the form is unbound; or
- the form's AllowFilters property is No.

 

[Guest]

Well done Allen and thanks for sticking with it.
My form was a popup form.
Cheers

 

[Guest]

Hi,

I've try the code proposed by Allen. It's working well IF I enter a criteria
in the first field [txtsturbineno]. But if I leave this first criteria blank,
and I fill the 1 or 2 others criteria, then I won't sort any result. Can you
help me ? Here's my code adapted from the one proposed by Allen.

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtsturbineno) Then
strWhere = strWhere & "([sturbineno] = """ & _
Me.txtsturbineno & """) AND "
End If

If Not IsNull(Me.txtworkdone) Then
strWhere = strWhere & "([work] = """ & _
Me.txtworkdone & """) AND "
End If

If Not IsNull(Me.txtdescription) Then
strWhere = strWhere & "([description] = """ & _
Me.txtdescription & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Enter some criteria first."
Else
DoCmd.OpenForm "frmSHOWRESULT", acFormDS, , Left(strWhere, lngLen)
End If

The inferface I like to give my users consists of several unbound controls
in the Form Header section of the form, along with a pair of command buttons
for Filter and Remove Filter. This Filter button's Click event procedure
builds up a string to use as the Filter of the form, based on the controls
where the user entered something.

The code for the Click event of the command button looks something like
this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first

'Build up there filter string from the non-blank text boxes.
If Not IsNull(Me.txtFilterSurname) Then 'text field example.
strWhere = strWhere & "([Surname] = """ & _
Me.txtFilterSurname & """) AND "
End If

If Not IsNull(Me.txtFilterAmount) Then 'number field example
strWhere = strWhere & "([Amount] = " & _
Me.txtFilterAmount & ") AND "
End If

If Not IsNull(Me.txtFilterBirthdate) Then 'date field example
strWhere = strWhere & "([Birthdate] = " & _
Format(Me.txtFilterBirthdate, conJetDateFormat) & ") AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Enter some criteria first."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = False
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to provide a user with a search facility on a customer form.
Nothing fancy, just the ability to enter multiple criteria on the form and
then hit a button that basically says Search.
How, please, do I do this???
Has anyone got a good example from start to end.
In the Tools>Startup I have tried to switch off all menus etc so that the
user cannot do anything silly.
I have created a customized toolbar for 'search by form' called Search but
when the form opens, my search icon is innactive.
Can someone please help as this is really causing me problems.

 

[Pieter Wijnen]

Much Simpler is

Dim varWhere AS Variant
Dim C AS Access.Control
On Error Resume Next
varWhere=Null
For Each C in Me.Controls
If TypeOf C Is Access.TextBox Then ' Or ComboBox Or Listbox
If VBA.InStr(C.Tag,"SearchCriteria") > 0 Then
varWhere= varWhere + " AND " & (C.Name "='" & C.Value & "')"
End If
End If
Next
'......

In Your Case (prefixing bound Controls (?!?)) substitute C.Name for
VBA.Mid(C.Name,4)

HTH

Pieter

Hi,

I've try the code proposed by Allen. It's working well IF I enter a
criteria
in the first field [txtsturbineno]. But if I leave this first criteria
blank,
and I fill the 1 or 2 others criteria, then I won't sort any result. Can
you
help me ? Here's my code adapted from the one proposed by Allen.

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtsturbineno) Then
strWhere = strWhere & "([sturbineno] = """ & _
Me.txtsturbineno & """) AND "
End If

If Not IsNull(Me.txtworkdone) Then
strWhere = strWhere & "([work] = """ & _
Me.txtworkdone & """) AND "
End If

If Not IsNull(Me.txtdescription) Then
strWhere = strWhere & "([description] = """ & _
Me.txtdescription & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Enter some criteria first."
Else
DoCmd.OpenForm "frmSHOWRESULT", acFormDS, , Left(strWhere, lngLen)
End If

The inferface I like to give my users consists of several unbound
controls
in the Form Header section of the form, along with a pair of command
buttons
for Filter and Remove Filter. This Filter button's Click event procedure
builds up a string to use as the Filter of the form, based on the
controls
where the user entered something.

The code for the Click event of the command button looks something like
this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first

'Build up there filter string from the non-blank text boxes.
If Not IsNull(Me.txtFilterSurname) Then 'text field example.
strWhere = strWhere & "([Surname] = """ & _
Me.txtFilterSurname & """) AND "
End If

If Not IsNull(Me.txtFilterAmount) Then 'number field example
strWhere = strWhere & "([Amount] = " & _
Me.txtFilterAmount & ") AND "
End If

If Not IsNull(Me.txtFilterBirthdate) Then 'date field example
strWhere = strWhere & "([Birthdate] = " & _
Format(Me.txtFilterBirthdate, conJetDateFormat) & ") AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Enter some criteria first."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = False
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to provide a user with a search facility on a customer form.
Nothing fancy, just the ability to enter multiple criteria on the form
and
then hit a button that basically says Search.
How, please, do I do this???
Has anyone got a good example from start to end.
In the Tools>Startup I have tried to switch off all menus etc so that
the
user cannot do anything silly.
I have created a customized toolbar for 'search by form' called Search
but
when the form opens, my search icon is innactive.
Can someone please help as this is really causing me problems.

 

[trickledown]

For the sake of simplicity and security I'd like my users to see only the
form. I've created a button that starts the "Filter by Form" command:

DoCmd.RunCommand acCmdFilterByForm

When this button is clicked the "Filter by Form" operation starts as it
should. The "Apply Filter" button is enabled on the normal toolbar but all
my buttons in the form are disabled.

The Pop Up property is set to NO

Is what I am trying to do possible?

 

[Guest]

For the sake of simplicity and security I'd like my users to see only the
form. I've created a button that starts the "Filter by Form" command:

DoCmd.RunCommand acCmdFilterByForm

When this button is clicked the "Filter by Form" operation starts as it
should. The "Apply Filter" button is enabled on the normal toolbar but
all
my buttons in the form are disabled.

The Pop Up property is set to NO

Is what I am trying to do possible?