ACC: Combo Box Wizard Does Not Allow Selection of Table/Query
ID: Q133087
|
The information in this article applies to:
-
Microsoft Access versions 7.0, 97
SYMPTOMS
Moderate: Requires basic macro, coding, and interoperability skills.
When you select the "Find a record on my form based on the value I
selected in my combo box" option in the Combo Box Wizard dialog box, the
Combo Box Wizard does not give you the option to select a table or query
as your data source.
CAUSE
When you select the option to find a record based on your selection in
the combo box, the Combo Box Wizard uses the data from the RecordSource
property of the form as the RowSource property for the combo box.
RESOLUTION
To work around this behavior, let the Combo Box Wizard build the combo
box, and then, after the Combo Box Wizard finishes building the combo box,
manually change the RowSource property of the combo box to the table or
query that you want. For an example of how to do so, follow the steps in
the "Steps to Reproduce Behavior" section.
MORE INFORMATIONSteps to Reproduce Behavior
CAUTION: Following the steps in this example will modify the sample
database Northwind.mdb. You may want to back up the Northwind.mdb file or
perform these steps on a copy of the Northwind database.
- Open the sample database Northwind.mdb.
- Create the following new query, add the Employees table, and save it
as qryEmployeeList:
Query: qryEmployeeList
-------------------------------------------
Type: Select Query
Field: EmployeeID
Table: Employees
Field: Name:[LastName] & ", " & [FirstName]
Table: Employees
Sort: Ascending
- Open the Employees form in Design view.
- If it is not turned on, click the Control Wizards button in the
toolbox to turn on the Control Wizards.
- Click the Combo Box button in the toolbox, and add a combo box to
the header section of the Employees form.
- Click the "Find a record on my form based on the value I selected in
my combo box" option in Combo Box Wizard, and then click Next. Note
that the Combo Box Wizard does not give you the option to select a
table or query as the data source for the combo box. Instead, you are
presented with a list of all fields available from the RecordSource
property of the form.
- In the Control Wizard dialog box, move the EmployeeId and LastName
fields from the Available Fields box to the Selected Fields Box,
and then click Next. In the next window, "Hide key column" is
checked, and the first column containing the EmployeeID is hidden.
Click next.
- In the "What label would you like for your combo box?" box, type
Find Employee, and then click Finish.
- Open the form in Form view. Note that you can find employees by
making selections from the combo box. To use the qryEmployeeList
query you created in step 2 above, you must change the form's
RowSource property.
- Open the Employees form in Design view.
- Using the right mouse button, click the combo box you added to the
Employees form header section, and then click Properties on the menu
that appears.
- Set the following properties for the combo box:
RowSourceType: Table/Query
RowSource: qryEmployeeList
ColumnCount: 2
ColumnWidths: 0";2"
BoundColumn: 1
- Open the form in Form view. The Find Employees combo box now
displays the employees' last names and first names in alphabetical
order. Through customizing the combo box, the Combo Box Wizard has
added the following code to AfterUpdate property of the combo box:
Sub Combo43_AfterUpdate()
'Find the record that matches the control.
Me.RecordsetCone.FindFirst "[EmployeeID] = " & Me![Combo43]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
NOTE: Your Combo box control may have a name other than Combo43.
To further synchronize the form and combo box so that the combo box
always displays the name of the employee on the current record when
you scroll through the records, add the following code to the form's
OnCurrent property event procedure:
Private Sub Form_Current()
Me![Combo43] = Me![EmployeeID]
End Sub
where [Combo43] is the name of your combo box.
Open the form in Form view. Note that the combo box now displays the
name of the employee on the current record. You can still use the
combo box to find a specific employee.
REFERENCES
For more information about combo boxes, search the Help Index for "combo
boxes," or ask the Microsoft Access 97 Office Assistant.
Keywords : FmsCmbob
Version : 7.0 97
Platform : WINDOWS
Issue type : kbprb
|