A
Adam Milligan
I am using Access 2007 on WIndows Vista. I have a subform with a single
combo box in it. The combo box's row source has two columns, the ID field
and then a concatenated text field that shows a person's name followed by
their company. What I woudl like to do is give the option to the user to
have the combo box display the person's company followed by their name. With
the following code, I have succeeded in chaging the combo box back and forth:
Private Sub cmdByContact_Click()
Me.cboReferralSource_FK.RowSource = "SELECT
tblReferralSource.intReferralSourceID, [txtReferralSourceLast] & ', ' &
[txtReferralSourceFirst] & ' - ' & [txtReferralSourceOrganization] AS
ReferralSource FROM tblReferralSourceOrganization INNER JOIN
tblReferralSource ON
tblReferralSourceOrganization.intReferralSourceOrganizationID =
tblReferralSource.intReferralSourceOrganization_FK ORDER BY
[txtReferralSourceLast] & ', ' & [txtReferralSourceFirst] & ' - ' &
[txtReferralSourceOrganization];"
Me.cboReferralSource_FK.Requery
End Sub
Private Sub cmdByOrg_Click()
Me.cboReferralSource_FK.RowSource = "SELECT
tblReferralSource.intReferralSourceID, [txtReferralSourceOrganization] & ' -
' & [txtReferralSourceLast] & ', ' & [txtReferralSourceFirst] AS
ReferralSource FROM tblReferralSourceOrganization INNER JOIN
tblReferralSource ON
tblReferralSourceOrganization.intReferralSourceOrganizationID =
tblReferralSource.intReferralSourceOrganization_FK ORDER BY
[txtReferralSourceOrganization] & ' - ' & [txtReferralSourceLast] & ', ' &
[txtReferralSourceFirst];"
Me.cboReferralSource_FK.Requery
End Sub
That is all well and good, but what I need to do now is make the existing
records in the subform fall into alphabetical order by the new text displayed
in the combo box. I am certain it is possible because I can just right click
the combo box and select "Sort AZ" and it does what I want. And in my
experience so far, ANYHTHING you can do by right clicking, you can do with
code. I Just can't seem to find the magic Me.DoWhatTheHeck.IWant command.
Any help would be appreciated.
Thanks
Adam
combo box in it. The combo box's row source has two columns, the ID field
and then a concatenated text field that shows a person's name followed by
their company. What I woudl like to do is give the option to the user to
have the combo box display the person's company followed by their name. With
the following code, I have succeeded in chaging the combo box back and forth:
Private Sub cmdByContact_Click()
Me.cboReferralSource_FK.RowSource = "SELECT
tblReferralSource.intReferralSourceID, [txtReferralSourceLast] & ', ' &
[txtReferralSourceFirst] & ' - ' & [txtReferralSourceOrganization] AS
ReferralSource FROM tblReferralSourceOrganization INNER JOIN
tblReferralSource ON
tblReferralSourceOrganization.intReferralSourceOrganizationID =
tblReferralSource.intReferralSourceOrganization_FK ORDER BY
[txtReferralSourceLast] & ', ' & [txtReferralSourceFirst] & ' - ' &
[txtReferralSourceOrganization];"
Me.cboReferralSource_FK.Requery
End Sub
Private Sub cmdByOrg_Click()
Me.cboReferralSource_FK.RowSource = "SELECT
tblReferralSource.intReferralSourceID, [txtReferralSourceOrganization] & ' -
' & [txtReferralSourceLast] & ', ' & [txtReferralSourceFirst] AS
ReferralSource FROM tblReferralSourceOrganization INNER JOIN
tblReferralSource ON
tblReferralSourceOrganization.intReferralSourceOrganizationID =
tblReferralSource.intReferralSourceOrganization_FK ORDER BY
[txtReferralSourceOrganization] & ' - ' & [txtReferralSourceLast] & ', ' &
[txtReferralSourceFirst];"
Me.cboReferralSource_FK.Requery
End Sub
That is all well and good, but what I need to do now is make the existing
records in the subform fall into alphabetical order by the new text displayed
in the combo box. I am certain it is possible because I can just right click
the combo box and select "Sort AZ" and it does what I want. And in my
experience so far, ANYHTHING you can do by right clicking, you can do with
code. I Just can't seem to find the magic Me.DoWhatTheHeck.IWant command.
Any help would be appreciated.
Thanks
Adam