Sorting form data

[judith]

I have a continuous form displaying a series of data items and have code
attached to the double click event of each field
' sort field
DoCmd.RunCommand acCmdSortAscending
But two of my fields are combo boxes and the bound field is a code rather
than the data displayed. I created a new field
=DLookUp("[unit]","CSCSUnit","[CSCSUnitID]= " & [unitId]) and changed the
double click code for the combo box field to
' sort field
DoCmd.GoToControl "unitSort"
DoCmd.RunCommand acCmdSortAscending
Me.UnitID.SetFocus
but I just get an error that the sort command is not available. Any
suggestions please

 

[Allen Browne]

One way to solve this is to create a query, using both the table you really
need the data from and also the CSCSUnit table. You can then use this query
as the RecordSource for your form, so it has the [unit] field in it, and so
you can sort by this field.

1. Create a query using the 2 tables.
In the upper pane of query design, double-click the line joining the 2
tables.
Access pops up a dialog offering 3 choices.
Choose the one that says:
All records from [YourMainTableName], and any matches from CSCSUnit.

2. In the sorting row, set up the sorting you want.
Save the query.

3. Open the form in design view.
Set its RecordSource property to the name of this query.

You can now sort on the fields you need.

 

[judith]

Thanks Allen. I will put the lookup fields in a joined table query. The combo
boxes will need to remain on the form as they are input fields. I like my
users to be able to double click on fields to sort dynamically so will leave
the double click code but it will now direct to a hidden query field for
sorting and not the generated field which i guess was what caused the
problem. Thanks

One way to solve this is to create a query, using both the table you really
need the data from and also the CSCSUnit table. You can then use this query
as the RecordSource for your form, so it has the [unit] field in it, and so
you can sort by this field.

1. Create a query using the 2 tables.
In the upper pane of query design, double-click the line joining the 2
tables.
Access pops up a dialog offering 3 choices.
Choose the one that says:
All records from [YourMainTableName], and any matches from CSCSUnit.

2. In the sorting row, set up the sorting you want.
Save the query.

3. Open the form in design view.
Set its RecordSource property to the name of this query.

You can now sort on the fields you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a continuous form displaying a series of data items and have code
attached to the double click event of each field
' sort field
DoCmd.RunCommand acCmdSortAscending
But two of my fields are combo boxes and the bound field is a code
rather than the data displayed. I created a new field
=DLookUp("[unit]","CSCSUnit","[CSCSUnitID]= " & [unitId]) and changed
the double click code for the combo box field to
' sort field
DoCmd.GoToControl "unitSort"
DoCmd.RunCommand acCmdSortAscending
Me.UnitID.SetFocus
but I just get an error that the sort command is not available. Any
suggestions please