Four ways to move to a record from a Combo Box selection (287658)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

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

1. Use the AutoForm: Columnar Wizard to create a new form that is based on the Products table, and then save the form as frmComboTest.
2. Use the Combo Box Wizard to add an unbound combo box. In the Combo Box Wizard, following these steps:
   
   
   1. Click the Find a record on my form based on the value I selected in my combo box option, and then click Next.
   2. Include the ProductID and ProductName fields, and then click Next.
   3. Click Finish.
   The Combo Box Wizard creates an event procedure similar to the following:

   Private Sub Combo0_AfterUpdate()
       ' Find the record that matches the control.
       Dim rs As Object
   
       Set rs = Me.Recordset.Clone
       rs.FindFirst "[ProductID] = " & Str(Nz(Me![Combo20], 0))
       If Not rs.EOF Then Me.Bookmark = rs.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 for the product that you selected.

Method 2

1. Use the AutoForm: Columnar Wizard to create a new form that is based on the Products table, and save the form as frmComboTest2.
2. Set the Filter property of the frmComboTest2 form as follows:

   [ProductName] = Forms![frmComboTest2]![cboLookup]

3. Add an unbound combo box named cboLookup, and then set the properties of the control as follows:

      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 named mcrLocateProduct:

      Action
      --------------------------------------
      SetValue
      Requery
   
      mcrLocateProduct Actions
      --------------------------------------
      SetValue
      Item: Forms![frmComboTest2].FilterOn
      Expression: True
   						

   When you open the frmComboTest2 form and select a product name from cboLookup control, the filter is set to that value.

Method 3

1. Create a new form that is not based on any table or query and save it as frmMain. Then add a combo box and set its properties as follows:

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

2. Use the AutoForm: Tabular Wizard to create a second form that is based on the Products table, set the DefaultView property of the form to Single Form, and then save the form as frmSub.
3. Use the frmSub form to create a subform control on the frmMain form by dragging the frmSub form from the Database window and dropping it into the detail section of the frmMain form.
4. Set the subform control properties as follows:

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

   By changing the value in cboLookup control, Access ensures that the records in the subform match the combo box.
   
   The Orders form in Northwind.mdb illustrates this method. The Order Details subform is related by the LinkMasterFields and LinkChildFields properties.

Method 4

1. Create a table named tblProductSelect that has a single field, ProductID. Set the Data Type of the field to Number and set the Field Size to Long Integer. A primary key is not necessary. Do not add records to this table.
2. Create the following query named qryProductSelect that is 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
   
      Field: <any other fields you are interested in>
      TableName: Products
   					

3. Use the AutoForm: Columnar Wizard to create a form that is based on the qryProductSelect query, and then view the form in Form view.
4. Right-click the text box control for ProductID, point to Change To, click Combo Box, and then make the following property assignments for this combo box:

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

5. Save the form as frmComboTest3, and then run the form.