Display search results in different form

[Veus]

Hi,

I am developing a system which lets users search on multiple fields.
I have a form for the search part and a form which will display the
results. Ive used this code: http://allenbrowne.com/ser-62code.html as
a starting point however this displays the results in the current form.

Ive tried changing

Me.Filter = strWhere
Me.FilterOn = True

to

stDocName = "firms"
DoCmd.OpenForm stDocName, , , strWhere
stDocName.Filter = strWhere
stDocName.FilterOn = True

however I get an invalid qualifier result. I also have one calculated
field on the results form which works out how many employees work for
the specific company (using the code:
=DCount("ID","individuals","compID = " & [ID]) )
With this included i got an object required error.

If you could help I would be grateful.

Thanks

 

[Allen Browne]

If the Firms form is not open, you can just use:
DoCmd.OpenForm stDocName, , , strWhere
There is no need to set the Filter or FilterOn.

If the form is already open, you would set its filter with:
Forms(stDocName).Filter = strWhere
Forms(stDocName).FilterOn = True

I'm not clear about the DCount() expression, but it would probably be best
to include the result (the actual number it returns) rather than the
expression into the Filter string.

If you need to see the filter string to help with debugging, add the line:
Debug.Print strWhere
Then after it runs, open the Immediate Window (Ctrl+G) to see the output.

 

[Veus]

Hi,

Thanks for the help, it doesnt come up with an error now but i think
its getting confused.
I have:

If Not IsNull(Me.name) Then
strWhere = strWhere & "([name] Like ""*" & Me.name & "*"") AND
"
End If

[name] is the company name, however using the debug it always comes out
like this:

([name] Like "*testingSearch*")

testingSearch is the name of the search form name. It seems like its
getting the form name rather than the textbox name. How would i tell it
to get the correct variable?

If the Firms form is not open, you can just use:
DoCmd.OpenForm stDocName, , , strWhere
There is no need to set the Filter or FilterOn.

If the form is already open, you would set its filter with:
Forms(stDocName).Filter = strWhere
Forms(stDocName).FilterOn = True

I'm not clear about the DCount() expression, but it would probably be best
to include the result (the actual number it returns) rather than the
expression into the Filter string.

If you need to see the filter string to help with debugging, add the line:
Debug.Print strWhere
Then after it runs, open the Immediate Window (Ctrl+G) to see the output.

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

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

I am developing a system which lets users search on multiple fields.
I have a form for the search part and a form which will display the
results. Ive used this code: http://allenbrowne.com/ser-62code.html as
a starting point however this displays the results in the current form.

Ive tried changing

Me.Filter = strWhere
Me.FilterOn = True

to

stDocName = "firms"
DoCmd.OpenForm stDocName, , , strWhere
stDocName.Filter = strWhere
stDocName.FilterOn = True

however I get an invalid qualifier result. I also have one calculated
field on the results form which works out how many employees work for
the specific company (using the code:
=DCount("ID","individuals","compID = " & [ID]) )
With this included i got an object required error.

 

[Allen Browne]

That's good debugging!

You have stuck a problem that most people do with Access. Nearly everything
in Access - forms, reports, controls, and so on - has a Name property. So
if you create a field called Name, or a text box named Name, it is
ambiguous, and you will run into the problem you discovered.

Rename the field to something such as Surname, or CompanyName, or RuleName,
or whatever. Then change the Name of the text box and its Control Source on
your form as well. You will then code:
strWhere = strWhere & "([Surname] Like ""*" & Me.Surname & "*"") AND
which works around the problem.

There are more than 1000 of these names you need to avoid. Name and Date are
the most common ones, but others like Year, Month, Day, Form, Height, Width,
and so on will cause you grief too. For a list of all the ones I am aware
of, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html

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

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

Thanks for the help, it doesnt come up with an error now but i think
its getting confused.
I have:

If Not IsNull(Me.name) Then
strWhere = strWhere & "([name] Like ""*" & Me.name & "*"") AND
"
End If

