One of the problems I have most often faced with Microsoft Access is code duplication. Intended not only as a duplication of VBS code, but also of queries, macros, procedures, etc. In this article we will see how to link forms between Microsoft Access databases, in order to use the same form between multiple databases.
The procedure, after understanding it, is quite simple and intuitive. It involves these steps:
- Create a reference database, which will contain the templates
- Create the other databases
- Connect the databases to the reference database
- Call up masks when needed
Create a reference database
As an example I create a simple database (
notes). It has a single table,
tbNotes with 2 fields:
Notes. In addition, it has two forms. The first,
frmNotes, which allows you to insert the various notes and delete them.
The second form is used to display the position of the open database, and that of the linked form.
I create two functions:
So you get something like this:
I also need two functions to open the two forms:
Create the other databases
Now that I have the database with the reference templates I can create the
otherNotes database. For the moment I create a form with two buttons, but without implementing the code:
In order to actually link the masks I have to add a reference to the project. I go to the Visual Basic for Applications (VBA) editor and click on
References. From here I can select the reference database,
This is the simplest solution but I prefer to implement a general function.
First I make sure that the same reference does not already exist. If there is I remove it. Then I add again the reference to the database I want to connect with.
This way I can connect to multiple databases, without having to manually add references.
I create some controls and add a button
So I write the function:
After connecting the two databases, I can call the functions to open the forms.
The final result is this:
Well, that’s it. I created a repository with the code from the example. You can find it here: el3um4s/how-to-link-forms-in-access