I like to keep things as simple as possible. After all, I am not an Access expert, just a guy who likes to build Databases. I have learned a lot from the forums and on internet sites. Most of the pros on the forums give really great advice. But the forums were not meant to be tutorials where you get detailed step by step instructions. So I created this blog so we can share what we have learned with each other.
Here is one of my favorite Access tips:
How to Open Forms with a simple Function that saves you time and work.
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!
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).
We will have some more tips later about what else you can do with a Custom Function.
To download free databases and code samples visit our Gaining Access Website.
Get the free Code Tracker™ Beta Add-in to record and then view how your procedures are being used.
More Free Downloads:
ScopeSight for Access Reports Demo: get the exact data you want on your Access Reports.
Free Church Management Software now with new Contributions management.
Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.
Patrick (Pat) Wood