The Best Access Master Detail Form Tutorial

advanced access vba

Access Master Detail Form Tutorial

Utilizing SQL to create an Access Master Detail Form

 

Suggested Udemy Courses:

Microsoft Access 2016 Master Class: Beginner to Advanced

Access for Excel Users

Access VBA for Non-Programmers

Microsoft VBA Workshop

Access Workshop

 

This tutorial will use the super store sample data to explain how to create a proper Main Form / Sub Form structure to edit and maintain each company’s sales data.

The first required step is to create the underlying required data. A master detail form approach usually mimics a Primary Key / Foreign Key entity relation type. Consider that we must have a set of records that will assign a proper primary key to each customer’s name. In the end we will have a conceptual scheme like the one below

masterformsql1

We will need to create a new table having the following structure and after that we will explain the required steps to produce and map records using SQL.

The fields CUSTOMER_NAME and ADDRESS can be 255 characters long PHONE can be 50 characters long.

masterformsql2

Once all data types and sizes are defined right click on ID_CUSTOMER grey row heading and select Primary Key from the context menu. ID_NUMBER will be the unique identifier for each customer record.

masterformsql3

After creating the above table using Access table editor save it as tblCustomers, it is empty for now.

Now let’s migrate the customers’ names to the created table letting the primary key (a sequential id) generate itself (access will do that work for us as its underlying data type is Autonumber). To perform this we will run an INSERT sql statement eliminating the existing duplicates values in CUSTOMER_NAME. Once we will only insert data into CUSTOMER_NAME field, the insert statement must explicitly specify the fields that will receive data. In the create tab select query design, close the presented tables list, right click and select SQL View from the context menu. Paste the following SQL statement into the SQL area

INSERT INTO tblCustomer (CUSTOMER_NAME)

SELECT DISTINCT CUSTOMER_NAME

FROM tblOrders

The reader may notice the CUSTOMER_NAME field name inside brackets. That means only that field needs to receive data (unless some other field is mandatory and does not allow null values, but that is not the case). After pasting the SQL statement save the query as qryDefineCustomerMasterData

masterformsql4

The reader will notice the plus sign near the query name. This means the query is of the append type, it will insert data into a table. The reader can now execute the query (as a side note consider queries do not need to be saved to be executed) just by double clicking on it. After being prompted regarding the number of records to be inserted,

masterformsql5masterformsql6

answer yes to both questions and the distinct list of customers’ names will be appended to the tblCustomers table (initially empty). Please double click in tblCustomers table and check if data was properly inserted, the final outcome should be similar to the one presented below.

masterformsql7

In the next step we will migrate the generated primary key to the “child table” tblOrders so the CUSTOMER_NAME can be removed from it. First let’s add a new field called ID_CUSTOMER_FK (the FK suffix stands for foreign key, it means this field will reference a value that exists as a primary key in another table). So, put the tblOrders in design mode by clicking on it with the right mouse button

masterformsql8

Once in design mode right click on the first field and select Insert Rows

masterformsql9

In the empty created row, name the corresponding field as ID_CUSTOMER_FK and define its type as Number (Long – in the bottom page data type settings).

The field type must accommodate the referenced field data type so it cannot have less precision.

masterformsql10

masterformsql11

Save the table and close the editor.

The next step will be the execution of an update query that will pull the corresponding ID present in tblCustomers using its description present in tblOrders to match. For this update step we will execute an UPDATE sql instruction mixed with a DLOOKUP access function.

The SQL statement to be executed is the following

UPDATE tblOrders

SET ID_CUSTOMER_FK = DLOOKUP(“ID_CUSTOMER”,”tblCustomers”,”CUSTOMER_NAME = ‘” & REPLACE(CUSTOMER_NAME, “‘”, “””) & “‘”)

WHERE ISNULL(CUSTOMER_NAME) = FALSE;

The logic is the following, per each row in table tblOrders the ID_CUSTOMER_ID will assume the value returned by the function DLOOKUP. To be familiar to the DLOOKUP function syntax please visit

