Hi All, I'm posting to see what other methods for doing this there might be. In my db, which is multi-user, users enter call history details. This is via an unbound form and a INSERT INTO script. The problem lies where on the form, it looks up the next value in the table for the CallID value but if two users open the form at the sdame time then one will error because the txtbox (txtCallID) will be issued to 2 users. so the following is a workaround which i developed but want to check that it is the best way?

Dim StrDbID As String
Dim StrFormID As String
StrDbID = DMax("[Call Reference]", "TblCalls") + 1
StrFormID = Me.txtCallReference
If StrDbID = StrFormID Then 'My form checks to see if a call has been entered using the same ID and if so then uses the next value.
Me.txtCallReference = DMax("[Call Reference]", "TblCalls") + 1
Else
End If

Me.Refresh
Dim strSql As String
Dim strSQLAudit As String
Dim StrAuditType As String
Dim StrUser As String
Dim StrDepartment As String
Dim StrAuditDate As String
StrAuditType = Me.txtCallResult
StrUser = CurrentUser()
StrDepartment = Me.txtDepartment
StrAuditDate = Me.txtDate
strSql = "INSERT INTO TblCalls (CompanyCode, [Call Reference], [Call Date], Department, Person, [Call Note], Result)"
strSql = strSql & " VALUES(txtCompanyCode, txtCallReference, txtDate, txtDepartment, txtPerson, txtCallNote, txtCallResult)"
strSQLAudit = "INSERT INTO TblAudit (AuditType, User, Department, Auditdate)"
strSQLAudit = strSQLAudit & " VALUES('" & [StrAuditType] & "', '" & [StrUser] & "', '" & [StrDepartment] & "', '" & [StrAuditDate] & "')"
DoCmd.RunSQL strSql
DoCmd.RunSQL strSQLAudit
DoCmd.Close acForm, "FrmCallNew"
[Forms]![FrmDatabase]![LbCalls].Requery

I only had to set up a system like this once and that was quite a while ago.

The general consensus at the time was the best way was to look up the maximum existing record, (as you are doing I believe) and then add one to it.

If you have a unique index set against the field that contains the unique ID, then if you try and post back into this field an existing ID,it will throw an error. You can collect this error number and generate a new number if the error is triggered.

I think that's what I did, however, with more experience under my belt, I now realize that it is not considered good practice to depend on error generation to make your code run properly.

I would suggest an alternative would be to do the same, (generate what you consider the next highest number) just before posting this number back to the field, run a quick check to see if the number already exists in the table, if it does, generate a new number.

>>>Newbies of today are the experts of tomorrow. Where would we be without community!<<<

I like your tagline Charles, I would add however that the expert of tomorrow, is now only an expert where the Newbie is concerned. In other words I am discovering the more I learn, the less I know.

See the latter part of this thread (http://www.access-programmers.co.uk/forums/showthread.php?t=165887) that is discussing a similar issue. Several alternate solutions discussed.

Thanks Rabbie, Good information indeed. In my case have gone fo rthe DMax approach with error handling which I think should be good enough - at least for my purposes. Having a separate tbl to store id's seems to be adding an extra step. Thanks all for your posts and feedback.

Autonumber may be a good startpoint but I have come across scenarios where autonumber is a bit of a pain and i think the more 'programmer control' which can be utilied then the better and greater flexibility.