Sorting on the text value of a combobox in a continuous form

[WCStarks]

I have three tables relevant to this discussion:
Members
Events
Activities
Activities is formally related as a child of both Members and Events.

I create child Activities records with the Events Form and the embedded continuous Activities_Ev sub-form

The sub-form contains a Combo box (Member_Name), used to select a person in the Members table to whom this Events' Activities_Ev child record should link. It displays the name properly. Just so there is no question about how I am managing the linking, I also provided an OnClick event procedure for the combo box, to take the Member's ID, bound column(0), and assign it to the linking field in the child record, to properly associate the child record with the members parent record, as below.



Private Sub Member_Name_OnClick()
[Member_ID] = [Member_Name].Column(0)
End Sub

That all works fine. I found this is a handy feature, which I recently learned how to do. I now use it in several similar places in the database.

But, when I attempt to sort on the combobox, it sorts by the bound column(0), not the combo box column(1), which contains the member's name, and which is displayed in the combobox.

I tried setting the sub-form Order By property to [Member_Name].Column(1), but that it not understood by Access. I also tried quotes around that command. It did not cause an error, and it did change the sort order. However, it is not alphabetical, just some apparently other random sort order.

How do I get the sub-form to sort by the text value in the combobox instead of the bound column?

[June7]

Can include the Members table in the form RecordSource (join type "Include all records from Activities ...). Bind textbox to the member name field and set it Locked Yes to prevent editing. Reference that field to sort by.

[WCStarks]

I already had the Members table as a data source for the Activities_Ev Sub-form. After reading your response, I added the [Members].[Mem_Name] field to the sub-form and asked it to sort on Mem_Name instead of the Combobox. That was simple, and it works I'll just keep it hidden. I didn't need to do anything else. It seems a little kudgy, though, keeping duplicate data. I suppose the combobox is really only storing a pointer, and not the actual text.

Incidentally, I wasn't sure how to bind the combobox to the name field, without loosing the members_ID primary key designation.

Thanks again for your help.

[Micron]

Thought I'd throw this in since I came across it here https://www.pcreview.co.uk/threads/v...bobox.3963288/

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