MS Access ADP Autonumber

Question

I am getting the following error in an MS Access ADP when trying to add a record on a form linked to a MS SQL Server 2000 table:

Run-time error '31004':

The value of an (AutoNumber) field cannot be retrived prior to being saved.

Please save the record that contains the (AutoNumber) field prior to performing this action. note: retrieved is actually spelled wrong in the error.

Does anyone know what this means?

I've done a web search and was only able to find the answer at a certain site that only experts have access to.

Answer 1

Are you trying to assign the value of an Identity field to a variable or something else before you have saved the record?

For whatever reason, your app is trying to read the value of the identity field before the record has been saved, which is what generates that identity field. In other words, no value exists for the Autonumber field until the row is saved.

I think we'd need to see more code or know more about the steps that lead up to this error to resolve it in more detail.

Answer 2

First of all, if you are going to look at experts-exchange - do it in FireFox, you'll see the unblocked answers at the bottom of the page.

Second, do you have a subform on that form that's using the autonumber/key field on the master form? Do you require the data that's on that subform to be saved (i.e., having its own key) before the main form is saved. You could be into a deadlock of A and B requiring each other to be saved first.

Other than that, you must somehow be accessing that autonumber field whenyou are saving it. The best I can suggest is to step through the code line by line.

Answer 3

You should have add some lines of code to show us how you're managing your data and what you are doing exactly. But I am suspecting an issue related to a recordset update. can you identify when the autonumber value is created? Is it available in a control on a form? Can you add a control to display this value to check how it is generated when adding a new record? Is the underlying recordset properly updated? Can you add something like me.recordset.update on some form events: I would try the OnCurrent one ...