Solved PK numbers missing

[smtazulislam]

Any help will appreciated.
See the picture with RED marked emp. no (PK) numbering is missed.

ADD NEW EMPLOYEE form to record all of the data. If I CLICK the ADD EMPLOYEE button then save sometime is one black record saved without show same numbers in the tblEmployee. Now, I found 25 numbers is missed. I tried to compact, but its not back previous numbers.

How can I solve this problem ?

 

[Cotswold]

I suggest that you provide a great deal more information in the first instance as the summary of the issue and image are not helpful in any way

 

[smtazulislam]

I suggest that you provide a great deal more information in the first instance as the summary of the issue and image are not helpful in any way

Thank for your reply.
my employeeID numbers is PK (Value : AutoNumbers). when I save the button click then sometimes saved black record.
exp: I have recorded numbers is 1441. I try to add new employee now, its should came 1442 .
But sometimes recorded 1443 without record 1442 numbers data.

EDIT/
SaveButton

Private Sub cmdSave_Click()
'    If Nz(Me.txtEmployeeName) = "" Or Nz(Me.EmploymentDate) = "" Or Nz(Me.cboStatus) = "" Then
'       MsgBox "You should enter the an Employee Name & employment date", vbInformation + vbOKOnly, "Data Required"
'       Me.txtEmployeeName.SetFocus
'       Exit Sub
'       DoCmd.GoToRecord , "", acNewRec
'    End If
    
    If (IsNull(ValidateRecord)) Then
       Form.SetFocus
    End If
    If ValidateRecord <> 0 Then
       MsgBox "The entered data has been saved successfully" & vbCrLf & _
        "" & vbCrLf & _
        "You can choose others tab", vbInformation + vbOKOnly, "Saved Successful"
        DoCmd.GoToRecord , "", acNewRec
        'DoCmd.RunCommand acCmdSaveRecord

    End If
End Sub

 

[jdraw]

From an older article
Autonumbers
What they are NOT:
1. Row (record) sequence numbers.
2. An "order of entry into the table" number
3. A "gapless" series of numbers.
4. Editable numbers.
5. A series of (necessarily) always increasing numbers.
6. Intended to be viewed/used by end users of the application.
7. Predictable (as to what the previous or next one in the table is/or will be).
8. Reassigned, once deleted or discarded
9. A predictor/indicator of the number of rows in a table.
10. Intended to be used to "rank" or "sort" or "number" rows returned from the table.
11. Necessarily used to determine the default order the rows may be returned from the table.
12. Indicative of or related to any TimeStamp field that may also be in the table row.

What they are:
1. Unique numbers used to identify individual rows in a table.
2. Automatically created by Access when a new row is "instanced" by Access.
3. Great/Outstanding/Essential for use as the Primary Key of a table.
4. Great/Outstanding/Essential for use as "link points" by Foreign Keys in other tables.
5. Unchanging, once assigned to a particular table row

 

[plog]

You don't have a problem to solve.

What's so magical about ID=1461? How is your database adversely effected by it not existing in your database?

 

[smtazulislam]

You don't have a problem to solve.

What's so magical about ID=1461? How is your database adversely effected by it not existing in your database?

Hello sir, Where you found the magic ?
can you talk me, How this database missing the record. You see the red numbers missing the serial.
see picture...


Edit/
Even I dont delete any saved records data.

 

[smtazulislam]

Private Sub cmdSave_Click()
'    If Nz(Me.txtEmployeeName) = "" Or Nz(Me.EmploymentDate) = "" Or Nz(Me.cboStatus) = "" Then
'       MsgBox "You should enter the an Employee Name & employment date", vbInformation + vbOKOnly, "Data Required"
'       Me.txtEmployeeName.SetFocus
'       Exit Sub
'       DoCmd.GoToRecord , "", acNewRec
'    End If
   
    If (IsNull(ValidateRecord)) Then
       Form.SetFocus

"Here need something added as my idea, Maybe database need compacting when record validate is incorrect OR undo the ID number OR Current record is delete automatically" Need someone focus my idea, its can possible.

    End If
    If ValidateRecord <> 0 Then
       MsgBox "The entered data has been saved successfully" & vbCrLf & _
        "" & vbCrLf & _
        "You can choose others tab", vbInformation + vbOKOnly, "Saved Successful"
        DoCmd.GoToRecord , "", acNewRec
        'DoCmd.RunCommand acCmdSaveRecord

    End If
End Sub

 

[arnelgp]

it is Missing the Series because, on New record (the new number will be created), you Cancel addding new record.
if you cancel the New record, it will not re-claim the old number but will advance to New number.

