Filtering a Form through another Form

[maindaco via AccessMonster.com]

I have Form1 bounded to a table (with fields: BDate, Name, LastName, City,
Street), then I created another form SearchForm, intended to search and look
up for specifics fields on Form1. I'm using the following statements to
filter 1 criteria (LastName):

Private Sub SearchCommand23_Click()
On Error GoTo Err_SearchCommand23_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form1"

stLinkCriteria = "[LName]=" & Me![LastName]
DoCmd.Close

DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_SearchCommand23_Click:
Exit Sub

Err_SearchCommand23_Click:
MsgBox Err.Description
Resume Exit_SearchCommand23_Click

End Sub

Questions: Is it possible to set the stLinkCriteria in order to make a
combined search (using 2 or more criteria like City and/or Street)?...What
would be the code to be added to a button, in order to "clean" the filter on
Form1?

Your responses will be really appreciated. Thanks.

 

[kingston via AccessMonster.com]

Have you tried using the built-in menu function Records->Filter->Filter By
Form in your Form1? What you're trying to do sounds an awful lot like that.
You can also right click on a field to filter for something and then right
click on another field to filter for something else. If you really want to
do it your way, simply add the additional criteria to form your string:

stLinkCriteria = "[LName]='" & Me![LastName] & "' AND [City]='" & Me![City] &
"'"

I have Form1 bounded to a table (with fields: BDate, Name, LastName, City,
Street), then I created another form SearchForm, intended to search and look
up for specifics fields on Form1. I'm using the following statements to
filter 1 criteria (LastName):

Private Sub SearchCommand23_Click()
On Error GoTo Err_SearchCommand23_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form1"

stLinkCriteria = "[LName]=" & Me![LastName]
DoCmd.Close

DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_SearchCommand23_Click:
Exit Sub

Err_SearchCommand23_Click:
MsgBox Err.Description
Resume Exit_SearchCommand23_Click

End Sub

Questions: Is it possible to set the stLinkCriteria in order to make a
combined search (using 2 or more criteria like City and/or Street)?...What
would be the code to be added to a button, in order to "clean" the filter on
Form1?

Your responses will be really appreciated. Thanks.

 

[Klatuu]

Your criteria can include multiple fields:

stLinkCriteria = "[LName]= '" & Me![LastName] & "' And [City] = '" &
Me.City & "'"

Note the enclosing single quotes. When searching on text table fields, the
comparison value needs to be enclosed in single or double qoutes.

Define "clean"

 

[maindaco via AccessMonster.com]

Dear friends Kingston & Klatuu, I followed your indications but now I'm
getting error message: "The OpenForm action was cancelled" and nothing
happens....

On the other hand, what I intended to say with "clean" was to: have a button
into the filtered form that allows the user to show all the existing records,
it is... "un-filtering" the form....

Final question, will this work if only one criteria is selected? How can I do
that to work in the same form ?

This is what I have now:
*********************************************************************
Private Sub SearchCommand23_Click()
On Error GoTo Err_SearchCommand23_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form1"

stLinkCriteria = "[LName]= '" & Me![Last Name] & "' And [City] = '" & Me!
[City] & "'"

DoCmd.Close

DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_SearchCommand23_Click:
Exit Sub

Err_SearchCommand23_Click:
MsgBox Err.Description
Resume Exit_SearchCommand23_Click

End Sub

 

[Klatuu]

The error you are getting is usually caused by either one of the field names
in your critera is not spelled correctly or the field in the form's recordset
does not exist.

If you want to remove the filter, all you need is
Me.FilterOn = False

 

[maindaco via AccessMonster.com]

I double checked and can't find a solution... : (

Any other suggestion?

 

[Klatuu]

What is the error number you are getting?

 

[maindaco via AccessMonster.com]

How can I get the error number? It just say: The OpenForm action was canceled.


Have you tested the code? Thanks so much for helping with this..It's really
important for me..

 

