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

• Microsoft Access 2000

SUMMARY

This article shows you four methods of moving to a specific record based on a combo box selection. The methods are as follows:

• In the AfterUpdate property of the combo box, call a macro that uses the FindRecord action.



• In the AfterUpdate property of the combo box, call a macro using the ApplyFilter action.



• 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.



• Base the form on a query that joins two tables, and use the AutoLookup technique to bind the combo box to the field that controls the join.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

   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 

Method 1

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.

Method 2

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



2. Set the Filter property for the frmComboTest form to the following:




[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 

Method 3

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 

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

Method 4

1. Create a table called tblProductSelect that has a single field, ProductID. Set the field's 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 re-create it as a combo box, as follows:




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

REFERENCES

For more information about AutoLookup queries, click Microsoft Access Help on the Help menu, type "AutoLookup" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about referencing controls, click Microsoft Access Help on the Help menu, type "referencing controls" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about using the control wizard to find a record, click Microsoft Access Help on the Help menu, type "Find a record by selecting a value from a list" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about how to use the Filter By Form technique, click Microsoft Access Help on the Help menu, type "Filter By Form" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: Auto lookup forms text box move

Keywords          : kbusage kbdta FmsCmbo 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 13, 1999