https://support.office.com/en-us/article/DLookup-Function-8896cb03-e31f-45d1-86db-bed10dca5937

Based on its syntax and parameters, the actual DLOOKUP call is looking for the ID_CUSTOMER field in the table tblCustomers where the CUSTOMER_NAME (in table tblCustomers) is equal to the customer name present in the current row being updated. The last part is achieved by concatenating the expressions

“CUSTOMER_NAME = ‘” & REPLACE(CUSTOMER_NAME, “‘”, “””

The green part refers to the actual row CUSTOMER_NAME value and the blue part corresponds to the tblCustomers CUSTOMER_NAME field. The concatenation is just redefining the function criteria expression for each row. The replace function is present to make sure the SQL string delimiter is properly escaped otherwise the SQL query will return an error. The final WHERE clause is applied as it does not make sense to update rows if the CUSTOMER_NAME is blank.

Save the query and name it qryPullCustomerPrimaryKey.

The DLOOKUP function is not SQL ANSI standard (please check https://en.wikipedia.org/wiki/SQL and https://www.w3schools.com/sql/sql_syntax.asp) and it can be used in queries or VBA code, it is very useful in VBA as it avoids complex recordset operations to achieve the same outcome. There are other non ANSI standard functions that can be used, DMAX, DMIN, DCOUNT, DFIRST, among others.

To run the query just double click on it and answer yes to the prompted questions or warnings. All rows should be properly updated. The final outcome should be similar to

masterformsql12

The reader should notice the first column was populated with the corresponding customer ID from the tblCustomers table. Now that we filled the ID_CUSTOMER_FK column it does not make sense to keep the CUSTOMER_NAME column in the orders table. Right click on it and from the context menu select delete field.

masterformsql13

Now let’s create the proper relation between the two tables. From the tab Database Tools click on Relationships

masterformsql14

Drag the two tables from the left pane to the relations pane. Now drag the field ID_CUSTOMER from the table tblCustomers to the field ID_CUSTOMER_FK from the table tblOrders.

masterformsql15

The relation parameter definition window will open

masterformsql16

Select the three check boxes so the proper referential integrity is applied if the parent record is deleted or updated

masterformsql17

Hit create button and the table relation is created

masterformsql18

Now let’s pass this relation to forms.

The first required step is the creation of the customer master information management window. The Access wizard is enough for this example so, click the form wizard button under the tab Create.

masterformsql19

After that select tblCustomers from the list and drag all fields to the right blank area, hit next

masterformsql20

Select the Justified layout and hit next

masterformsql21

Name the form frmCustomers and click finish. The master form should look like the next image

masterformsql22

Now let’s create the sub-form that will be used to show each company’s orders data. Once again click the form wizard button

masterformsql23

Move all fields to the right area except ID_CUSTOMER_FK (this field could be migrated as well but it is not required as it does not need to be shown). Please note that when selecting fields to be added the user is selecting the fields that will be bounded to form controls so we are only informing the wizard which controls need to be created in the form. The underlying table field is always available despite not being showed. That is why it will be possible to link the master form to the sub-form in the next steps. Hit next.

masterformsql24

In the above window select the Tabular option so the orders appear as a list in the new bottom detail area we are creating (Datasheet would also be possible but it does not allow deep control formatting), press next. Name the form frmOrdersSub and press finish

masterformsql25

The form is created and its layout should be similar to the one presented below. The actual form is presented using the continuous forms layout type (check image below). The continuous forms layout type will repeat the set of controls present in the form detail area for each record, that way rows are presented like a list.

masterformsql26

masterformsql27

The reader will notice data is not properly presented. This is due to control sizes and formats so, after using the wizard, some manual formatting work is required. The reader can re-size the text boxes and labels manually or use the height and width properties. It is possible to change the textboxes font, size, color, and other properties so data is presented the best way possible. After some manual design and formatting work the final layout can be as follows. After some re-design a possible approach to both forms is

masterformsql28

And the sub-form not yet inserted in the main form

masterformsql29

Now please close the frmOrdersSub form, put the frmCustomers form in design mode and, from the left pane grab the frmOrdersSub form and drag it to the frmCustomers in design mode below master form last field. Check the next image

masterformsql30

Now let’s tell Access how the forms link (remember the Primary Key / Foreign Key logic?). Select the sub-form and access its data tab in the Property Sheet (right pane). The tab is the following

masterformsql31

Remember naming conventions and the relation we created?

Access is smart enough to understand database design properly. It identifies the master table / form primary key is the field ID_CUSTOMER and the linked field is ID_CUSTOMER_FK. Even if no relation was created Access is able to suggest a relation based in fields’ names.

Either way, if this is not the way both forms should link, the reader can define a field or set of fields from both forms to link each record from master table to each related record in the child table. The configuration form is the following and it is accessed by clicking in the three dots in ID_CUSTOMER or ID_CUSTOMER_FK area in the property sheet pane

masterformsql32

The reader must pay attention Access is not able to link any type of field. Their underlying data types must be compliant. Also the reader should pay attention not all relations make sense so proper database design knowledge is required.

After accepting the suggested link between forms right click on the top sub-form title label and delete it (the user can keep it if a proper sub-form title is required)

masterformsql33

Go to the top left corner and select form view from view button

masterformsql34

The final form / sub-form layout should be as follows

masterformsql35

The reader may notice the final layout is much better looking than the default values created by the wizard. With some work Microsoft Access can produce highly professional solutions. That is related and will depend mostly on personal taste and development experience. One rule of thumb should be applied: once a style is defined try to use it all over the development. But consider that form design takes a lot of time and to get well designed forms proper and standard measures should be applied.

If the reader navigates through the records using the main form navigation control arrows, it will be evident the sub-form list changes accordingly. Per each record the user sees in the main form the related child order records are properly refreshed in the below area.

masterformsql36

Now let’s add a simple form operation to the sub-form, a delete record button. It will allow the user to delete a row from the list. Close the main form and at same time the sub-form. Open the sub-form and put it in design mode.

masterformsql37

Extend the footer sub-form area by clicking in the beginning of the row and pulling down.

masterformsql38

Create a button in the form footer and cancel the button wizard

masterformsql39

Name the button cmdDelete and change its caption to Delete. Right click in the button and select build event from the context menu.

masterformsql40

Now access VBA code editor by selecting code builder. Add the following code to the button click event

Private Sub cmdDelete_Click()

On Error GoTo cmdDelete_Click_err:

 

‘the user is promped if the record should really be deleted

If MsgBox(“Are you shure you want to delete the selected record?”, vbYesNo + vbQuestion, “System question …”) = vbYes Then

DoCmd.SetWarnings False ‘this option switch off Microsoft Access standard warnings as we are testing it with a proper message

DoCmd.RunCommand acCmdDeleteRecord ‘the record is deleted

DoCmd.SetWarnings True ‘put the SetWarnings back to its default value

‘A message informing the user the record was properly deleted

Call MsgBox(“The selected record was deleted with success.”, vbOKOnly + vbInformation, “System information…”)

End If

Exit Sub

cmdDelete_Click_err:

Select Case Err.Number

Case Else

Call MsgBox(Err.Number & ” – ” & Err.Description, vbOKOnly + vbCritical, “System Error …”)

End Select

End Sub

 

The green comments explain what each line is doing in the code flow. The reader should pay attention to the fact the record that is selected in the sub form will be the one to be deleted when the delete button is pressed.

The message presented to the user will be similar to

masterformsql41

Answering yes will delete the selected record, selecting no will cancel the operation.

This last button operation ends the actual tutorial. Once again we must mention these are only small examples regarding Access capabilities. With proper training, experience and good specifications very complex solutions can be developed. In our opinion the developer should always balance the pros and cons of using visual studio solutions (C# or VB. net) against Microsoft Access.

Leave a Reply

Your email address will not be published. Required fields are marked *