VBA - Order by second column of combobox

[matchorno]

I have a continuous form with column headers. The column headers are command
buttons that you can click to order the form by that particular field. One
of the fields is a combobox. It has two columns - one hidden bound column
(autonumber) and one not hidden. I want to sort by the second, visible
column.

Right now the code looks like this:

Private Sub cmdSortByJudge_Click()
Me.OrderBy = "[JudgeID]"
Me.OrderByOn = True
End Sub

However, that sorts the form by the first autonumber column of the combobox.
I want to sort it by the second column (JudgesFullName). I can't get it to
work.

I've tried the following:

me.orderBy = "[JudgeID].Column(1)"

as well as:

me.orderBy = "[JudgesFullName]"

But neither works. I've scoured the internet forums with no luck. Any
suggestions are appreciated. Thanks!

 

[Dorian]

Try this:

Private Sub cmdSortByJudge_Click()
Me.OrderBy = "[JudgeID].column(1)"
Me.OrderByOn = True
End Sub

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

I have a continuous form with column headers. The column headers are command
buttons that you can click to order the form by that particular field. One
of the fields is a combobox. It has two columns - one hidden bound column
(autonumber) and one not hidden. I want to sort by the second, visible
column.

Right now the code looks like this:

Private Sub cmdSortByJudge_Click()
Me.OrderBy = "[JudgeID]"
Me.OrderByOn = True
End Sub

However, that sorts the form by the first autonumber column of the combobox.
I want to sort it by the second column (JudgesFullName). I can't get it to
work.

I've tried the following:

me.orderBy = "[JudgeID].Column(1)"

as well as:

me.orderBy = "[JudgesFullName]"

But neither works. I've scoured the internet forums with no luck. Any
suggestions are appreciated. Thanks!

 

[John W. Vinson]

I have a continuous form with column headers. The column headers are command
buttons that you can click to order the form by that particular field. One
of the fields is a combobox. It has two columns - one hidden bound column
(autonumber) and one not hidden. I want to sort by the second, visible
column.

Right now the code looks like this:

Private Sub cmdSortByJudge_Click()
Me.OrderBy = "[JudgeID]"
Me.OrderByOn = True
End Sub

However, that sorts the form by the first autonumber column of the combobox.
I want to sort it by the second column (JudgesFullName). I can't get it to
work.

I've tried the following:

me.orderBy = "[JudgeID].Column(1)"

as well as:

me.orderBy = "[JudgesFullName]"

But neither works. I've scoured the internet forums with no luck. Any
suggestions are appreciated. Thanks!

Since the judge's full name isn't *IN* the form's Recordsource, you can't sort
by it using the Form's OrderBy property. The information simply isn't there!

You may want to try basing the Form on a Query joining to the table of judges,
and including the judge's name. You will not be able to sort by a combo box.

 

[matchorno]

Since the judge's full name isn't *IN* the form's Recordsource, you can't
sort

by it using the Form's OrderBy property. The information simply isn't there!

You may want to try basing the Form on a Query joining to the table of judges,
and including the judge's name. You will not be able to sort by a combo box.

Thanks for the info. I was afraid of that. I was trying to avoid that. I
had it that way before, but I noticed that if one of the records did not have
a judge assigned (it's not a required field), the record doesn't show up in
the list. Maybe I'm doing something wrong there?

Scenario:
I have two tables: One called tblAppeals that has fields like
[DefendantFname], [DefendantLName], etc. and the foreign key [JudgeId]. The
other related table is called tblJudges. It has fields like [JudgeFname],
[JudgeLname] and the primary key [JudgeId]. The continuous form that I'm
trying to sort is a search form to search for records. Right now the form is
only based on the table: tblAppeals. If I try to base it on a query that
contains both tables tblAppeals and tblJudges...if a record is in tblAppeals
but does not contain a related record in tblJudges (i.e., the foreign key
[JudgeID] in tblAppeals is null), then the record does not show up on the
search form. not sure why?

 

[matchorno]

:
I did try that already...

Try this:

Private Sub cmdSortByJudge_Click()
Me.OrderBy = "[JudgeID].column(1)"
Me.OrderByOn = True
End Sub

I've tried the following:

me.orderBy = "[JudgeID].Column(1)"

as well as:

me.orderBy = "[JudgesFullName]"

But neither works. I've scoured the internet forums with no luck. Any
suggestions are appreciated. Thanks!

 

[matchorno]

I just thought of something...
If I click in the combobox field, then go to the toolbar and click sort, it
sorts it by the second, visible column as desired. So if I can do this
manually, I figured there must be a way to program it using "doMenuItem". It
seems a dirty way of doing it, but I tried it and it does indeed work:

Private Sub cmdSortByJudge_Click()
Me.JudgeID.SetFocus '--set focus to the field you want to sort by
DoCmd.DoMenuItem A_FORMBAR, A_RECORDSMENU, 3, 0, A_MENU_VER20
End Sub

 

[John W. Vinson]

Thanks for the info. I was afraid of that. I was trying to avoid that. I
had it that way before, but I noticed that if one of the records did not have
a judge assigned (it's not a required field), the record doesn't show up in
the list. Maybe I'm doing something wrong there?

What you would need to do is to use a Left Join in the query - select the join
line and use "Show All Records in tblAppeals and matching records in
tblJudges".

Scenario:
I have two tables: One called tblAppeals that has fields like
[DefendantFname], [DefendantLName], etc. and the foreign key [JudgeId]. The
other related table is called tblJudges. It has fields like [JudgeFname],
[JudgeLname] and the primary key [JudgeId]. The continuous form that I'm
trying to sort is a search form to search for records. Right now the form is
only based on the table: tblAppeals. If I try to base it on a query that
contains both tables tblAppeals and tblJudges...if a record is in tblAppeals
but does not contain a related record in tblJudges (i.e., the foreign key
[JudgeID] in tblAppeals is null), then the record does not show up on the
search form. not sure why?

The default Inner Join shows records only where there is a match in both
tables.

An alternative would be to use a calculated field in the query

JudgeName: DLookUp("[JudgeLName]", "[tblJudges]", "[JudgeID] = " & [JudgeID])

in the Query and sort by it. It'll be less efficient than the Outer Join but
should work.