Now, we will explore some interesting trick with Forms in VBA. We will explore two different aspects on this particular topic.
How to open several instances of a single Microsoft Access Form in memory, displaying different information on each of them? Sample screen shot of two instances of Employees Form is given below for reference.
The first form is behind the second instance of the form, displays employee details of ID: Click on the image to enlarge the picture. The target form must be opened in memory in order to call the function procedure of the form from outside. Function Procedures in a Form module is helpful to avoid duplication of code. It can be called from subroutines in different locations on the same Form, from a command button click or from some other Event Procedures of the Form.
The function procedure on a Form Module can be anything that does some calculation, validation check, updating the information or a stand-alone search operation procedure, like the one we are going to use on our sample Employees Form.
Our own VBA codes that does something goes within this block of codes: Private declaration is absolutely necessary to avoid procedure name clash with the same name on another Class Module or Standard Module.
Form Function Procedure must be declared as Public in order to call it from outside the Form. To perform a trial run of the above trick you need the Employees Table and a Form. Import Employees Table from Northwind. Click on the Employees Table to select it. Click on Create Ribbon. Save the Form with the name frmEmployees. Open the frmEmployees Form in Design View. RecordsetClone 'Find the first record that matches the criteria rst.
FindFirst crit 'If the record is found then If Not rst. NoMatch Then 'copy the recordset bookmark to the form bookmark 'this will make the found record current on the form Me. Have you noticed the starting line of the above Function that is declared as Public? If the search operation fails then it gives a warning message, saying that the employee ID code passed to the function is not within the range of ID codes available in the Employees table.
Besides that this program demonstrates as how to create more than one instance of a Microsoft Access Form and open them in memory, to access their properties, methods or control contents. Click somewhere within the body of frmInstanceTest function and press F5 key to run the code.
The program will pause at the Stop statement and this will facilitate to view the Application window, where the frmEmployees Form instances are open in normal view mode, one overlapping the other. Click and hold on the title bar area of the top form and drag it to the right, to make part of the form behind visible. Check the employee records on both forms, they are different, one with employee code 4 and the other is 5. Check the title area of forms, both are showing frmEmployees title.
Now, let us come back to the program and continue running the code to complete the task.