Query criteria from a closed form?

[Guest]

hi folks,
I know how to use a textbox value from a form in the criteria of a query -
if the form is open.

And in general I know if a form is closed there is no value in its textbox
and therefore one would not successfully use it as a criteria.

but an OR criteria where it is Form1.textbox OR Form2.textbox OR
Form3.textbox if some (but not all) of these forms are closed - since one
value is valid I was hoping the query would return......however it balks that
there are no parameter values for the closed form and there is no return

anyone out there have a solution on this? much thanks

 

[Marshall Barton]

I know how to use a textbox value from a form in the criteria of a query -
if the form is open.

And in general I know if a form is closed there is no value in its textbox
and therefore one would not successfully use it as a criteria.

but an OR criteria where it is Form1.textbox OR Form2.textbox OR
Form3.textbox if some (but not all) of these forms are closed - since one
value is valid I was hoping the query would return......however it balks that
there are no parameter values for the closed form and there is no return

An alternative to using form control references for the
criteria is to use a public function that returns a global
variable's value. This way, any form can set the global
variable and the function won't care where it came from.

query criteria:
GetMyVar()

Code in a standard moudule:

Public MyVar As Variant
Public Function GetMyVar()
GetMyVar = Myvar
End Function

code in any form:
MyVar = Me.textbox
'run query or open form/report

 

[Guest]

thanks much,

not working though

1. put GetMyVar() in criteria of approriate field in query

2. wrote new module identical to your instruction; left default name of
Module1 for it

3. Put the code MyVar = Me.Billing_ in the After-Update Event of TextBox
Billing_

I did notice that the name of the textbox in properties (and to my mind) is
Billing# while in the code the autocomplete and the auto click event header
used Billing_ so I just went with that....

Seems like it should work - not sure which of the 3 elements is wrong -
after one relooks at each a few times for typos - not much more one can
do..... Perhaps the After-Update location is wrong. I was not clear as to
what one means when instructed to "put the code in any form"....

 

[Marshall Barton]

not working though

1. put GetMyVar() in criteria of approriate field in query

2. wrote new module identical to your instruction; left default name of
Module1 for it

3. Put the code MyVar = Me.Billing_ in the After-Update Event of TextBox
Billing_

I did notice that the name of the textbox in properties (and to my mind) is
Billing# while in the code the autocomplete and the auto click event header
used Billing_ so I just went with that....

Seems like it should work - not sure which of the 3 elements is wrong -
after one relooks at each a few times for typos - not much more one can
do..... Perhaps the After-Update location is wrong. I was not clear as to
what one means when instructed to "put the code in any form"....

Please be more explicit the just telling us "not working
though". That just does not provide any significant clues.
Knowing what actually happened is critical to the debugging
process, especially when we can't even see your screen.

The IntelliSense list is not infallible, especially when you
use odd characters. If the control's Name property contains
Billing#, then that's what you must use when referencing the
control. Did you compile your code? If the name was
invalid and if your module contained an OPTION EXPLICIT
statement, there should have been an undefined name error.
It is also a syntax requirement that a name that contains
any non-alphanumeric character must be enclosed in [ ].
Best Practice is to never use a name that contains a funky
character. I suspect that you should have used:
MyVar = Me.[Billing#]

The AfterUpdate event seems like a good place to set the
variable. Or you could wait until you are actually going to
use the query. Waiting might be better (especially during
debugging) because global variables are cleared on
unhandled errors or when you reset the project.

By "put the code in any form" I meant that you can set the
variable whenever needed without being concerned about which
form is open as you were trying to do in your original
question.