Question Field Indexed

[ypma]

Fellow Access users ,
Using access 2010 multiple users with backend on the server .

Of late I am getting duplicate client id numbers when the field index is set to indexed Yes (No Duplicates) . "Happening about once a week"

I currently solve the problem by deleting one of the duplicate client id ,but have to access the backend to reset the index, which has reverted to Indexed No.

Any pointers as to possible cause would be appreciated

Regards

 

[CJ_London]

is the field an autonumber primary key? Or a calculated 'next number' type of field? Are there frequent deletions of records and compact/repair? Does the value have any meaning to you other than as a record identifier?

Also not sure how the field is being changed from being indexed - no duplicates to not being indexed. Do you have any sort of code/data macros which might impact this?

 

[ypma]

CJ, Thank you for quick response to my question. The field is an autonumber , but not a primary key. The deleted records warning has been occurring about once a week for the last couple of Months and compact/repair is done each time I go into the backend.

The field is used by users to find a client using a search key which has been used since ac2000 version , but have pasted the code for reference . For further info, the server is running at full capacity and is due to be replaced.

Regards Ypma

#Private Sub Command146_Click()

If IsNothing(Me.SearchKey) Then
MsgBox " pse enter a client id in searchKey Box"
Exit Sub
End If

Dim db As Database
Set db = CurrentDb
Dim rec As DAO.Recordset
Set rec = db.OpenRecordset("personal Detaill new", dbOpenDynaset)
With rec
.FindFirst "[Client ID] = " & Me!SearchKey

If Not .NoMatch Then ' we found it


DoCmd.OpenForm "Personal Details Form", wherecondition:="[Client ID] = " & Me!SearchKey
DoCmd.GoToControl "[client id]"

End If
#

If Not .NoMatch = False Then
MsgBox "No Match"
End If

End With
End Sub

 

[CJ_London]

when you compact and repair, the autonumber seed value gets reset. What can happen is if there are gaps in the range, Access does not pick up the highest number but a lower one where there is a gap. Don't ask me why, I don't know - but take a look at this link for some of the causes and a fix

http://allenbrowne.com/ser-40.html

 

[ypma]

CJ, You may have solved my problem in my nativity I though it followed on from the last number ,I will try out AllenBrowne fix .
Thanks again
Regards Ypma

 

[CJ_London]

I though it followed on from the last number

it's not guaranteed.

Take a look at this link about autonumbers - doesn't directly cover your issue but you may find useful

http://www.utteraccess.com/wiki/index.php/Autonumbers