How to pass variable from one From to another form using VBA in access

Question

This is in Access 2016 VBA

I have a form that user types in a number and clicks search button to run a query which opens a form to show data found. The form that shows the query's data checks if there is data exist to open otherwise a msgbox will popup informing user no data found!

On the first form I have another button (Excel button) that runs the same query and exports the data into an Excel file without opening any other form.

When user clicks Excel button I show a YN msgbox informing user to make sure Search the data before to prevent creating empty Excel file, which works fine; however, if user answers yes and there is no data, Excel file will be created regardless.

I tried to set a Boolean variable called IsSearched and set to value false and change the value to True if user already clicks on search and the data exist and query has a result. Which means this IsSearched value will be changed after the query result of data found or not found. I am able to change the IsSearched but not able to pass it to the 1st form. By being able to pass this my code should check if the Search has been clicked and there is data exist to let user create Excel file and if there is not data then will not create blank Excel file.

I hope I explained it well. Thanks

question from:https://stackoverflow.com/questions/65927240/how-to-pass-variable-from-one-from-to-another-form-using-vba-in-access

Answer 1

In MS Access, if you want to send/pass a value from one form to another, then this is the answer.. Let's take an example..you are in login page and you have logged in with your credentials, now you are in the home page or any landing page, here you want to show the userName or use userId further, In login page you write-

UserName = resultSet("username") TempVars("UserName") = resultSet!username.Value

DoCmd.openForm "HomePage", acNormal, , , , acWindowNormal, openArgs: userName

Now you can use this temporary var in your homepage like this- Declare variable first - Dim userName As String UserName = TempVars("UserName")

Here you have userName with you .