Sub Datasheets

Sub Datasheets are a way to provide lookup and edit functionality with tables engaged in a one-to-many or one-to-one relationship. In the table on the 'one' side of the relationship we place the sub datasheets that enables us can lookup and edit the rows in the table on the 'many' side of the relationship.

Sub datasheets may be used in tables, queries, form datasheets, or in sub-forms. Microsoft Access automatically creates a sub datasheet on the "one" side of the relationship, provided that the sub datasheet Name property of the table is set to [Auto] - which is the default. So if everything went smoothly up to this point, all the setting up of sub datasheets has been already been done for us when the tables were originally created. So we are going to remove all setting up that Access has automatically done, and redo it manually and learn what it all means for ourselves . Once we have an understanding gained from setting up sub datasheets manually, then we will write a generic VBA subprocedure to automate the process on any table in the current database.

Lets get some background first: below we have a screenshot of our table relationships - focus on two relationships: 1. tblIceCream table and tbliceCreamIngredient and 2. tblIngredient and tblSupplierList . Note that they are both one to many relationships.

We are going to set up two sub datasheets:

In the tblIceCream (One) and tbliceCreamIngredient (Many) One-to-Many relationship
In the tblIngredient (One) and tblSupplierList (Many) One-to-Many relationship

The sub datasheet is placed in the Subdatasheet Name property of the table on the One side of the relationship.

The reason why these sub datasheets are useful is because: In the first example, when a user opens tblIceCream they can view the ingredients that are used in a selected ice cream. So in this case we may have a situation where they want to add an ingredient to a selected ice cream, so by looking up Walnut wonder ice cream in tblIceCream they can use the sub datasheet ( tbliceCreamIngredients ) and see all the ingredients that are currently used for that ice cream and so add a new valid ingredient, Apple for example.

In the second example, when a user opens up tblIngredient they can view the suppliers that supply the selected ingredient and they can edit the list of Suppliers that supply that selected ingredient - which is a very intuitive way of working. For example, they may need to order more apples, and so looking up Apples in the tblIngredient they use the sub datasheet ( tblSupplierList ) to see which company supplies apples - 'Fran and Nick's Fruit and Nuts'.

In the Try It Out we will do this manually so that we get a feel for what we are doing, and then once we feel comfortable with that, then we will use VBA code. It is good practice to slowly work through a manual process - in "slow motion" - until you get a feel for what the code will have to do and the sequence in which it will carry out its role, and then start coding. What we are trying to build up is a mental picture or visualization to take with us to the code editor - coding is less frustrating once you have that all important mental picture of the way things work.

Now let's manually create two sub datasheets and reinforce what we have just discussed.

Try It Out-Creating Sub Datasheets (Manually)

Before we begin to create the sub datasheets we need to remove the sub datasheets that were automatically added by Access. We do the following in the tblIceCream and tblIngredient tables:

Open tblIceCream in datasheet view and remove the sub datasheet by selecting the following from the menu: Format Subdatasheet Remove , save the table and close it - do the same to tblIngredient .
To create a sub datasheet open up tblIceCream in datasheet view and select from the menu Insert Subdatasheet An Insert Subdatasheet dialog should appear - select tbliceCreamIngredient from the listbox and make the respective selections from the drop-down boxes as shown below, and then click OK and save the table.
Now repeat the process with tblIngredient by opening it in datasheet view and selecting Insert Subdatasheet - select tblSupplierList in the listbox and ensure the following selections are in the drop-down boxes, then click OK , and save the table.
We have now created both sub datasheets, and so let see what effect this has had on the properties of the table. If you select tblIceCream in design view and select the table properties you should have the same as below:
If you now select tblIngredient in design view and select the table properties you should have the same as below:
Let's now take a look at the effect of the sub datasheets: open up tblIceCream in datasheet view and click on the top left + and you should see the contents of tbliceCreamIngredient in the sub datasheet, as shown below:
Now open up tbliceIngredient in datasheet view and click on the top left + and you should see the contents of tblSuppliesList in the sub datasheet, as shown below:
Now we are going to make some changes to the data. Make tblIceCream the active table (it should be in datasheet view). Click on the + alongside Walnut Wonder ice cream and it should be the same as the screenshot in Step 6 above. To this product we want to add another ingredient - an Apple - so if you click in the grid below Vanilla Extract , a drop-down box should list the ingredient choices that are available from the tblIceCreamIngredient table. Select Apples and enter 1 in the Quantity column, then click Save.
Now make tblIngredient the active table (it should be in Datasheet view). Click on the cross alongside Apples - it should be the same as the screen shot in Step 7 above. To this ingredient we want to add another supplier - Amethyst Group - so if you click in the grid below Fran and Nick's Fruit and Nuts , a drop down should list the company choices that are available in the tblSupplierList table - select Amethyst Group and enter 2.95 in the PricePerUnit column, then click Save .

