Cannot edit records after sort

Hi,

I have an MS Access 2010 form (Datasheet View) with two fields on it, one is a combo-box which is set as control source of the PK of the table and the other is a description.

I use a combo-box with a query so the ID is used as the key but the company name is shown for selection in the combo.

This works fine until the form is sorted, then none of the records can be changed with a message stating... "This record is not updateable"

Why? and how do I make it updateable after a user has perfomed a sort?

Thanks,

1DMF.

Not sure if I understand your question correctly, but let me guess. You are using a recordset to populate a combo box, and subsequently changing the "Sorted" property to "True". This will not work. You must use a sorted query to populate the combo box, so that the query and the combo box are in the same order.

J.A. Coutts

Hi couttsj,

Not sure we are using same terminology. Remember I am talking MS Access!

I am not using a recordset object, if that's what you mean?

I have a form, bound to a table, on the form is a combo box, which is populated by a query so that the text shows column 2 (company name) but the value is bound to column 1 (PK).

However, I think I understand what you are trying to say...

When they sort the combobox , they are not sorting the actual records on the form! They are merley sorting the order of the query bound to the combobox so now the company names / PK is not aligned to the order of the records on the form.

Is that what you are implying?

If so how do you create a form with a combobox and allow the records to be sorted, or can't you?

I have made the combobox load the data in order ASC, but does this mean if the user wanted to flip the order to DESC to get quick access to the bottom of the list , this isn't possible?

Why when the combobox is sorted, it doesn't move the order of the records on the form, after all the combo box is bound to the PK of the table?

It's like it's sorting the order of column 2 (shown as the text) but not column 1 (the PK and value bound)?

How do I make this possible?

Thanks,
1DMF.

My statements are based upon my experience with VB6, but VB Script and VB for Access basically use the same engine. A combo box uses a simple string for it's contents, with each element in the list separated by a NULL character (Chr$(0)). When you select an element from the list, the index value becomes a pointer into the cached read from the database. When you perform a database update, it requires another trip to the database, so if you change the order the update is not allowed to be performed. This is a limitation of a bound control. The only way I know of to get around the problem is to perform a refresh on the cache by executing a read in the order you desire.

In VB6, I generally steer clear of bound controls, and when I need to perform an update, I generate an SQL statement to execute the task.

J.A. Coutts

ok, thanks I guess I'll just sort the query bound to the control and they can use filter to find a particular entry.