I was designing a database with a lot of forms. To make it user friendly, I had to add numerous buttons on these forms so the users could easily get to the other forms they needed. Using the Access Button Wizard was adding a lot of code to my Database. It was growing faster than the National Debt. Fortunately I discovered a simple solution to avoid adding all that code and the database bloat that goes with it.
Instead of adding code to your forms to open other forms, you can use this simple Function to open your forms. You can add this Function to an existing Module or create a new module (not a Form, Report, or Class Module but a regular module in the Modules group).
You can start by copying the following code to a Module:
Public Function Openmyfrm(myfrmname As String)
DoCmd.OpenForm myfrmname
End Function
That was easy wasn’t it? But the best is yet to come. How do you get this Function to open your form? With just one little line in the On Click event of a button
=Openmyfrm(“YOURFORMNAME”)
That’s it! The Function accepts the form name between the quotes as a String because we told it to do that when we added (myfrmname As String) to the Function. It then uses that form name as a variable with the DoCmd.OpenForm code.
You need to add the =Openmyfrm(“YOURFORMNAME”) expression to the On Click Event of a Button. (I am using all caps to indicate where you need to add the name of your form).
In case you need help, here’s how to add the expression to a button: Open your form in design view. Click on the button on the tool bar and place a new button on your form. The Button Wizard fires up. I just click Cancel and I have a new button ready to use. I change the Caption on the button to something like Open Customers Form.
Then open the button’s property sheet. You can do that by right clicking on the button and selecting “Properties”. Next, click on the “Event” tab. Find the white area beside the On Click Event. Add the expression with the name of the Form you want to open between the quotes.
You are finished! (Don’t forget to add some error handling to your Function).
