AutoNumber In A Multi-User Environment

[CharlesWhiteman]

Hi All, In my Db a user opens an unbound form, which on oppening retrieves the next autonumber value and remembers it in a hidden txt box. Once save is clicked then the values are saved to the database. However, i recognise that in a multi-user environment another user could also open the same form before the other user has clicked save and then there would be an error message so I'm using the following code to check this and am wondering if my method is the most efficient way of doing it?

'Check AutoNumber Has Not Already Been Taken
Dim StrAppointmentID As String
Dim DbID As String
Dim NewApptSQL As String
StrAppointmentID = Me.txtAppointmentID
StrDbID = Nz(DMax("[AppointmentID]", "TblAppointments"), 0) + 1
If StrAppointmentID = StrDbID Then
Me.txtAppointmentID = Nz(DMax("[AppointmentID]", "TblAppointments"), 0) + 1
Else
End If
' Save & Enter The Appointment Details
NewApptSQL = "INSERT INTO TblAppointments (AppointmentID, CustID, CustName, AppointmentType, AppointmentDate, AppointmentTime, AppointmentDuration, ApptWithName, ApptForName, BookedBy, BookedDate, MeetingSummary, AddedToOutlook)"
NewApptSQL = NewApptSQL & " VALUES(txtAppointmentID, txtCustID, txtCustName, txtAppointmentType, txtMeetingDate, txtMeetingTime, txtDuration, CbMeetingWith, txtRepresentative, txtMeetingBookedBy, txtDateBooked, txtMeetingBrief, cbAdded)"
DoCmd.RunSQL NewApptSQL
[Forms]![frmcust]![LbAppointments].Requery

 

[Alisa]

Why not get the next autonumber on save instead of on open?

 

[CharlesWhiteman]

Thanks for the reply. I half expected that to come back and you may be right. It could be a wood for the trees mental scenario! The reason why I have done this is for example; when a user creates a new appointment the Db also creates a task which becomes due a day after the sceduled appointment. In other words there are records in other tables which have a relationship. So I am thinking that I need to have the PK up front?

 

[Alisa]

Yes, I see your point. This is sort of a tricky scenario then. Why can't they use a bound form? Or, what if you create the new record when the form opens, so that it takes the autonumber at that point, and then on save do an update event to store whatever they entered?

 

[petehilljnr]

If I understand your problem correctly, here's an example how I have dealt with this before:

Say you've got 2 tables:

Table1: ID1 (Autonumber, PK), Text1 (Text)
Table2: ID2 (Autonumber, PK), ID1 (Number, FK), Text2 (Text)

Set up your relationships as per table set up i.e. Table1 (One) to Table2 (Many).

Then create a query that links the 2 tables but also shows ID1 (FK) from Table2.

Query1:
SELECT Table2.ID1, Table1.Text1, Table2.Text2
FROM Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID1;

If you open the query now, you'll notice that you can add text in the fields Text1 and Text2, and when you move to the next record, the Table2.ID1 field autopopulates also.

So now - we are creating a record in Table1 and Table2 at the same time. The DB can deal with the creation of the records and autonumbers by itself at the same time so no clashes should occur.

Then, we can also run code to insert records into this query, rather than into the tables directly.
e.g.

DoCmd.RunSQL "INSERT INTO Query1 (Text1, Text2) VALUES ('m1','m2')"

Sweet as!

Regards,
Pete

 

[CharlesWhiteman]

Thanks for your reply Alisa. I've stayed away from bound forms for two reasons, speed - when running across a network using aq split database there is no network or db overload. Also, if there was a network error then the record could be lost or corrupted. I dont want the dabase to create a record first for similar reasons.

Thanks too to Pete, I kind of see what you mean and will stick my brain into it in due course and may have one or two questions.