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

   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
				

Back to the top

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 by using the Control Wizard. (To use the Control Wizard, make sure that the Control Wizards button is pressed in on the toolbox before you create the combo box.) In the Control Wizard dialog box, follow these steps:
   
   
   1. Click the "Find a record on my form based on the value I selected in my combo box" button, and then click Next.
   2. Include the ProductID and ProductName fields, and then click Next.
   3. 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.)

Back to the top

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: Type a space between Product and ID in the ProductID field (Product ID) 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.

Back to the top

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
   						

Back to the top

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]
   						

Back to the top

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: Type a space between Product and ID in the ProductID field (Product ID) 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.
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
   						

   
   

Back to the top

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. A primary key is not necessary. Also, you should add no records to this table.
2. 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
   						

   
   
3. Create a form based on qryProductSelect, and add all fields. You must add at least the ProductID field.
4. 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: 1
         ColumnWidths: 2"
         BoundColumn: 1
   						

   
   

Back to the top