Setting Query Criteria from Form

[fredk]

I have posted a similar question a while back but never recieved an answer.

I want to set the query criteria from my form - However, I want the criteria to equal any of 5 possibilities.

Basically, I run a function that returns the criteria to a field on the form based on the users signon.

For example, here is what is populated in the field on my form:

'Broker1' Or 'Broker2' Or 'Broker3'

In my function that populates the field I have a select case but the data is:

Case UserName:
GetBroker = " 'Broker1' Or 'Broker2' Or 'Broker3' "

And my query criteria is:

[forms]![sacspecific]![brokers]

Am I missing something or can this not be done?

When I copy the text right from the field on my form and paste it over the query criteria, the query works fine?????

Thanks!!!!

Fred

 

[ImpMan]

Have you missed out the equals sign?

The line in the query should be
=[forms]![sacspecific]![brokers]

By the way,
If the sacspecific form actually shows the results of the query in a subform then you may need to refresh or requery the subform inorder that it shows the results of the query. To check this I launch the query whilst the form is loaded and see if I see the correct results in the query. If I do see the correct results in the query I then just requery the subform by doing something like this in code

me.subform.form.requery

Goodluck

 

[fredk]

Thanks for the help ImpMan - I don't think the = is needed - I added it and saved the query but when I went back in it was gone - I think that it is assumed to be = (the result is a report)
Thanks again!!

Fred

 

[ImpMan]

In that case then,

I think your syntax is correct.

This is obvious but....
Is the query already open prior to opening the form? Otherwise open the query after the form.

Open the report last, while the form and query are still open

Good luck

 

[fredk]

I think I am ok with that - I open the report by clicking a button on the report - The open event of the form sets a field on the form to the criteria using a function -

I can't figure it out - It has something to do with the multiple criteria because if I just use one criteria instead of several, the process works.

Thanks for the help!!!

Fred

 

[randysmid]

Fred,
I am having the same trouble, but I do have a work-around that I have been using for several years. The "Or" criteria will work with a filter. I wrote an FAQ on this topic (faq703-2657). Essentially, the string containing the "or's" is sent to the report, which is then based on a query.

I have been experimenting with this problem with "or" criteria being sent from a form, and it seems like Access is unable to convert this string into the syntax it uses. For instance, if you enter "CA" Or "OR" or "WA" (as state abbreviations) into the query criteria line, then look at the SQL view, you will see that it converted the entire line to this:
..... where (States.State_Abbr)="CA" or (States.State_Abbr)="OR" or .............etc.

Some of my research has focused on the fact that one or more "references" may not have been set correctly, but the Microsoft support sites (including MSDN) don't seem to have very much documentation on them.

I will be posting a new question this inside of this Access Queries forum very shortly. HTH,
Randy Smith
California Teachers Association

 

[ImpMan]

Ah, you are changing the 'filter' property of the form. I have had problmes with this too.

My favoured solution is have a query that is your search query. The form shows the results of the query and also changes the query definition.

When the form loads the form modifies the query to
'select * from ....etc '
This will mean that all the records are displayed after the form loads.
Every time the user changes criteria for the search, the query definition is changed and the form requeried.

This works very well.