[name] is the company name, however using the debug it always comes out
like this:

([name] Like "*testingSearch*")

testingSearch is the name of the search form name. It seems like its
getting the form name rather than the textbox name. How would i tell it
to get the correct variable?

If the Firms form is not open, you can just use:
DoCmd.OpenForm stDocName, , , strWhere
There is no need to set the Filter or FilterOn.

If the form is already open, you would set its filter with:
Forms(stDocName).Filter = strWhere
Forms(stDocName).FilterOn = True

I'm not clear about the DCount() expression, but it would probably be
best
to include the result (the actual number it returns) rather than the
expression into the Filter string.

If you need to see the filter string to help with debugging, add the
line:
Debug.Print strWhere
Then after it runs, open the Immediate Window (Ctrl+G) to see the output.

I am developing a system which lets users search on multiple fields.
I have a form for the search part and a form which will display the
results. Ive used this code: http://allenbrowne.com/ser-62code.html as
a starting point however this displays the results in the current form.

Ive tried changing

Me.Filter = strWhere
Me.FilterOn = True

to

stDocName = "firms"
DoCmd.OpenForm stDocName, , , strWhere
stDocName.Filter = strWhere
stDocName.FilterOn = True

however I get an invalid qualifier result. I also have one calculated
field on the results form which works out how many employees work for
the specific company (using the code:
=DCount("ID","individuals","compID = " & [ID]) )
With this included i got an object required error.

 

[Veus]

Thanks again

I have managed to get it working by using

Me.Controls("name")

instead of just name.

Thanks for all your help!

That's good debugging!

You have stuck a problem that most people do with Access. Nearly everything
in Access - forms, reports, controls, and so on - has a Name property. So
if you create a field called Name, or a text box named Name, it is
ambiguous, and you will run into the problem you discovered.

Rename the field to something such as Surname, or CompanyName, or RuleName,
or whatever. Then change the Name of the text box and its Control Source on
your form as well. You will then code:
strWhere = strWhere & "([Surname] Like ""*" & Me.Surname & "*"") AND
which works around the problem.

There are more than 1000 of these names you need to avoid. Name and Date are
the most common ones, but others like Year, Month, Day, Form, Height, Width,
and so on will cause you grief too. For a list of all the ones I am aware
of, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html

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

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

Thanks for the help, it doesnt come up with an error now but i think
its getting confused.
I have:

If Not IsNull(Me.name) Then
strWhere = strWhere & "([name] Like ""*" & Me.name & "*"") AND
"
End If

[name] is the company name, however using the debug it always comes out
like this:

([name] Like "*testingSearch*")

testingSearch is the name of the search form name. It seems like its
getting the form name rather than the textbox name. How would i tell it
to get the correct variable?

If the Firms form is not open, you can just use:
DoCmd.OpenForm stDocName, , , strWhere
There is no need to set the Filter or FilterOn.

If the form is already open, you would set its filter with:
Forms(stDocName).Filter = strWhere
Forms(stDocName).FilterOn = True

I'm not clear about the DCount() expression, but it would probably be
best
to include the result (the actual number it returns) rather than the
expression into the Filter string.

If you need to see the filter string to help with debugging, add the
line:
Debug.Print strWhere
Then after it runs, open the Immediate Window (Ctrl+G) to see the output.


I am developing a system which lets users search on multiple fields.
I have a form for the search part and a form which will display the
results. Ive used this code: http://allenbrowne.com/ser-62code.html as
a starting point however this displays the results in the current form.

Ive tried changing

Me.Filter = strWhere
Me.FilterOn = True

to

stDocName = "firms"
DoCmd.OpenForm stDocName, , , strWhere
stDocName.Filter = strWhere
stDocName.FilterOn = True

however I get an invalid qualifier result. I also have one calculated
field on the results form which works out how many employees work for
the specific company (using the code:
=DCount("ID","individuals","compID = " & [ID]) )
With this included i got an object required error.