Sort by problem

  • Thread starter Thread starter Russ
  • Start date Start date
R

Russ

I've got a report that I would like the user to be able to choose the
sort order for records by selecting the field name using a combo box
in a form. I believe I could do that if I wanted to sort by a record,
but by a fieldname has got me stumped.

Using a record, I believe I would base the report on a query with
similar criteria to select whatever was chosen in the form combo box
like this: [Forms]![ChooseDutyVolList]![MainCatName]

Can I create a combo box that displays field names instead of records?
 
No.

In your query that feeds the report you can create a SortOption field in
several ways.
The easiest is a calculated field like this --
SortOption : IIF([Forms]![YourForm]![CheckBox] = -1, [Field1], [Field1])

You can nest more IIFs or use Option Group instead of check boxes.

You can also change from ascending to descending by having a second field
SortOption_1. A check box to control which field outputs data or null.
Then in the report have both fields in the one Sorting and Grouping but one
ascending and other descending. The one that has an output will result in
the sort as null will not affect sorting.


Russ said:
I've got a report that I would like the user to be able to choose the
sort order for records by selecting the field name using a combo box
in a form. I believe I could do that if I wanted to sort by a record,
but by a fieldname has got me stumped.

Using a record, I believe I would base the report on a query with
similar criteria to select whatever was chosen in the form combo box
like this: [Forms]![ChooseDutyVolList]![MainCatName]

Can I create a combo box that displays field names instead of records?
Click to expand...
 
You can populate the combo with a list of fields in two ways.

Option 1 is to set two properties of the combo box as follows:

RowSourceType = Field List
Row Source = TableName

This will fill the combo with all of the fields.

Option 2 is to use the following code:

Private Sub FillCombo
Dim rst As Recordset
Dim fld As Field

Set rst = Me.Recordset
For Each fld In rst.Fields
Combo1.AddItem (fld.Name)
Next
End Sub

Option 2 gives you full control over which fields get added to the list so
you could exclude fields that you might not want such as primary or foreign
keys.

Ray
 
Thanks guys for the responses. I'll give it a go tomorrow.

Russ

You can populate the combo with a list of fields in two ways.

Option 1 is to set two properties of the combo box as follows:

RowSourceType = Field List
Row Source = TableName

This will fill the combo with all of the fields.

Option 2 is to use the following code:

Private Sub FillCombo
Dim rst As Recordset
Dim fld As Field

Set rst = Me.Recordset
For Each fld In rst.Fields
Combo1.AddItem (fld.Name)
Next
End Sub

Option 2 gives you full control over which fields get added to the list so
you could exclude fields that you might not want such as primary or foreign
keys.

Ray

Russ said:
I've got a report that I would like the user to be able to choose the
sort order for records by selecting the field name using a combo box
in a form. I believe I could do that if I wanted to sort by a record,
but by a fieldname has got me stumped.

Using a record, I believe I would base the report on a query with
similar criteria to select whatever was chosen in the form combo box
like this: [Forms]![ChooseDutyVolList]![MainCatName]

Can I create a combo box that displays field names instead of records?
Click to expand...
Click to expand...
 
Ray,

I've got a couple of problems.

The fields I want to choose from are in separate tables so I created a
make table query to choose the fields I wanted. Hope that will work.
Also, if I use the Option 2 as you suggest, where should I put the
code?

You can populate the combo with a list of fields in two ways.

Option 1 is to set two properties of the combo box as follows:

RowSourceType = Field List
Row Source = TableName

This will fill the combo with all of the fields.

Option 2 is to use the following code:

Private Sub FillCombo
Dim rst As Recordset
Dim fld As Field

Set rst = Me.Recordset
For Each fld In rst.Fields
Combo1.AddItem (fld.Name)
Next
End Sub

Option 2 gives you full control over which fields get added to the list so
you could exclude fields that you might not want such as primary or foreign
keys.

Ray

Russ said:
I've got a report that I would like the user to be able to choose the
sort order for records by selecting the field name using a combo box
in a form. I believe I could do that if I wanted to sort by a record,
but by a fieldname has got me stumped.

