You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today's guest blogger is Access MVP Scott Diamond. Scott is the principal of Diamond Computing Associates and the author of Microsoft Office Access 2007 VBA.
This is a very frequently discussed issue. 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.
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.
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.
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.
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 Foreign Key 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.
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.
Comments: (10) Collapse
I'd prefer Name index for ComboBox/ListBox. I've been asking for it for ages... no success so far.
With Column property it is very difficult to re-arrange ComboBox/ListBox.
I think you should mention also about speed/performance of those four different approaches.
Nice overview Scott. I had to wade through and figure these out myself - this will come in handy for anyone newly exposed to the concept.
Excellent tutorial, Scott. Thank you.
Vladimir: I'd prefer Name index for ComboBox/ListBox. I've been asking for it for ages... no success so far. In Access 2003 & later you can already do that via the Recordset property. In fact, I do most of my VBA manipulation upon Recordset property of form/combobox/listbox objects in favor of DoCmd equivalent. Not better, just a different way and in my case, more convenient.
Vladimir,
You can refer to the value of a column in a combobox by name if you use a function like the following (if the combobox rowsource is based on a table or you assign a recordset to it using VBA): Public Function ComboColumn(Combobox As Combobox, FieldName As String) As Variant Dim lngCount As Long Dim lngResult As Long Dim blnFoundField As Boolean blnFoundField = False For lngCount = 0 To Combobox.Recordset.Fields.Count - 1 If Combobox.Recordset.Fields(lngCount).Name = FieldName Then lngResult = lngCount blnFoundField = True Exit For End If Next lngCount If blnFoundField = True Then ComboColumn = Combobox.Column(lngResult) Else ComboColumn = Null End If End Function Thus you might want to know the value of the Surname column in a combobox called cboNames on an open form called frmNames where the Surname column is not necessarily the bound column. To do this you would use ComboColumn(Form_frmNames.cboNames,"Surname") Unfortunately it doesn't seem to be possible to use it directly in a query via the design window and so you would have to set up the SQL dynamically. Alan
PS You can get to the surname value from a query if you put cboNames.Tag = ComboColumn(cboNames, "Surname") into the AfterUpdate event code of the combobox (and Form_Open event code if you set the combobox's default value?), i.e. Private Sub cboNames_AfterUpdate()
cboNames.Tag = ComboColumn(cboNames, "Surname")
End Sub You can then use [Forms]![frmNames]![cboNames].[Tag] as a criterion in your saved query. Alan
Scratch that. There is a bug in Access 2007 comboboxes and listboxes where the first time you run the above code, the recordset does not sync with the selected values, though the second and later times it does. The following seems to work OK though. Public Function ColumnValue(ListOrCombobox As Object, FieldName As String) As Variant Dim intBoundColumn As Integer Dim intRecordsetField As Integer Const conQuotes As String = """" Select Case ListOrCombobox.ControlType Case acComboBox, acListBox With ListOrCombobox intBoundColumn = .BoundColumn intRecordsetField = intBoundColumn - 1 If IsDate(.Recordset(intRecordsetField)) Then 'Date .Recordset.FindFirst .Recordset(intRecordsetField).Name & " = #" & Format$(.Value, "MM/DD/YYYY") & "#" ElseIf Not IsNumeric(.Recordset(intRecordsetField)) Then 'String .Recordset.FindFirst .Recordset(intRecordsetField).Name & " = " & conQuotes & .Value & conQuotes Else 'Plain number .Recordset.FindFirst .Recordset(intRecordsetField).Name & " = " & .Value End If ColumnValue = .Recordset(FieldName) End With Case Else MsgBox "Please inform the developer that the control " & ListOrCombobox.Name & " has an error.", _ vbCritical, "Wrong control type" ColumnValue = Null End Select End Function
Banana: In some occassions I do use Recordsets. It's very easy but ComboBox/ListBox name index would be much more convenient.
Alan: Thank you very much for your time. I'll put your code into my latest project to test it immediatelly.
Comments: (loading) Collapse