A question regarding Access 2010 sorting incorrectly

[minigendo]

Greetings,

My apologies if this has been asked recently, but a search did not reveal it. My apologies also if this is the incorrect forum for this post. If so please let me know where it should go.

I recently inherited an Access 2010 data project to debug. I have quite a bit of experience with databases and programming, but very little with Access itself.

There's a bug in the application that I am unable to resolve. There is a complex split form, which contains a number of sub forms. The table which is visible in the form contains a number of columns that access claims aren't actually there. That is to say, they aren't present in the record source query access says it is using the for the form. From the evidence, it is clear that a join is going on somewhere, and these columns are being linked in by a key value present on the record source query. When I, in form view, sort the data by one of these "mystery columns" alphabetically, access performs the sort, but sorts by the value of the underlying key instead.

For example, if I had:



Person_key, Person name
5, Anderson
3, Thomas
1, McDonald

Sorting by person name would get me:

Person_key, Person name
1, McDonald
3, Thomas
5, Anderson

Does anyone have any suggestions on a) how I might convince access to sort by the Name instead of the key, or b) how these mystery columns got here in the first place? Barring that, can anyone suggest a place to read up on behavior similar to this?

My thanks in advance for any help anyone might be able to offer.

[June7]

Can you provide the project for analysis?

How are you accomplishing the 'sort'? With right click or ribbon shortcuts? Or with search box and code?

[minigendo]

The sort that I was performing involved clicking on the column header of the dataset when in form view and choosing to sort A-Z. Unfortunately, I can not provide the project. I have made some progress in diagnosing the issue though.

My inexperience with Access is really going to show here, but I discovered that the "mystery" columns I mentioned before are renamed according to the label attached to the field, regardless of which column serves as the source for the contents. With this revelation, I was able to discover how the field was being populated.

Essentially, there was a combo box which had a control source set to the key value, and a row source which performed a query to populate the box. Again, I'm an Access novice, so I'm a bit hazy on the connection between these. However, this seems likely to be the source of the problem. Unfortunately, I was unable to determine how to fix it.

Instead I chose a inelegant and inefficient work around. Since I knew where the table source of the column that I needed to sort was, I rewrote the forms row source to include a left join against the appropriate table and simply pulled in that column. Then I placed it in an invisible text field, and hid the original field which refused to sort properly. The result was a field identical in contents, which did sort as expected.

That said, I'd love to learn what the right way to do it was. Do you have any suggestions?

My thanks again.

[June7]

The combobox is to select value from the RowSource. The RowSource query doesn't populate combobox, selecting a row or just typing in the combobox populates the combobox. The combobox must be multi-column and set to display an alias value from the RowSource instead of the actual saved value.

You did the correct method to provide sort field if you want to use the shortcuts, however, you don't want to allow edits to the field. Set it is as Locked Yes, TabStop No.

You might need that combobox when creating new record so you can select related record value. Without knowing more about your project can't really say if this is correct, but seems likely.

Can't provide project why? Make copy, remove confidential data, run Compact & Repair, zip if large, attach to post or upload to fileshare site such as box.com and post link to file.

Might want to check some of these video tutorials http://datapigtechnologies.com/AccessMain.htm

[minigendo]

The combobox was multicolumn, and the key value was column 0 of that query, so that may explain why it seemed to be linked. If I could ask, how does one set it to display an alias value? Alternatively, where could I read more about that functionality?

Thank you for your advice with regards to locking down the new column, I have done so.

I didn't get rid of the combo box, just hid the column in the displayed split form datasheet. The previous worker on this particular access project had done the same thing several times. Not the cleanest solution, but it seemed to work.

I'm afraid that I can't share the project because it violates the SOPS of where I work. Even sanitized, it would probably still count as something which could get me fired.

Regardless, please let me thank you again for your willingness to discuss the issue with me.

[June7]

Review the tutorials about comboboxes at the site I referenced in the Access Forms: Control Basics section.

[minigendo]

I will. My thanks again for your help.