[Klatuu]

I can't test the exact code because I don't have your form, tables, etc;
however, this approach is a very common way. I did create a similar
situation so I could test it. There were two ways I was able to make it fail
with the same error message (the error number is 2501). One was to misspell
the field name in the form's recordset, which is what I expected. The other
was incorrect syntax for the data type. In the line below that you posted,
to work correctly, both [LName] and [City] are typed as text fields. It
would be normal for names and city names to be text, but I would check to be
sure.

stLinkCriteria = "[LName]= '" & Me![Last Name] & "' And [City] = '" & Me!
[City] & "'"

One place I paid special attention was for your control Last Name. Spaces
in names are bad. The bracketting permits it, but it is still not a good
idea.

I also checked using a misspelled form control name, but that gave a
different error.

So, I am back to where I started. Only two things will cause the exact
error you are getting.
1. The form recordset field name in your code does not match a field in the
form's recordset.
2. The syntax for the data type of the field is incorrect.

 

[maindaco via AccessMonster.com]

I think I know where the error is coming from, but need help to solve it....
The thing is.... City is a numeric value (since it is a code), so my question
is: what would be the sintax in case of numeric values??? Please include a
case related to dates ...

I eliminated the "blanks" in the middle of Field Names and tested the code
with 2 "Text fields only" for the filter and it worked fine... the problem
appears when a numeric field is combined whith a text field in the filter...
and the search fails..."The OpenForm operation was canceled"

Thanks so much for the support.

 

[Klatuu]

stLinkCriteria = "[LName]= '" & Me![Last Name] & "' And [City] = '" & Me!
[City] & "'"

The syntax is different for different data types. The rules are:
Text - Enclosed in Single or Double quotes
"[LName = """ & Me![LastName] & """"
or
"[LName = '" & Me![LastName] & "'"

The first version is preferred because using the second version, you will
have problems when you hit a name like O'Reilly because of the single qoute
in the data.

Number - Not Enclosed
"[City] = " & Me![City]

Date - Enclosed in Pound Sign #
"[HireDate] = #" & Me.txtHireDate & "#"

 

[maindaco via AccessMonster.com]

Bingo!!! Now I got it... Thanks so much, now I got a new question for the
same thing... what if I'd like to apply a filter only using one of the
parameters and not both at the same time, using the same form.... it is.. the
current stLinkCriteria allows me to combine the LastName & City fields for
the search... but what if I only want to filter the LastName only in the form?
... What Is the code I need?

 

[Klatuu]

You can popuate stCriteria with whatever will work. You have to have a way
to know what to filter on, but whatever you put in as the filter will filter

 

[maindaco via AccessMonster.com]

Thanks for the reply, but I think I didn't explain myself correctly....

I have now Form1 which has 3 text boxes and a button that help me make a
filter on Form2 using the 3 parameters at the sametime...Now, I tried using
only 2 parameters (or 1) in the same form and buttom and an error appears...
My question is... can I use the same Form1 and button to filter only what I
want and not be forced to enter the 3 parameters only? Thanks for any help

 

[Klatuu]

Yes, Here is how you can do that. The logic here is that it only adds the
field to the filter if something has been filled in in the text box. For the
second and third fields, it first looks to see if a prior criteria has been
created, so it knows whether or not it needs an And.

If Not IsNull(Me.txtName) Then
strCriteria = "[Name] = '" & Me.txtName & "'"
End If
If Not IsNull(Me.txtCity) Then
If Len(strCriteria) > 0 Then
strCriteria = strCriteria & " And "
End If
strCriteria = strCriteria & "[City] = " & Me.txtCity
End If
If Not IsNull(Me.txtOther) Then
If Len(strCriteria) > 0 Then
strCriteria = strCriteria & " And "
End If
strCriteria = strCriteria & "[Other] = " & Me.txtOther
End If

(Couldn't remember what the 3rd one is)