ACC: Four Ways to Move to a Record from a Combo Box Selection

Last reviewed: February 4, 1998
Article ID: Q100132
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article lists four different methods of moving to a specific record based on a combo box selection.

 Method 1: In the AfterUpdate property of the combo box, call a macro
           using the FindRecord action.

 Method 2: In the AfterUpdate property of the combo box, call a macro
           using the ApplyFilter action.

 Method 3: Use a Form/Subform, with the combo box in the main form and
           the data in the subform, bound by the LinkMasterFields and
           LinkChildFields properties.

 Method 4: Base the form on a query joining two tables and bind the combo
           box to the field that controls the join, using the AutoLookup
           technique.

These four methods are outlined in the "More Information" section and are based on the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 2.0 or earlier).

MORE INFORMATION

The following table compares the features (benefits and drawbacks) of the four methods:

   Method Number:                  1   2   3   4
   ---------------------------------------------
   Requires no code/macros                 x   x
   Saves on subforms               x   x       x
   Can scroll to other records     x       x   x
   Does not require a query        x   x   x
   Can edit records                x   x   x

NOTE: These methods can also apply to text boxes.

Method 1 (For Microsoft Access 97 and 7.0)

  1. Create a form called frmComboTest based on the Products table, and set the form's DefaultView property to Single Form.

  2. Add an unbound combo box using the Control Wizard. Make sure that the Control Wizards button is depressed in the toolbox before creating the combo box. In the Control Wizard dialog box, follow these steps:

        a. Click the "Find a record on my form based on the value I selected in
           my combo box" button, and then click Next.
    

        b. Include the ProductID and ProductName fields, and then click Next.
    

        c. Click Finish.
    

    The Control Wizard creates an event procedure similar to the following:

          Sub ComboNN_AfterUpdate()
             'Find the record that matches the control.
             Me.RecordsetClone.Findfirst "[ProductID] = " & Me![ComboNN]
             Me.Bookmark = Me.RecordSetClone.Bookmark
          End Sub
    
    

  3. View the frmComboTest form in Form view. Note that when you choose a product name in the combo box, you are moved to the record selected.

    (NOTE: This event procedure can also be used in Microsoft Access 2.0.)

Method 1 (For all versions)

  1. Create a form called frmComboTest based on the Products table, and set the DefaultView property to Single Form.

  2. Add the following text box to the form:

          Text Box
          ------------------------
          ControlName: ProductID
          ControlSource: ProductID
          Visible: Yes
    

    NOTE: There is a space in the Product ID field in Microsoft Access 2.0 and earlier.

    NOTE: Include at least the ProductID field on the new form. However, additional fields may help to illustrate that you have changed records based on the value selected in the combo box.

  3. Add an unbound combo box called cboLookup, and set the control's properties as follows. The combo box can be located anywhere on the form, but preferably in the form header or footer.

          Combo Box
          ------------------------------
          ControlName: cboLookup
          ControlSource: <leave blank>
          RowSourceType: Table/Query
          RowSource: Products
          ColumnCount: 4
          ColumnWidths: 0";2"
          BoundColumn: 1
          After Update: mcrLocateProduct
    

    NOTE: If you are using Microsoft Access 2.0 or earlier, set the ColumnWidths property to the following value: 0";0";0";2".

  4. Create the following new macro called mcrLocateProduct:

          Macro Name        Action
          -----------------------------
          mcrLocateProduct  GoToControl
                            FindRecord
    
          mcrLocateProduct  Actions
          ---------------------------
          GoToControl
             ControlName: [ProductID]
          FindRecord
             Find What: =cboLookup
             Find First: Yes
    
    

  5. View the frmComboTest form in Form view. Note that when you choose a product name in the combo box, you are moved to the record selected and the appropriate ProductID is displayed.

NOTE: An example of this method is illustrated with the Products And Suppliers form in NWIND.MDB in Microsoft Access 2.0.

Method 2 (For Microsoft Access 97 and 7.0)

  1. Create a form called frmComboTest based on the Products table, and set the form's DefaultView property to Single Form.

  2. Set the following property for the frmComboTest form:

          Filter: [ProductName] = Forms![frmComboTest]![cboLookup]
    

  3. Add an unbound combo box called cboLookup, and set the control's properties as follows. The combo box can be located anywhere on the form, but preferably in the form header or footer.

          Combo Box
          ----------------------------------------------
          ControlName: cboLookup
          ControlSource: <leave blank>
          RowSourceType: Table/Query
          RowSource: Select [ProductName] from Products;
          BoundColumn: 1
          ColumnWidths: 1"
          AfterUpdate: mcrLocateProduct
    

  4. Create the following macro called mcrLocateProduct:

          Macro Name          Action
          ----------------------------
          mcrLocateProduct    SetValue
                              Requery
    
          mcrLocateProduct Actions
          --------------------------------------
          SetValue
             Item: Forms![frmComboTest].FilterOn
             Expression: True
    
    
