Ignore query criteria if form not open (1 Viewer)

Thicko

Registered User.
Local time
Today, 06:10
Joined
Oct 21, 2011
Messages
61
Hi All,

I have multiple forms for entering different types of stock onto a database, when entering, a query looks up stock already on the database for that product. To make life simplier I want one query with the criteria for product something like:
Code:
[Forms]![frmStockEntry].[Product] Or [Forms]![frmStockEntryPreCompounded].[Product]
unfortunatly it's not that simple and if one of the forms isn't open it can't locate and therefore throws up an error.

One thought is to open the other forms in the background and they'll return a blank then close them when the user has completed entering the stock, but this seems a like workaround rather than a clean fix. Is there a simple solution to avoid this error?

Many Thanks for any thoughts
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:10
Joined
Oct 29, 2018
Messages
21,358
Hi. As far as I know, the only way to avoid the error if the form is not open is to not use a form reference. Maybe try using TempVars instead.
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:10
Joined
Mar 14, 2017
Messages
8,738
There are ways in VBA to test if a form is loaded or not. Thus another option you could consider would be to create a function in vba. The function would handle your "OR" situation as elegantly as it might need to and return some value. Perhaps "not available" if none of the options panned out.
Edit for further clarity: You could then use the Function in the query with no errors.
Check if a form is open
 

Micron

AWF VIP
Local time
Today, 02:10
Joined
Oct 20, 2018
Messages
3,476
You have 2 forms, and it's likely that only one of them will be open and you want your query to use the reference from the open form? Then on each form, either run the right sql from behind the right form (don't open or call the query) or modify the query def object sql (the sql behind the stored query) from that form. About the easiest way to move the sql to the form is to copy it from the sql view of the query as a start. You would remove the reference to the form that won't apply in each case (because I think you're saying you just have one query at present). Not sure if I explained that very well, but it's very common to run sql from within a form module.
 

Thicko

Registered User.
Local time
Today, 06:10
Joined
Oct 21, 2011
Messages
61
All sorted with a custom function, great idea Isaac
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:10
Joined
Mar 14, 2017
Messages
8,738
Glad it worked out for you good luck with your project
 

Users who are viewing this thread

Top Bottom