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

Jumat, 19 Juli 2013

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

Andrew-

It's really a small change. Tell the users they have to SAVE the record to
see the job number. Give them a big fat red Save button. It also ensures
they have all the critical information from the engineer before creating a
new job.

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: Thursday, July 18, 2013 7:10 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Custom Primary Key in a Multi-User environment

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
>

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

Yahoo! Groups Links