Hi John,
Thanks for the response.
I had thought about not allocating a Job Number until just before the record is saved as you suggest, and although it is probably the best everyday solution unfortunately it doesn't cover all the eventualities that this system has to deal with.
For example, It doesn't allow the users to see the Job Number on screen when they are entering a new Job. Aside from the fact that this is what they are used to, it is also important because some jobs are allocated to engineers over the telephone & the user does that whilst the job screen is open. Getting them to change this practice would be an uphill struggle at best.
I know that this is an age-old problem in Multi-user databases & having thought about it a bit since I posted yesterday, I think one solution is to allocate the Job Number when the user starts entering a new record, & then if the record is completed successfully, all is well. If for some reason the user cancels the New Job, check that another user hasn't created another job in the meantime. If they haven't delete the Job & reset the counter. If they have, void the Job and carry on. You would loosa a Job Number, but I think the could live with that.
Andrew
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar