Form in Add New mode uses existing number for primary key field

[Kazzief]

Hi,

I created a form to create a brand new record. The form has 2 subforms in it and until now it has been behaving. The database has been in use for about 4 years without problems. I regularly compact and repair the database.

The form is opened (in add mode) via a button on another form (which is used as a menu).

The primary key fields are autonumbers.

The form has been working well until recently. Now when I click the button to open the form in add mode, the subforms are blank (great); but the parent form is populated with an existing ID and doesn't say (New) as it would usually. When I see this, I close the form and as you would expect, I have to abandon the data as I can't overwrite an existing ID.

I have no idea why this has started happening. I have 2 forms like this and they've both taken on this behaviour. Have I missed something in a software update that could be causing this or could something else have happened to the data?

if anyone has any ideas I'd be grateful.

Many thanks.

 

[theDBguy]

Hi. Does this behavior also happen if you open a previous working backup copy of your application?

 

[Kazzief]

Good question. I'll try that now.

 

[Kazzief]

I don't have the latest back up. I created this for someone else to use and it is on their machine. However, the last copy I have prior to this is from January 2019 and that works fine. The user should have backups that I can go to if need be.

The odd thing is that when I first open the database (the most recent one), and I click to open the form and create a new record, it starts by numbering from an entry which was previously deleted (so, say we've got 100 records and records 10 and 12 were deleted, we've compacted and repaired and when I open the database and click the button to create a new record, it numbers 10. When I do it again, it numbers 11, which still exists. It doesn't go to a completely new record or to the next free number - if that's a thing!).

The January version does.

Can you think of why that can be?

Thanks,
Karen

 

[Kazzief]

Me again,

Just thinking, do you think that if I copy over the forms and macros from the working file into the newest file it would make a difference? It all seems a bit odd.

 

[arnelgp]

you may try to reset the seeding of the autonumber field (backup first).

Public Function ResetAutoNumber(ByVal TableName As String, ByVal AutoField, Optional Start As Long = 1, Optional Increment As Long = 1)
'You can execute an Access DDL statement from ADO to reset the autonumber seed value. Here is an example Immediate window session:
'The statement must be executed from ADO. It will fail if you try it with DAO (such as CurrentDb.Execute strDdl), or from the Access query designer.
'The example succeeded because Currentproject.AccessConnection is an ADO object.
'The two values following COUNTER are seed and increment. So if I wanted the autonumber to start from 1000 and increment by 2, I could use COUNTER(1000, 2)
'If the table contains data, the seed value must be greater than the maximum stored value. If the table is empty when you execute the statement, that will not be an issue.
Dim strDdl As String
On Error Resume Next
strDdl = "ALTER TABLE " & TableName & " ALTER COLUMN " & AutoField & " COUNTER(" & Start & ", " & Increment & ");"
CurrentProject.AccessConnection.Execute strDdl

End Function

 

[theDBguy]

Me again,

Just thinking, do you think that if I copy over the forms and macros from the working file into the newest file it would make a difference? It all seems a bit odd.

Hi. If you're talking about an Autonumber field, then I think the problem is in the backend, not in the front end. So, updating the FE objects won't probably help at all.


PS. When you perform C&R on the BE file, make sure you're not doing it over the network.

 

[Kazzief]

you may try to reset the seeding of the autonumber field (backup first).

Public Function ResetAutoNumber(ByVal TableName As String, ByVal AutoField, Optional Start As Long = 1, Optional Increment As Long = 1)
'You can execute an Access DDL statement from ADO to reset the autonumber seed value. Here is an example Immediate window session:
'The statement must be executed from ADO. It will fail if you try it with DAO (such as CurrentDb.Execute strDdl), or from the Access query designer.
'The example succeeded because Currentproject.AccessConnection is an ADO object.
'The two values following COUNTER are seed and increment. So if I wanted the autonumber to start from 1000 and increment by 2, I could use COUNTER(1000, 2)
'If the table contains data, the seed value must be greater than the maximum stored value. If the table is empty when you execute the statement, that will not be an issue.
Dim strDdl As String
On Error Resume Next
strDdl = "ALTER TABLE " & TableName & " ALTER COLUMN " & AutoField & " COUNTER(" & Start & ", " & Increment & ");"
CurrentProject.AccessConnection.Execute strDdl

End Function

This looks great. Thank you for your help.
However, I really don't know where to start with this. Unfortunately I'm not a programmer.
Thanks though.
Karen

 

[Kazzief]

Hi. If you're talking about an Autonumber field, then I think the problem is in the backend, not in the front end. So, updating the FE objects won't probably help at all.


PS. When you perform C&R on the BE file, make sure you're not doing it over the network.

Yes, I can confirm that is the case. We aren't doing the C&R on the network.

I do wonder what has happened to the data.

 

[theDBguy]

Yes, I can confirm that is the case. We aren't doing the C&R on the network.

I do wonder what has happened to the data.

Anything could happen to the data, we are talking about computers after all (power or network interruptions could happen anytime). If you have a good backup of the data, try switching it out with your current one to see if the problem still exists. If so, try importing all the tables into a brand new and empty BE file and then test that.