Using same variable for multiple tables, queries and forms. 1

[JimmyDix]

I need to query a table based on user entered text, update several fields automatically, then open a form which the user can then edit further, and finally print a report based on the same user entered text. How do I do this without requesting the user text multiple times?

 

[Jonathan]

If you declare a variable in a module using (for example)<br>
<br>
Public gstrUserText as String<br>
<br>
Then you can use the variable anywhere in your program. If you need to use the value in a query, you'll need an associated function to get the value for you, ie,<br>
<br>
Public Function FN_strUserText() as String<br>
<br>
FN_strUserText = gstrUserText<br>
<br>
End Function<br>
<br>
So you could have the user enter the text, store the value in your global variable and take it from there.<br>
<br>
Or, once the user has entered the text in a form, just make the form invisible whilst you do everything else. This will mean that you can still access the contents of the text box. However, I prefer the first way.<br>
<br>
Hope this helps<br>
<br>
Jonathan

 

[JimmyDix]

I'm not that great with VBA. How do you assign the value from a form box to a variable?

 

[Jonathan]

Assuming you have put your global variable and the function in a module...<br>
<br>
If, once the user has entered their text, they have to click on an "OK" or "Go" kind of button, then you set the "OnClick" property of the button to [Event Procedure]. Once this is done, you can click button with the three dots to the right of this and you will be taken to the forms class module, with the cursor inside the buttons Click event. This is code that is run when the user clicks on the button. Simply add the code<br>
<br>
gstrUserText = Me.text_box_name_here<br>
<br>
and when the user clicks the button, the value of the text box will be assigned to the variable.<br>
<br>
If, up to now, you'd used a macro to do stuff after the user entered the text, you can either run this from the code ( use DoCmd.RunMacro macro_name_here ) or convert the macro into VBA (there's an add in that will do this for you, but you'll have to play around with it after...)<br>
<br>
Jonathan