Multi-User Db / Vb Generated PK Values

[CharlesWhiteman]

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

 

[Rabbie]

See the latter part of this thread that is discussing a similar issue. Several alternate solutions discussed.

 

[CharlesWhiteman]

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.

 

[CharlesWhiteman]

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.