Form in Add New mode uses existing number for primary key field (1 Viewer)

Kazzief

New member
Local time
Today, 22:29
Joined
Oct 2, 2014
Messages
8
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

I’m here to help
Staff member
Local time
Today, 15:29
Joined
Oct 29, 2018
Messages
21,358
Hi. Does this behavior also happen if you open a previous working backup copy of your application?
 
Last edited:

Kazzief

New member
Local time
Today, 22:29
Joined
Oct 2, 2014
Messages
8
Good question. I'll try that now.
 

Kazzief

New member
Local time
Today, 22:29
Joined
Oct 2, 2014
Messages
8
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

New member
Local time
Today, 22:29
Joined
Oct 2, 2014
Messages
8
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:29
Joined
May 7, 2009
Messages
19,169
you may try to reset the seeding of the autonumber field (backup first).
Code:
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

I’m here to help
Staff member
Local time
Today, 15:29
Joined
Oct 29, 2018
Messages
21,358
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

New member
Local time
Today, 22:29
Joined
Oct 2, 2014
Messages
8
you may try to reset the seeding of the autonumber field (backup first).
Code:
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

New member
Local time
Today, 22:29
Joined
Oct 2, 2014
Messages
8
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

I’m here to help
Staff member
Local time
Today, 15:29
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom