Change Combo Box search order

[Guest]

I have a membership database and I use combo-boxes to find member names for
various different tasks. I have set up an option to "sort" the data by
Member name or Membership Number, but I have done this using two different
queries which have different columns and sort orders.

What I would like, for ease of admin, is to create one query that has the
correct fields and "hide" the columns I don't need using the column width
setting. This works, but I would like the sort order to change as well so
they are sorted alphabetically when in name order and numerically when
ordered by Membership Number.

I am currently using a Check Box to change the data in the combo box and
setting the RowSource depending on whether the box is checked or not.

thanks in advance for any help,

 

[RoyVidar]

I have a membership database and I use combo-boxes to find member
names for various different tasks. I have set up an option to
"sort" the data by Member name or Membership Number, but I have done
this using two different queries which have different columns and
sort orders.

What I would like, for ease of admin, is to create one query that has
the correct fields and "hide" the columns I don't need using the
column width setting. This works, but I would like the sort order
to change as well so they are sorted alphabetically when in name
order and numerically when ordered by Membership Number.

I am currently using a Check Box to change the data in the combo box
and setting the RowSource depending on whether the box is checked or
not.

thanks in advance for any help,

I think I'd use one combo for each, but here's a quick sample. Create a
litle sub to be called from both the on load or on current event of the
form, and the after update of your check box. Air code

Sub ToggleComboProperties(ByVal v_fCurrent as Boolean)

Const cstrSql as string = "SELECT no, [name] FROM members ORDER BY
"

With Me!cboTest
If v_fCurrent Then
.RowSource = cstrSql & "no"
.ColumnWidths = "0;1702"
Else
.RowSource = cstrSql & "[name]"
.ColumnWidths = "284;1418"
End If
End With

End Sub

Then call it for instance like this

ToggleComboProperties False

or

ToggleComboProperties Me!chkMyCheckbox.Value

 

[Guest]

Here is the code from a test form I put together recently. (Please forgive
the horrible naming conventions, it was only an experiment to see if the
concept worked). It allows a user to click a command button that changes
which column is bound, what the sort order is, and what is displayed. It may
be very close to what you are trying to do:

Option Compare Database
Option Explicit

Private Sub Combo0_AfterUpdate()
Dim rst As DAO.Recordset
Dim strCriteria As String

If Me.Combo0.BoundColumn = 1 Then
strCriteria = "[Activity] = '"
Else
strCriteria = "[DESCRIPTION] = '"
End If
strCriteria = strCriteria & Me.Combo0.Column(0) & "'"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub

Private Sub Command6_Click()
Dim strAct As String
Dim strDesc As String

strAct = "SELECT CISAttributeTable.ACTIVITY,
CISAttributeTable.DESCRIPTION " & _
"FROM CISAttributeTable ORDER BY Activity;"
strDesc = "SELECT CISAttributeTable.DESCRIPTION,
CISAttributeTable.ACTIVITY " & _
"FROM CISAttributeTable ORDER BY Description;"
If Me.Combo0.BoundColumn = 1 Then
Me.Command6.Caption = "Activity"
Me.Label1.Caption = "Description"
Me.Combo0.BoundColumn = 2
Me.Combo0.RowSource = strDesc
Me.Combo0.ColumnWidths = "2.5"";1.1"""
Else
Me.Command6.Caption = "Description"
Me.Label1.Caption = "Activity"
Me.Combo0.BoundColumn = 1
Me.Combo0.RowSource = strAct
Me.Combo0.ColumnWidths = "1.1"";2.5"""
End If
Me.Combo0.SetFocus
End Sub

 

[Guest]

Thanks for both responses, the trick is in using a SQL statement in the
"RowSource" and that works fine now!

thanks again

Here is the code from a test form I put together recently. (Please forgive
the horrible naming conventions, it was only an experiment to see if the
concept worked). It allows a user to click a command button that changes
which column is bound, what the sort order is, and what is displayed. It may
be very close to what you are trying to do:

Option Compare Database
Option Explicit

Private Sub Combo0_AfterUpdate()
Dim rst As DAO.Recordset
Dim strCriteria As String

If Me.Combo0.BoundColumn = 1 Then
strCriteria = "[Activity] = '"
Else
strCriteria = "[DESCRIPTION] = '"
End If
strCriteria = strCriteria & Me.Combo0.Column(0) & "'"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub

Private Sub Command6_Click()
Dim strAct As String
Dim strDesc As String

strAct = "SELECT CISAttributeTable.ACTIVITY,
CISAttributeTable.DESCRIPTION " & _
"FROM CISAttributeTable ORDER BY Activity;"
strDesc = "SELECT CISAttributeTable.DESCRIPTION,
CISAttributeTable.ACTIVITY " & _
"FROM CISAttributeTable ORDER BY Description;"
If Me.Combo0.BoundColumn = 1 Then
Me.Command6.Caption = "Activity"
Me.Label1.Caption = "Description"
Me.Combo0.BoundColumn = 2
Me.Combo0.RowSource = strDesc
Me.Combo0.ColumnWidths = "2.5"";1.1"""
Else
Me.Command6.Caption = "Description"
Me.Label1.Caption = "Activity"
Me.Combo0.BoundColumn = 1
Me.Combo0.RowSource = strAct
Me.Combo0.ColumnWidths = "1.1"";2.5"""
End If
Me.Combo0.SetFocus
End Sub

I have a membership database and I use combo-boxes to find member names for
various different tasks. I have set up an option to "sort" the data by
Member name or Membership Number, but I have done this using two different
queries which have different columns and sort orders.

What I would like, for ease of admin, is to create one query that has the
correct fields and "hide" the columns I don't need using the column width
setting. This works, but I would like the sort order to change as well so
they are sorted alphabetically when in name order and numerically when
ordered by Membership Number.

I am currently using a Check Box to change the data in the combo box and
setting the RowSource depending on whether the box is checked or not.

thanks in advance for any help,