Displaying data from related tables on one form

This is a question I often get asked on Q&A sites. But before I get into the methods, you need to understand one of the principles of relational databases. That principle is that data should exist in one place only. Having the same data in multiple tables is a violation of normalization. Related records are indicated by a Foreign Key within the record that holds the Primary Key value of the parent record. So when you want to have data from multiple tables on a data entry form, you set it up to display the data not store it in the form’s Recordsource
There are basically four ways to display related data on a form; Subforms, The Column property, DLookups and Listboxes. Ill discuss each in turn and suggest where to use each.
1) Subforms. You can use a subform to display several fields from the related table. Create the form using the Subform wizard or create a separate form and place it on the main form as a subform (I generally create a separate form). Using the wizard, you go through the following steps.
• Select whether to use an existing form or create a new one
• If you are creating select the table and fields to use
• Select the linking fields, usually accept the defaults Access proposes
You can customize the subform, so it looks like part of the main form, by removing record selectors, navigation buttons, borders etc. I use subforms when I want to display 4 or more fields from the related record. Another advantage of using subforms is where you have a One to Many relation. Using a Continuous Form or Datasheet view, you can display multiple related records at once.
2) Column Property. Generally Foreign Keys are entered by selecting the related value from a combobox. The combobox uses a query as it Rowsource. This query displays the records from that parent table. At the least, the query includes the primary key field as its bound column and a description field. However, you can add as many other fields from the table as you want. These fields can then be reference using the Column property. Click the Build button […] next to the Rowsource property to enter Query Design Mode. In Query Design Mode you can add tables and fields to the query. You can control what fields actually display in the pull down list by setting their Column Width. Setting the width of an individual column to 0″ will hide that column (Note: Column widths are entered separated by a ; for each column listed in the column Count). The combobox will only display the first non zero length column after selection. The following properties of a combo are key to using combos in this way: RowSource (the list), Bound column (what’s actual stored), Column Count (how many columns in the list, Column Widths (the size of each column in the list).
You can then set the ControlSource for an unbound control to:
=[comboxname].Column(x)
Where comboxname is the name of the control and x is the number of the column in the query for that field. Note: the column count starts with 0 so the 3rd column is 2.
Since the combobox selects a single record, the Column property will also reflect a single record. I use this method if I need to display 3 or less values from the related record.
3) DLookups. DLookups allow you pull a value from a field in a specific record. It uses the syntax: DLookup([fieldname],table/queryname,Criteria). The Criteria is used to specify the record you want to return. Since the Comboxname will store the FK value you would use a criteria like:
“[keyfield] = “ & [Comboboxname].
This would also be used as the controlsource of an unbound control. Each DLookup should only be returning data from a single record. If its possible that the DLookup might not find a matching record you should use it within a NZ (NullZero) function to prevent errors. I use DLookups when I need to pull data from different tables based on a key value.
4) Listboxes. A Listbox can have multiple columns with column headers. It also can be set to display multiple matching records. I will, sometimes, use a Listbox in place of a continuous form or datasheet subform. Listboxes will also display multiple matching records.
There are two exceptions to the rule of not repeating data in multiple tables. The first is the PK value. Obviously, that value has to be repeated as the FK to relate the records to each other. The other exception is time sensitive data. Sometimes you need to freeze data that will change over time. The best example of this is price data. For example: In an order entry application, you want to freeze the price at the time of the order. In such a case, you would have the Price field repeated in the OrderDetails table. Generally you would use the Column property for this and populate the control in the After Update event of the Products combo use code like:
Me.txtPrice = Me.cboProduct.Column(2)
These guidelines should help you build forms that preserve normalization and are well organized and easy for the user to use.
Scott<>

© 2006 Scott B. Diamond — Diamond Computing Associates

Advertisements

