Use query result to open form.

[Playerpawn]

Hi,

I have a form with a text edit field (input_text) and button control (go_button).

When the user clicks the button (go_button), I want it to pass the text in the editable field (input_text) into a saved query (qry_myquery). This will return either NULL or a number.

If null is returned, I want it to say it's a bad input and to clear the text control (input_text).



If a number is returned, I want it to close the current form and open a differnt form based on the result (ie, 1 = open form1, 2= open form2). Additionally, I would like to pass the editable field's data (input_text) into the newly opened form and have it auto populated into it's editable control (new control, new_forms_input_text).

Can someone help with this, please?

[pkstormy]

The best way I can think of is to have a function which does this. You can use a query expression such as: =iif(Forms!MyFormName!Input_Text = "X",null)

I'd write a function though. This you can then call this function in the query and refine how you want the number returned.

For Example:
Function retCalcNumber(vSomeVariable as variant) as variant
if isnull(vSomeVariable) then
retCalcNumber = null
exit function
end if
if vSomeVariable = <some equation here> then retCalcNumber = <some calculation here>.
end function

[Playerpawn]

How do I get the result of a query into a function's variable?

I have qry_which_one which will ultimately return 1, 2 or 3 when executed. I want that result into a variable within a function, so the function can take action based on the query's result.

ty

[pkstormy]

Try using the dlookup command. You could do this within the function also. The dlookup command will look up a value in a query or return the single value if there are no others (ie. no paramaters are supplied). - ex: somevariable = dlookup("[FieldNameinQuery]","QueryName")