How do I fix a Primary Key autonumber problem

[DPROMICS]

I have a database that won't allow me to enter new records. The problem is that when a new record is going to be inserted in to it the Auto Index number is a duplicate of an existing index number. The primary key field for this table is set to No Duplilcates. It looks like the auto index numbering has been reset back to a number already in use by an existing record.

The current database's primary table has 619 records in it. When I try to enter a new record the auto index sets the record number to 31. Of course that number is already in use by another record.

So some how the auto numbering was reset. So how do I reset it so that when I add a new record it starts with 620?

OK, I suppose I could export the database and then reimport it into a blank database. However, creating a blank database means just deleting all of the records in the database. Which will leave the auto number index set to start at 31.

Any body have any suggestions or ideas of how to fix this problem?

Best Regards,

 

[DPROMICS]

Adendum to above.

The main table, Tbl_PT_Demographics, has a one to many relationship with tables Tbl_Coumadin_Clinic_Visits and Tbl_Pacemaker_Clinic_Visits.

So mucking with the primary index in the table Tbl_PT_Demographics is a bit risky I suppose.

This is a live database so I don't want to loose any data.

Best Regards,

 

[PHV]

You may try this query:
ALTER TABLE Tbl_PT_Demographics ALTER COLUMN myID COUNTER(620,1);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DPROMICS]

I'll try that. I also found that by Exporting the specific table (Tbl_PT_Demographics) would also fix the index problem. Export exports the table in this case Tbl_PT_Demographics to another table name. I found that if I renamed the original Tbl_PT_Demographics to Tbl_PT_Demographics_Cur and renamed the exported table to Tbl_PT_Demographics all worked well again.

What appairently caused the problem with the index in the Tbl_PT_Demographics table is the following VB code.

Private Sub Cmd_Add_New_Patient_Click()
On Error GoTo Err_Cmd_Add_New_Patient_Click

' On Add New Patient insure that the Patient Data subform is visible
SubFrm_PT_Demographic_Pacemaker_ICD_Device_Data.Visible = True

' Set Focus to the Patient Demographic Data Subform
' [SubFrm_PT_Demographic_Pacemaker_ICD_Device_Data].SetFocus

' Create New Record for the New Patient to be entered in to the Database
DoCmd.GoToRecord , , acNewRec

Exit_Cmd_Add_New_Patient_Click:
Exit Sub

Err_Cmd_Add_New_Patient_Click:
MsgBox Err.Description
Resume Exit_Cmd_Add_New_Patient_Click

End Sub

The Set Focus line seems to be causing the table index problem. If that line of code is commented out everything works great. However, the subform's first entry is not automatically selected waiting for data.

If the line of code is placed after the DoCmd.GoToRecord , , acNewRec one then gets an error message, Tried to enter null value . . . or something like that, when one enters the first character in the first field of the subform. After that all works well. Very frustrating!

The idea is to have the subform made vissible and the first data field on it selected and waiting for data. As it is now, one must click on the first data field on the subform to start the data entry process on the subform.

Thanks for your reply. You keep later hours than I do. My day starts at 5:30 AM and ends when ever I finally get to bed.

Best Regards,