How It Works

In the introduction we said that we were going to set up two sub datasheets:

In the tblIceCream (One) and tbliceCreamIngredient (Many) One-to-Many relationship
In the tblIngredient (One) and tblSupplierList (Many) One-to-Many relationship

We did this and we can see in tblIceCream the subdatasheet Name property is Table.tblIceCreamIngredient . The Link Child Fields and Link Master Fields properties were automatically filled in from the dialog

We said that the reasons why these two sub datasheets are of value are: In the first example when we opened tblIceCream we viewed the ingredients that are used in Walnut Wonder ice cream, so we can use the sub datasheet ( tbliceCreamIngredients ) to add a new ingredient, Apple.

In the second example when we opened up tblIngredient we viewed the suppliers that supply the Apple ingredient: so we use the sub datasheet ( tblSupplierList ) to add a new Apple supplier: Amethyst Group .

From having worked through the exercise manually we are now well placed to understand the process and replicate it using VBA code. In our code we will write a generic subprocedure that will accept as parameters all that we need to set up a sub datasheet on any suitable table combination: to be suitable the tables need to be in a one-to-one or one-to-many relationship. In our code we target the properties of the table on the 'one' side of the relationship.

Let's open up our chapter code module ( Chapter 8 Code ) and start coding.

Try It Out-Creating Sub Datasheets (Code)

Enter the following code:

   Sub SubDataSheet(ByVal strSDN As String, ByVal strLCF As String, _     ByVal strLMF As String, ByVal strTbl As String)     Dim db As Database     Dim tbl As TableDef     'Open the database     Set db = CurrentDb()     'Open the table     Set tbl = db.TableDefs(strTbl)     'before: comment out the following With block when finished debugging     With tbl     Debug.Print .Properties("SubdatasheetName")     Debug.Print .Properties("Linkchildfields")     Debug.Print .Properties("Linkmasterfields")     End With     'Set the property values - this will override the current values     'saving us from having to remove them beforehand.     With tbl     .Properties("SubdatasheetName") = strSDN     .Properties("Linkchildfields") = strLCF     .Properties("Linkmasterfields") = strLMF     End With     'after: comment out the following With block when finished debugging     With tbl     Debug.Print .Properties("SubdatasheetName")     Debug.Print .Properties("Linkchildfields")     Debug.Print .Properties("Linkmasterfields")     End With     End Sub

Now test the code by opening up the Immediate window and entering the following, then pressing Enter .
SubDataSheet "Table.tblIceCreamIngredient", "fkIceCreamID", "IceCreamID", "tblIceCream"
You should see the following:

How It Works

We create a generic subprocedure passing ( ByVal ) the necessary string values:

 Sub SubDataSheet(ByVal strSDN As String, ByVal strLCF As String, ByVal strLMF As String, ByVal strTbl As String)

Then we drill down the Access object model until we hold the table that we want to address in the variable tbl .

 Dim db As Database   Dim tbl As TableDef   'Open the database   Set db = CurrentDb()   'Open the table   Set tbl = db.TableDefs(strTbl)

After which we print out the current property settings before we change them:

 'before: comment out the following With when finished debugging   With tbl     Debug.Print .Properties("SubdatasheetName")     Debug.Print .Properties("Linkchildfields")     Debug.Print .Properties("Linkmasterfields")   End With

Having done that we then get down to the business of assigning the property values passed in as parameters:

 'Set the property values - this will override the current values   'saving us from having to remove them beforehand.   With tbl     .Properties("SubdatasheetName") = strSDN     .Properties("Linkchildfields") = strLCF     .Properties("Linkmasterfields") = strLMF   End With

And just to check everything is OK we print the updated property value to the Immediate window and then close the subprocedure:

 'after: comment out the following With when finished debugging   With tbl     Debug.Print .Properties("SubdatasheetName")     Debug.Print .Properties("Linkchildfields")     Debug.Print .Properties("Linkmasterfields")   End With End Sub