ACC: How to Sort Form Records Using Field Names from Combo Box
ID: Q153579
|
The information in this article applies to:
-
Microsoft Access versions 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
This article describes how to sort the values on a form based on field
names that you select from a combo box.
MORE INFORMATION
To use this method, you create a form based on a table or query. You then
create a combo box whose row source is a value list containing the field
names to be used for sorting the records on the form. The record source for
the form is an SQL statement that sorts the data on the form based on the
values selected in the combo box. The AfterUpdate event of the combo box
contains a procedure that requeries the record source of the form each time
that you select a different field to sort by. This example uses the
Customers table from the Northwind sample database.
- Open the sample database Northwind.mdb (or Nwind.mdb for Access 2.0).
- Create a new form based on the Customers table in Design view. If the
field list is not display, on the View menu, click Field List, and then
drag all of the fields from the field list to the form. Set the form's
DefaultView property to Continuous Forms.
- Use one of the following two methods:
Method 1:
- Create a combo box, and set the Name property to cboLookup.
- Set the RowSourceType property to Field List. This will show all
fields.
- Set the RowSource property to the same table or query that the form
is based on.
Method 2:
- Change the RecordSource property of the form to:
Select * From Customers Order By '["& Me!cboLookup &"]';
- Use the Combo Box Control Wizard to add a combo box to the form.
When prompted, select "I will type in values that I want." Select a
single column and type in the field names to sort on. Accept the
defaults for the remainder of the wizard. Name the combo box
cboLookup.
NOTE: Steps 4 through 6 apply to both methods.
- Add the following event procedure to the AfterUpdate event of the combo
box.
NOTE: In the following sample code, an underscore (_) at the end of a
line is used as a line-continuation character. Remove the underscore
from the end of the line when re-creating this code.
Sub cboLookup_AfterUpdate()
Me.RecordSource = "Select * From Customers ORDER By _
[" & Me!cboLookup & "];"
End Sub
- Save the form and open it in Form view.
- Click the combo box and select a field name.
Note that the data in the form is sorted based on the field name
that you select from the combo box.
REFERENCES
For more information about the AfterUpdate event, search the Help Index
for "AfterUpdate," or ask the Microsoft Access 97 Office Assistant.
Additional query words:
Sort Field Names Combo Box howto
Keywords : FmsCmbo
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto