VBA - Order by second column of combobox

On Wed, 3 Feb 2010 09:21:03 -0800, matchorno
wrote:

>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.
 >> Stay informed about: VBA - Order by second column of combobox 

> 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.
> --
>
> John W. Vinson [MVP]

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?
 >> Stay informed about: VBA - Order by second column of combobox 

"Dorian" wrote:
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!
 >> Stay informed about: VBA - Order by second column of combobox 

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" wrote:
>
> 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.
> --
>
> John W. Vinson [MVP]
> .
>
 >> Stay informed about: VBA - Order by second column of combobox 

On Thu, 4 Feb 2010 06:13:01 -0800, matchorno
wrote:

>> 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.
>> --
>>
>> John W. Vinson [MVP]
>
>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.
 >> Stay informed about: VBA - Order by second column of combobox