When you open frmComboTest and select a product name from the cboLookup combo box, the filter will be set to that value.

Method 2 (For all versions)

  1. Repeat steps 1, 2, and 3 from Method 1 (For all versions).

  2. Create the following macro called mcrLocateProduct:

          Macro Name          Action
          -------------------------------
          mcrLocateProduct    ApplyFilter
    
          mcrLocateProduct Actions
          -----------------------------------------------------
          ApplyFilter
             Where:[ProductID]=Forms![frmComboTest]![cboLookup]
    
    
The ProductID field (or Product ID in Microsoft Access 2.0 or earlier) is the bound field in the combo box. While the combo box displays the ProductName information, it is bound to (or, holds internally) the value of the ProductID.

NOTE: This method is similar to the Suppliers form in NWIND.MDB in Microsoft Access 2.0, which uses the buttons to filter records from A to Z. Also, see the Alpha Apply Filter Buttons macro.

Method 3 (For all versions)

  1. Create a new form not based on any table or query. Add a combo box, and set the control's properties as follows:

          Combo Box
          ----------------------------
          ControlName: cboLookup
          ControlSource: <leave blank>
          RowSourceType: Table/Query
          RowSource: Products
          ColumnCount: 4
          ColumnWidths: 0";2"
          BoundColumn: 1
    

    NOTE: If you are using Microsoft Access 2.0 or earlier, set the ColumnWidths property to the following value: 0";0";0";2".

  2. Create a second form based on the Products table to use as a subform, and set the form's DefaultView property to Single Form.

  3. Add the following text box to the form:

          Text Box
          ------------------------
          ControlName: ProductID
          ControlSource: ProductID
          Visible: Yes
    

    NOTE: There is a space in the Product ID field in Microsoft Access 2.0 or earlier.

    NOTE: Include at least the ProductID field on the new form. However, additional fields may help to illustrate that you have changed records based on the value selected in the combo box.

  4. Save the form, and then use it to create a subform control on the first form by dragging it from the Database window to the detail section of the first form.

  5. Set the subform control properties as follows:

          Subform
          ----------------------------
          LinkChildFields: [ProductID]
          LinkMasterFields: cboLookup
    

By changing the value in cboLookup, Microsoft Access ensures that the records in the subform match the combo box.

The Orders form in Northwind.mdb in Microsoft Access 97 and 7.0 (or NWIND.MDB Microsoft Access 2.0 or earlier) illustrate this method. The Order Details subform is related by the LinkMasterFields and LinkChildFields properties.

Method 4 (For all versions)

  1. Create a table called tblProductSelect, containing a single field ProductID (or Product ID in Microsoft Access 2.0 and earlier). Set the Data Type to Number and the Field Size to Long Integer. No primary key is necessary.

  2. Create a single blank record.

  3. Create the following query called qryProductSelect, based on a join between the ProductID fields of the tblProductSelect and Products tables. Include the following attributes in the query:

          Query: qryProductSelect
          -----------------------------------------------
          Field: ProductID
          Table Name: tblProductSelect
          Show: X (checked on)
    

          Field: <any other fields you are interested in>
          TableName: Products
    

  4. Create a form based on qryProductSelect, and add all fields. You must add at least the ProductID field.

  5. Delete the text box control for ProductID and recreate it as a combo box, as follows:

          Combo Box
          --------------------------
          ControlName: ProductID
          ControlSource: ProductID
          RowSourceType: Table/Query
          RowSource: Products
          ColumnCount: 4
          ColumnWidths: 0";0";0";2"
          BoundColumn: 1
    

The Orders form in Northwind.mdb in Microsoft Access 97 and 7.0 (or NWIND.MDB in Microsoft Access 2.0 and earlier) illustrate this method. It allows the customer name and address to be selected based on the CustomerID combo box (or Customer ID in Microsoft Access 2.0 and earlier). By changing the CustomerID in the Orders table, the related Customers record is changed, and the corresponding fields are updated on the form.

REFERENCES

For more information about AutoLookup queries, type "AutoLookup" in the Office Assistant, click Search, and then click to view "About AutoLookup queries that enter data automatically."

For more information about referencing controls on a form, type "Identifiers" in the Office Assistant, click Search, and then click to view "Use values in expresions."

For more information about using the Control Wizard, type "control wizard find record" in the Office Assistant, click Search, and then click to view "Find a record by selecting a value from a list."


Additional query words: Auto lookup forms text box move
Keywords : FmsCmbo kbusage kbfaq
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 4, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.