ACCESS FEED: RE: [MS_AccessPros] Custom Primary Key in a Multi-User environment

Kamis, 18 Juli 2013

RE: [MS_AccessPros] Custom Primary Key in a Multi-User environment

Andrew-

The safest way using your current method is to not assign the Job Number
until the Before Update event of the form you're using to add records.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database, rst As DAO.Recordset

' If about to save a new record,
If Me.NewRecord Then
' Point to this database
Set db = CurrentDb
' Get the latest job number from Parameters
Set rst = db.OpenRecordset("tblParameters", dbOpenDynaset)
' Lock the record!
rst.Edit
' Update the job number
rst!JobNumber = rst!JobNumber + 1
' Update this record
Me!JobNumber = rst!JobNumber
' Save the change and exit to let Access save the new job
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing
End If
End Sub

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of adwsys
Sent: Wednesday, July 17, 2013 10:40 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Custom Primary Key in a Multi-User environment

Hi All,

I have a Job Tracking application (Access 2003) which has a custom Primary
Key on the Job table. When a new Job record is created, the code fetches
the next available Job Number from a parameters table and allocates it to
the Job record. Then when the new Job record is saved, the Job Number in
the parameters table is incremented. This works fine in a single-user
environment or in a multi-user environment where only one person ever enters
new Jobs, which up until now has been the case. However, the client now
wants to be able to enter multiple new jobs simultaneously and clearly the
way the application currently allocates Job Numbers will not accommodate
this.

I can think of various ways of doing it, but none of them are very elegant,
so I'd be grateful for any suggestions or pointers to any good articles.

TIA,
Andrew

------------------------------------

Yahoo! Groups Links