Using a record, I believe I would base the report on a query with
similar criteria to select whatever was chosen in the form combo box
like this: [Forms]![ChooseDutyVolList]![MainCatName]

Can I create a combo box that displays field names instead of records?
Click to expand...
Click to expand...
 
Hi,

Not sure what you mean by "make table query". I haven't tried it but I
would think that using the "Field List" option would work just fine getting
it's list of fields from a multi-table. It basically does the same thing as
the option 2 code...it just does it automatically.

For option 2, I would put the code in the form load event. Or rather,
create a separate sub to load the combo and then call that from the form load
event.

For example, if I have a combo box (or list or whatever) called
cboCustomerName, I typically create a sub called cboCustomerNameRequery or
cboCustomerNameFill, and then call that sub from the form load event. That
not only cleans up the form load event, but also allows you to call it from
other locations when appropriate.

Ray

Russ said:
Ray,

I've got a couple of problems.

The fields I want to choose from are in separate tables so I created a
make table query to choose the fields I wanted. Hope that will work.
Also, if I use the Option 2 as you suggest, where should I put the
code?

You can populate the combo with a list of fields in two ways.

Option 1 is to set two properties of the combo box as follows:

RowSourceType = Field List
Row Source = TableName

This will fill the combo with all of the fields.

Option 2 is to use the following code:

Private Sub FillCombo
Dim rst As Recordset
Dim fld As Field

Set rst = Me.Recordset
For Each fld In rst.Fields
Combo1.AddItem (fld.Name)
Next
End Sub

Option 2 gives you full control over which fields get added to the list so
you could exclude fields that you might not want such as primary or foreign
keys.

Ray

Russ said:
I've got a report that I would like the user to be able to choose the
sort order for records by selecting the field name using a combo box
in a form. I believe I could do that if I wanted to sort by a record,
but by a fieldname has got me stumped.

Using a record, I believe I would base the report on a query with
similar criteria to select whatever was chosen in the form combo box
like this: [Forms]![ChooseDutyVolList]![MainCatName]

Can I create a combo box that displays field names instead of records?
Click to expand...
Click to expand...
Click to expand...
 
Access 2k. A make-table query is just another of the action queries
such as delete query, update query, append query. It creates an empty
table from the query.

Hi,

Not sure what you mean by "make table query". I haven't tried it but I
would think that using the "Field List" option would work just fine getting
it's list of fields from a multi-table. It basically does the same thing as
the option 2 code...it just does it automatically.

For option 2, I would put the code in the form load event. Or rather,
create a separate sub to load the combo and then call that from the form load
event.

For example, if I have a combo box (or list or whatever) called
cboCustomerName, I typically create a sub called cboCustomerNameRequery or
cboCustomerNameFill, and then call that sub from the form load event. That
not only cleans up the form load event, but also allows you to call it from
other locations when appropriate.

Ray

Russ said:
Ray,

I've got a couple of problems.

The fields I want to choose from are in separate tables so I created a
make table query to choose the fields I wanted. Hope that will work.
Also, if I use the Option 2 as you suggest, where should I put the
code?

You can populate the combo with a list of fields in two ways.

Option 1 is to set two properties of the combo box as follows:

RowSourceType = Field List
Row Source = TableName

This will fill the combo with all of the fields.

Option 2 is to use the following code:

Private Sub FillCombo
Dim rst As Recordset
Dim fld As Field

Set rst = Me.Recordset
For Each fld In rst.Fields
Combo1.AddItem (fld.Name)
Next
End Sub

Option 2 gives you full control over which fields get added to the list so
you could exclude fields that you might not want such as primary or foreign
keys.

Ray

:

I've got a report that I would like the user to be able to choose the
sort order for records by selecting the field name using a combo box
in a form. I believe I could do that if I wanted to sort by a record,
but by a fieldname has got me stumped.

Using a record, I believe I would base the report on a query with
similar criteria to select whatever was chosen in the form combo box
like this: [Forms]![ChooseDutyVolList]![MainCatName]

Can I create a combo box that displays field names instead of records?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top