it is best to Leave it as is.
if you renumber it again, maybe other table is relying on that number and
your db will be at mess if you try to.

 

[smtazulislam]

it is Missing the Series because, on New record (the new number will be created), you Cancel addding new record.
if you cancel the New record, it will not re-claim the old number but will advance to New number.

it is best to Leave it as is.
if you renumber it again, maybe other table is relying on that number and
your db will be at mess if you try to.

Thank you very much, any suggested that I don't use this line "DoCmd.GoToRecord , "", acNewRec". Lets me try some numbers

 

[GPGeorge]

Re-Read Post #4 & Post #8. Take them seriously. It's not wise to expect AutoNumbers to be useful or meaningful to humans in any way outside their role as the Prmary Key for a table.

 

[smtazulislam]

Re-Read Post #4 & Post #8. Take them seriously. It's not wise to expect AutoNumbers to be useful or meaningful to humans in any way outside their role as the Prmary Key for a table.

I read it sir, here this form have some conditions to save data that is I called ValidateREC.
And this EmployeeID (PK) is liked the others tables.
in my absent my colleague added lot of data with gap the numbers. Now I can't add or edit this recorded.
so I need a ways to fill up numbers the record.

 

[cheekybuddha]

Hi, if you need sequential numbers to associate with your Employees, then don't use the Autonumber.

Leave the PK as autonumber, since it is the easiest way to identify a record in your table uniquely.

Add another field to your table for your sequential Employee Identifier and call it something like EmpNo.

You can populate it using an expression like =Nz(DMax("EmpNo", "tblEmployee"), 0) + 1

However, you will need extra code to take care in a multi-user situation.

 

[The_Doc_Man]

in my absent my colleague added lot of data with gap the numbers. Now I can't add or edit this recorded.
so I need a ways to fill up numbers the record.

The fact that you have gaps in your autonumbered records is not a technical reason to be unable to add or edit a record. Whether you used an autonumber method or computed each number on-the-fly, having contiguous numbers is not a database requirement. It might be an auditor's requirement - but not a database requirement. And that is true for more than just Access. It is also not an issue in SYBASE, ORACLE, and SQL Server.

If your system will not allow you to add an autonumbered record, make a copy of your DB (as a precaution in case the operation crashes or fails) and perform a Compact & Repair operation. If that works, you can discard the copy or keep it as a backup. That C&R will resynchronize all autonumber indexes; doing that should allow you to add records again. I do not know why you would be unable to edit a record, but the C&R operation might also help with that. If it succeeds, it is indicative of possible corruption caused by improperly closing the DB while records were pending for writing to the disk. Like turning off or rebooting the computer while Access is still open. That'll do it every time.

There IS NO SOLUTION to missing numbers in an autonumbered index because it isn't a problem in the first place. Anytime someone uses a CANCEL after starting to create a record of that type, you WILL have a gap. But then, autonumbers are NEVER guaranteed to be contiguous. They are only guaranteed to be unique. I have only ever found ONE use for an autonumbered index and that was as part of a "confirmation" number.

 

[GPGeorge]

I read it sir, here this form have some conditions to save data that is I called ValidateREC.
And this EmployeeID (PK) is liked the others tables.
in my absent my colleague added lot of data with gap the numbers. Now I can't add or edit this recorded.
so I need a ways to fill up numbers the record.

The problem here appears to be two fold then. First, and most critical, it sounds like the tables do not have Referential Integrity enforced on relationships. This allows entry of records in the table on the many side of a one-to-many relationship which have no corresponding record in the table on the one side of the relationship.

In addition, there might be a form design problem. Your forms allow records to be added to the many-side tables without a valid record in the one-side tables.

If the proper referential integrity were enforced, and if the forms are designed to support than, then records can't be added to many-side tables without a valid Foreign Key from the one-side tables. And that would mean gaps have no consequences.

 

[smtazulislam]

If your system will not allow you to add an autonumbered record, make a copy of your DB (as a precaution in case the operation crashes or fails) and perform a Compact & Repair operation. If that works, you can discard the copy or keep it as a backup. That C&R will resynchronize all autonumber indexes; doing that should allow you to add records again. I do not know why you would be unable to edit a record, but the C&R operation might also help with that. If it succeeds, it is indicative of possible corruption caused by improperly closing the DB while records were pending for writing to the disk. Like turning off or rebooting the computer while Access is still open. That'll do it every time.

I tried it. Its compact the last gap records. previously record are same...

 

[Pat Hartman]

If the field in question is really an autonumber, the fact that some records are missing a value is alarming. The table is corrupted and perhaps the entire database.

