Question "Check Out" Record

[roystreet]

Hello - I'm looking for ideas on something I've been considering for awhile. I have a database that partly deals with creating/modifying/reviewing projects. It is a multi-user database. I wanted to make it so that if a user had opened a project that it would mark it as the project was "Checked Out" (Like a book in a library) And the next user trying to open it could be redirected to a report showing many of the fields of the record, but wouldn't be editable. I believe this helps alleviate some problems that can occur when 2 users open the same project near the same time. I, of course, wanted to make it that once they exit the project that it mark it as checked back in.

I guess, what I was thinking was placing a value in a type of "ProjStatus" field (For a lack of better name) where if it was opened that the value would "2" else "1" Then when a user selected the project from a drop down, that it would check the table for the value using VB, if the value 2, then open report, else open the project editor.

I don't want it to be too complex...I've just noticed that multiple users could be in a record & every once it a while Access would tell you that "your changes were not able to saved because of duplicate records" - Something like that, I can't remember the exact warning.

Also, user 1 could close the project while user 2 would be changing a date which shouldn't be allowed if the project was closed, etc.

Anyway - Any Ideas Are Welcome...Thanks!

 

[spikepl]

See the part Update here: http://www.access-programmers.co.uk/forums/showpost.php?p=1430826&postcount=2

 

[JLCantara]

Hello Roy!

Spike is right in mentioning possible crash or abrupt PC turn off. So a simple flag may not be the best solution. I would rather keep the user name (use Environ("UserName")). So if a project is abnormally locked, you can check with the user and/or reset it manually.

Good luck, JLCantara.

 

[roystreet]

Hello Roy!...

Spike is right in mentioning possible crash or abrupt PC turn off. So a simple flag may not be the best solution. I would rather keep the user name (use Environ("UserName")). So if a project is abnormally locked, you can check with the user and/or reset it manually.

Good luck, JLCantara.

What do you mean by check with the user? Physically check or use an automated way? ...Part of why I ask is I would rather have very little need to directly work with the user. Not because I'm anti-social but I just want it to work for them, so they can simply get their job done with little interruptions or stress.

thanks

 

[JLCantara]

Hello Roy!

I now realize that your question is more complex then I first taught!
1 - Install an app user log (I have one...).
2 - In a UsrLock field, when a project is opened in update mode (you need to have 2 ways, read and update to access the main project form).
3 - When read a project in the Update form, check if it is locked and WARN THE USER that the project cannot be updated (make a read button visible to switch the read mode).
4 - Before displaying the project, save the UsrLock data.

Complicated? Well your problem is a very typical one and its solution has to be addressed totally. I had to solve such a problem for an internet gulf club court reservation. The error a junior made was to ignore the case of multiple open in read mode and then switching to update mode. She refused to believe me and upon demo, I won a 25 cents bet!!!!!

Good day, JLC.

 

[vbaInet]

Some good advice already, but before we start talking about concepts, how quickly can your users move between projects/records?

If your users can quickly navigate between records then this is not the sort of system you would want to implement because each time you move between records you're constantly checking and writing to the table.

So perhaps you can clear up this point.

 

[JLCantara]

Hello vbaInet!

If I were living in UK I would put 100$CDN on this one! If the operation is done correctly, how ever fast the user is, he would never notice anything!!!
Don't forget that thanks to the huge RAM storage, a major part of one's DB is actualy in mem. Lately, I have run a test on a 160,00 records SQL: took 2 seconds to return the result...

Good day, JLC.

 

[vbaInet]

We use Pound Sterling over here JLC, so I would gladly accept your £100

Of course the user wouldn't notice a thing but regardless of how much RAM you have, or how much processing speed you've got, one should try to minimise network traffic and resources as much as possible. If there's another approach that would use less network then it's worth re-thinking your setup before implementation.

 

[JLCantara]

Hi vbaInet!

Hey, wait a second, 100 $CDN is £53!!! you are trying to fool me...

Network traffic is easy to monitor. So if a given system is overloading network traffic, THEN it's time to modify it. My attitude has always been: best formal solution. Too heavy? OK let's adapt to the goddamn network. Note that I have been spoiled: I started programming on a Cyber 74 (long time ago) and believe me, overloading a CDC computer is a heck of a job.

Good day, JLC.

 

[vbaInet]

Worth a try hehe!

Alright, I'm not completely ruling out the method proposed, I'm just prompting the poster to rethink strategies and give us more information.

But going back to the issue on debate, imagine 10 or more users with their finger continuously hitting the arrow key searching for a record (perhaps), what would happen in this instance? You're constantly locking the record, unlocking on lost focus and locking the next. Now, with a quick form redesign, one could avoid such an instance by using a lock/unlock button or by double clicking the record.

The point is, see if your form needs to be redesigned then consider an approach.

 

[JLCantara]

Hi vbaInet!

What was not explicitly mentioned in my post his the necessary presence of 3 objects: a project combo and 2 buttons - one to consult and an other one to modify the project. So you only need a query for the combo (1 transfer) and one transfer for any project selected.
If I had 10 users trying to screw the system, well I would make their life miserable...
In an another life, I had to manage a plant server: one day, an operator downloaded porn vids billed to the company: no one ever repeated the joke!!!

Good day, JLC.

 

[vbaInet]

In an another life, I had to manage a plant server: one day, an operator downloaded porn vids billed to the company: no one ever repeated the joke!!!

Good day, JLC.

He must have been very bored

 

[JLCantara]

No, the plant was over staffed, so night and weekend shifts were partying!

JLC.

 

[roystreet]

Worth a try hehe!

Alright, I'm not completely ruling out the method proposed, I'm just prompting the poster to rethink strategies and give us more information.

But going back to the issue on debate, imagine 10 or more users with their finger continuously hitting the arrow key searching for a record (perhaps), what would happen in this instance? You're constantly locking the record, unlocking on lost focus and locking the next. Now, with a quick form redesign, one could avoid such an instance by using a lock/unlock button or by double clicking the record.

The point is, see if your form needs to be redesigned then consider an approach.

I'm trying to get through all of the responses here. I do not use arrow keys. I have created a form which shows all projects in a drop down menu. They then select the project they want to see. They then can select to just see report or an actual form (Project Editor). The project editor opens based on the system number for that project. The system number is never seen by the user & is unique. They see the "project number". Users can not switch from project to project via arrow keys.

Also, once the project editor is opened it runs code that enables certain buttons based on a "Open/Closed/Cancelled" value selected previously. This does use a record lock script also for the subforms. This stops users from editing a project that was previously locked. My problem is mostly based on the concern that once 2 users open an open project.

 

[vbaInet]

Sounds like an ideal situation for the setup that spikepl and JLC described. I'll advise that you wrap the whole thing in a class which resets the flag in the Terminate() sub.