When writing an Access database for others, you should certainly use Forms for managing the user interface. Forms include controls such as a text field, or an option (radio) button that can be linked to the underlying data fields. These controls can have a number of associated events, such as OnClick(), BeforeUpdate() or OnInsert() and this is how you can restrict how data is added, modified or deleted in the underlying database tables, ensuring data rules and constraints are not compromised.
Lets assume we have a database about members of a society, and we need to display the relatives that are associated with those members - perhaps so we can mail the members about their relatives' anniversaries... birth, marriage or death. We have a table called 'Members', and a table called 'Relatives'. Since a member can have many relatives, but a relative could also have more than one member in the database (for example a son could have parents who are both members), then this is a M:N relationship. You automatically need a relationship table for any M:N relationship. We shall call this 'related_to'. The relationship table contains the indexes for the Members and Relatives tables, and also stores the type of relationship in a field called RelationshipToMember, where the allowable entries include father, mother, son, daughter etc. There is also provision to add InLaw where required. The screenshot below shows the relationship between the three tables:-
The Primary Keys 'Member_ID' and 'Relative_ID' have been posted to the related_to table, creating the M:N relationship between the two tables.
When a user opens a form and finds the member record, there is a button on the form entitled 'Add/View Relatives'. When clicking this button, the user wants to find all the relatives that belong to that member as opposed to *every* relative in the entire database, by opening a linked form with the Relative records filtered. We can use a button with an 'OnClick' event to open the second form, using an argument that pre-filters the form.
The [...] button opens the Visual Basic Editor. Here is the code to open the linked Relatives Form and display the records belonging to the member only.
Command103 is the name of the command button that has the caption 'View/Add Relatives'. DoCmd is an Access application object property. OpenForm is a method belonging to that object, and it has a number of arguments, which are separated by commas. Here are the available arguments that could be used. The ones we are using in this example, are in bold.
In the code, the WhereCondition argument ['Member_ID]=Me.[Member_ID] filters the relatives records based on the value of the Member_ID text field. The OpenArgs argument, Me.[Member_ID] also takes the value of the Member_ID text field and passes it to the Relatives form when it is opened.
Here is the design for the 'Members' form. The primary key for the Members table is 'Member_ID', which indexes all the records and ensures they are unique, since duplicates of a primary key are not allowed in the same database table. When a user opens the 'Members' form, and finds the record of interest, (s)he can add or amend data.
The form has two functions. One is to delete the record, and the other is a (command) button with the caption 'View/Add Relatives' which opens the linked 'Relatives' form. We only want the relatives that "belong" to that member to be displayed. When you open the form in design mode, you can right click the View/Add Relatives button and choose Properties. This is where you add VBA code to the form to control events, on this case, the OnClick event using the small [...] button on the right.
;
We are using the OpenArgs argument, for illustration purposes only. You do not need the OpenArgs value to filter the 'Relatives' form, this is doen by the WhereCondition. However if we want to take further action when the 'Relatives' form is opened, we can use the OpenArgs value. To use the OpenArgs value for additional processing, we need to use the Form_Open event for the 'Relatives' form.
However note that Member_ID is a field value in the 'Members' table, not the 'Relatives' table. So how can we filter the records displayed in the 'Relatives' form by using a value that belongs in a different table? The 'Relatives' form must be based on two tables, the 'Relatives' table and the 'related to' table. Since a form can only be based on one table at a time, the form must instead be based on a query that includes both tables.
Forms can therefore be based on a single table, such as 'Members', or on a query that can incorporate multiple tables.
Here is the design of the query upon which the Relatives form is based.
Be aware that using a query with one table and the relationship table from one side of the M:N relationship will allow the Query, and therefore the form, to be fully editable. Records that will be displayed in the form can be added, amended and deleted by the user.
However, if the form is based on a query with three tables, including a M:N relationship, you will find that the form cannot be edited, certainly the case with Access 2003.
In other words, if we added the Members table to the query, we would be able to have a single form that displayed Members, Relatives and the special fields RelationshipToMember and In Law that belong to the related_to table, but when the form is opened, the records would be 'read_only'.
This is a restriction of the Access source code.
As the Member_ID is now part of the Query, and therefore can be included in the design of the Form, it can be used to filter records by matching the incoming value of the OpenArgs argument, Me.Member_ID.
Remember we do not need to use the OpenArgs argument of the OpenForm method fo filter the records, since this is done by the WhereCondition argument. The OpenArgs method allows you to program what happens after the filtering takes place. Whenever a form is opened, If there is a value in the OpenArgs argument, then this can be used in that procedure. Of course we *do* have such a value, it is 'Me.Member_ID'
By opening the Relatives form in design view and immediately clicking Properties we can find the Event procedures relating to the Form, (see right) which includes the On Open event. Here is the code for the Form_Open event procedure, which uses the OpenArgs value that has been passed to it.
If OpenArgs is empty, nothing happens because the String Length is not > 0. Otherwise, the value of OpenArgs is passed to a variable called strMemberID. This is used to explicitly find the first filtered record, but we could just as easily go to one of the other filtered records, buy amending the arguments of FindRecord, which are summarised here: - FindRecord(FindWhat, Match, MatchCase, Search, SearchAsFormatted, OnlyCurrentField, FindFirst)
Summary: When the user clicks the 'View/Add Relatives' button on the Members form, the On Click event procedure fires, opening the Relatives form, and sending arguments to its Open_Form event. The Relatives form is opened and the WhereCondition ensures that the records shown are filtered with only those records in the Query that include the Member ID taken from the Member_ID text box on the Members form. When the Relatives form opens, the Open Form event fires, and takes any value present in the OpenArgs argument.