To fix the table
0. Turn off Name Auto Correct if it is on.
1. Remove any Referential Integrity. You will add it back later.
2. copy all the records with missing IDs to a temporary place and delete them from the table.
3. Copy the schema of the original table to create a new, empty table.
4. Create append query #1. Select remaining records from original table and append them to new, empty table. Include the autonumber in the selection. This is the ONLY way you can update an autonumber - i.e. append it with a record..
5. Compact and repair.
6. Create append queyr #2. Select the records missing the autonumbers from the temp table and append them to the new table. Do NOT include the autonumber column. Access needs to assign a new autonumber.
7. Compact and Repair.
8. Delete the original bad table
9. Rename the new table to the old table name
10. Reset the RI
11. Turn on Name auto correct if you really want it on. This is such a dangerous "feature" that I always leave it off and only turn it on if I need it to do its thing and I am prepared to step through it to ensure everything I change propagates correctly.

PS, at some point in time, there was a bug with the Seed of the autonumber and if you had a table with an autonumber but had defined a different column as the PK, the autonumber would loose it's seed and start generating duplicate numbers. As the others have mentioned, the autonumber has one and only one use and that is as a unique primary key for a table. If you have a different field defined as the primary key, you have no need of the autonumber and it should be removed from the table.
PPS, In this particuar situation, you might be able to fill the gaps if you know which records should go where. To do that, you could type the numbers into the empty fields in the temp table. BUT, you would have to assign valid numbers to all of the rows, not just some OR split into two tables. Then in #6, the second append query would also include the PK field in the queyr.

 

[arnelgp]

Thank you very much, any suggested that I don't use this line "DoCmd.GoToRecord , "", acNewRec"

leave it as is, it will not create new number, yet.
the recordset is just positioned to the "New" record.
once you start typing something on this New record, then
it will use the Next autonumber.

 

[Mike Krailo]

I read it sir, here this form have some conditions to save data that is I called ValidateREC.
And this EmployeeID (PK) is liked the others tables.
in my absent my colleague added lot of data with gap the numbers. Now I can't add or edit this recorded.
so I need a ways to fill up numbers the record.

Is that ValidateREC done in the forms Before Update Event? If not, it should.

When those employee's added lots of data, they most likely canceled the add operation multiple times to create those gaps in the autonumbers for EmployeeID. This is all totally normal and the way it should work. It has no effect on anything because those autonumbers were essentially thrown away by the user adding records and then deciding to cancel the addition. In other words, there never was a valid record created that later needs to be edited. All those gaps are simply aborted record additions (new employee's that never got created). If you need an unbroken sequence of EmployeeNumber, it should have it's own field and should be created with code. Otherwise, there is no problem with gaps in autonumbers as has already been said multiple times.

 

[Pat Hartman]

See the picture with RED marked emp. no (PK) numbering is missed.

Gaps in an autonumber are totally different from MISSING autonumbers. Missing autonumbers just don't happen.

@smtazulislam --- please clarify.

 

[smtazulislam]

If the field in question is really an autonumber, the fact that some records are missing a value is alarming. The table is corrupted and perhaps the entire database.

To fix the table
0. Turn off Name Auto Correct if it is on.
1. Remove any Referential Integrity. You will add it back later.
2. copy all the records with missing IDs to a temporary place and delete them from the table.
3. Copy the schema of the original table to create a new, empty table.
4. Create append query #1. Select remaining records from original table and append them to new, empty table. Include the autonumber in the selection. This is the ONLY way you can update an autonumber - i.e. append it with a record..
5. Compact and repair.
6. Create append queyr #2. Select the records missing the autonumbers from the temp table and append them to the new table. Do NOT include the autonumber column. Access needs to assign a new autonumber.
7. Compact and Repair.
8. Delete the original bad table
9. Rename the new table to the old table name
10. Reset the RI
11. Turn on Name auto correct if you really want it on. This is such a dangerous "feature" that I always leave it off and only turn it on if I need it to do its thing and I am prepared to step through it to ensure everything I change propagates correctly.

PS, at some point in time, there was a bug with the Seed of the autonumber and if you had a table with an autonumber but had defined a different column as the PK, the autonumber would loose it's seed and start generating duplicate numbers. As the others have mentioned, the autonumber has one and only one use and that is as a unique primary key for a table. If you have a different field defined as the primary key, you have no need of the autonumber and it should be removed from the table.
PPS, In this particuar situation, you might be able to fill the gaps if you know which records should go where. To do that, you could type the numbers into the empty fields in the temp table. BUT, you would have to assign valid numbers to all of the rows, not just some OR split into two tables. Then in #6, the second append query would also include the PK field in the queyr.

Great Idea ! Now I count the missing numbers. and manually fill all. Thank you.