22 Comments

  1. I was very excited to have gotten a response to my question. The information in this post have been very helpful. I am glad that their are knowledgable people still willing to let a helping hand when times get rough! Great SITE!! AAA+++

  2. Hi Scott
    I am having a problem crating a form and i would like your help
    I have a form with two list box fields, one pulls a “order number” and another pulls the “Order number item”, both required fields.
    The order number item as, in the list query, a filter so that the user only see the itens of the order select.
    However, i need to update the item field if the user changes the order number.
    I also want to avoid that the user, after insert a first set of values, changes the order number and close the form (or moves to a new record), without update the item field.
    I use this code in the AferUpdate of the “Order”:

    Private Sub Order_AfterUpdate()

    [Item].SetFocus
    [Item] = ” ”
    [Item].Requery

    End Sub

    I tried [Item] = null but the field is marked as required and not null, so it doesnt accept. However, eevn if i use a function to stop the user from leave the field with the ” “, for instance, in the LostFocus of the Item field, like this:

    Private Sub Item_LostFocus()

    If [Artigo] = ” ” Then
    DoCmd.CancelEvent
    MsgBox “Select a valid item”
    [Item].SetFocus
    End If

    End Sub

    The form still moves to a new record, and save the content, even showing the message

    Can you help?

    Thanks and Best Regards

    Jose Calvo

  3. First are these Listboxes or Comboboxes? What is the Recordsource of this form? I’m not really following your work flow. What does this form do? Why are you selecting an existing order and line item and what are you going to do with it after selection. I need to understand this to help.

    Scott

    • Hello again
      This are combo boxes
      The source is a table where we want to record contacts made with the supplyers and control situations with returning itens that where ordered but for some reason need to be returned.
      With this table we keep a record of this contacts and can trace better situations that usually are resolved only by phone.
      The person responsable for making the contact (and create the regestry) pulls the form, enters the orders and the item and them has a field to input why is this item need to be returned.
      Note that is not a returning regestry. That would be done after, or even not done, because if we face a simple change of itens, usually there is no need for more papers. It is only for internal control, so that the person responsable for sending the order and control the financial transaction (different from the one that controls the delivery of the itens) can know that may be a need for a change in the order.
      Hope it was clear 🙂
      Best Regards

      • Yes, much clearer now. This is the way I would do it. The OrderNo combo would be UNBOUND, therefore not required. In the After Update event of the Order No combo, I would requery the Item combo. The RowSource of the Item combo would include the Order No field. For example:

        SELECT ItemID, ItemDescription, OrderNo
        FROM tblOrderDetails
        WHERE OrderNO = Forms!formname!cboOrderNo
        ORDER BY ItemDescription;

        I would then have a hidden text box which would be populated in the After Update Event of the Item combo:

        Me.txtOrderNO = Me.cboItem.Column(2).

        Now if you want to make sure the Item is changed if the Order no is changed, then in the After Update event of cboOrder use:

        Me.cboItem=Null

        So everytime they change the Order the Item is blanked. But whenever they select an item, the correct Order is populated.

        Hope this helps,
        Scott

  4. Hi
    I was able to make it function, but not exactly how you suggest.
    I made the hide text box and i populate it with the item reference.
    I made a button to save the record and i put in the code a if instruction
    First i was checking if the text field was null, but for some reason it didnt recognize the field as being null, so i “inverted” the question and check if it was igual to the item field.
    This way i dont need to make the order field unbound.
    Thanks for your help
    (i am thinking right now that a check box may be a easy way to make it work…)

  5. But if you have the Order combo bound, then you don’t need the hidden reference at all. The idea I had was to use the Order combo only the filter the Item combo. And once the Item was selected you then stored both the item and order #.

    • I see what you mean, but if i try to clear the item field (that is bound) after i change the order field, Access dont let me atributte a null value to the field, even if only temporarily (i am using Access 2007, by the way), because the field is marked as required and not null. But is “working as intended” 🙂

  6. First, you don’t have fields on a form. You have controls that may or may not be bound to a field in a table. This is a subtle but important distinction. If a field or even a control is required, I don’t believe its checked for a value until the record is committed. So you should be able to set a control as null, even for a bound control. But if that’s the case, what you might consider is adding an unselected choice to your RowSource. You can then set the control to Unselected.

    Scott

  7. Hi Scott, I have access database with one table some of my members are family members that has the same Member_ID_number. On a form I have combo box that brings in all Member_ID_numbers. I want to select one Member ID number(that could have 3 matches) so it can auto populate the three matches(records) to the fields on my form. My code will work with msgbox naming all two or three with matching IDs. But when it goes to my form the three text boxes(for 3 matches) shows data only for the first person. I want it to show 3 different names for 3 matches with the other fields corresponding to the 3 individuals. Is this possible? I know i am clear as mud. when I run a query it shows three individual records as design but I like putting it in a form design. thanks Here is my code:
    Dim rs As DAO.Recordset
    If Not IsNull(Me.Combo25) Then
    If Me.Dirty Then
    Me.Dirty = False
    End If
    Set rs = Me.RecordsetClone

    rs.FindFirst “[Member_ID_Number] = “”” & Me.Combo25 & “”””
    ‘ Me.Text188 = Null
    If rs.NoMatch Then
    MsgBox “No Record found”

    Me.Combo25 = Null

    GoTo Cleanup

    Else

    Do While Not rs.NoMatch

    MsgBox “First Name: ” & rs!First_Name
    Me.Bookmark = rs.Bookmark

    rs.FindNext “[Member_ID_Number] = “”” & Me.Combo25 & “”””

    Loop

    rs.FindNext “[Member_ID_Number] = “”” & Me.Combo25 & “”””

    End If

    End If

    Cleanup:
    rs.Close
    Set rs = Nothing

    • I’m sorry but you aren’t being very clear. If you have a situation where members share the same MemberID then you need to use a child table with MemberID as a foreign key. To list all members on form, you would use a subform bound to the child table to list each child record.

      If that’s not what you need let me know.

      Scott

  8. Scott I need to ask a question about Access and I have done this before but for the life of me I cannot remember how to make it work. I am creating a Safety Training database and I have created several entry forms to encompass all areas of training. I then have a main form with buttons to each subform. On one of my training forms I have several trainings that can be completed on the same date. I would like to auto-populate the date from to several other fields in the form. Is this possible in Access 2010?

    • Certainly its possible, but I’m not sure I understand. Each Training to be completed should be a separate record. If you want to have each record default to the date from the previous record you can do that by setting the Default value of the control to the current entry.

      If that’s not what you need please clarify.

      Scott

      • I have several training classes that are part of our New Hire Safety Orientation which we are required to show the exact date they took each segment of this training. Some of these training sessions are covered on the same date but I must show them in individual reports each month. I would like to auto-populate the date from one training to the training that will be covered in the same day. I need the code to show this.

        So to be clear I have a date field in my form that is generalized to “New Hire Safety Orientation” I then have several fields below that which encompass all sections of that training but it is over the course of several days. So I have few other fields that will need to have the same date as the “New Hire” field. Can you please let me know what how and where I would code that.

  9. Hi Scott! Happy New Year!

    I was hoping you could help me with your sequential numbering code. I have a combination of scenario 2 and 3 and am using this:
    = Nz(DMax(“[Sequence]”, ”job”, ”Year([receiveddate]) = “ &
    Year(Me.[currentyear]),0))+1) and am getting an invalid syntax error. I’d be tremendously grateful for your help!

    • Well first, what is the exact error message? it may not mean anything to you but it may give me a clue.

      Also is job, the name of your table? And is receiveddate the name of a field in that table? Finally is currentyear, the name of a control on your form? Also, what is the Controlsource of the control? Is it bound to a Date/Time datatype or does it just hold a year value as the name suggests?

      Scott

      • Sorry, thought everyone could read my mind 🙂 Job is the name of my table, receiveddate is a field in my table and on my form. CurrentYear is a control on my form, derived from =Format(Date(),”yyyy”) the control source for the field I’m trying to increment, “correctjobid” is = Nz(DMax(“[Sequence]”, ”job”, ”Year([receiveddate]) = “ &
        Year(Me.[currentyear]),0))+1) and I am getting “the expression you entered contains invalid syntax.You may have entered an operand without an operator.”

        Thank you for your time Scott!

  10. OK, First, the Format function creates a text value. So comparing Year(ReceivedDate) to Year(Me.CurrentYear) is going to throw an error. You should do it as:

    “Year(Receiveddate) = ” & Val(Me.currentYear)

    Second the Dmax expression should NOT be used as a Controlsource. This is explained in the blog. Also, You getting the Max of a field named Sequence, but you said you are trying to increment correctjobid. The field name in the Dmax should be a long integer datatype that contains the sequential number) If you are adding anything to that number to create an identifier, that is done with an expression.

    You should be assigning the Dmax to a control on your form bound to the Sequence field (or whatever you want to name it). This should be done in a code module that is triggered by an event of your choosing. This is also explained in the blog.

    Scott

  11. This is a little different, but kind of the same subject. I’m doing a covenants violations database for our HOA. I have all the properties (1,300 of them) listed in a table and there shouldn’t be any additions to it. I then have the complaints in another table and then the actions/findings in a third table. When I’m doing a form, I’ve been thinking I should do the data from the property table as the main form, with the complaint information as a sub form and the action/findings table as a sub-sub form. Is this the best way or can you suggest another way of thinking about the data? The property table just includes the street address, the division and the lot information. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s