Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-14-2019, 04:41 AM   #1
Zampa
Newly Registered User
 
Join Date: May 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Zampa is on a distinguished road
Record locks on a form

hope someone can help me with this.
I have a table of data that comprises a bunch of customer data that we need to call each day. At the moment the list from the table of customer details is printed off and passed round a team of 6 for each agent to use as their work sheet for the day. I'd like to change this so that, in a multi user environment, we have a form displayed that shows the customer details on the screen and allows the user to record outcomes etc. effectively editing the record

That's all working well and have no concerns about collecting data and storing it back etc. BUT if I open the form on my PC the customer record shows as i hoped it would. However if one of my colleagues opens the form on their screen at the same time, we both see the same record.

I have put a record lock on the form properties for "edited record" but on its own this doesn't seem to help. At the moment we are both simply opening the form from the side bar of all Access objects list.

As we move forward and before release to the agents, I will be creating a command button on a Welcome form with "give me the next record" on it (or similar). Is there some code i should add to this button that will open the form and display data based on the next unlocked record in the table? Hopefully that makes sense.
should add that at the moment the database is not split, not sure if that matters.
Many thanks in advance


Last edited by Zampa; 08-14-2019 at 05:18 AM.
Zampa is offline   Reply With Quote
Old 08-14-2019, 06:13 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,335
Thanks: 0
Thanked 735 Times in 720 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Record locks on a form

you MUST have a split db. Do you?
each user has a their own copy of the front end
and the Backend tables is on a server.
this prevents the locks.

another way is to show users a snapshot and only allow them to edit 1 record.
Ranman256 is offline   Reply With Quote
Old 08-14-2019, 06:13 AM   #3
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,164
Thanks: 495
Thanked 904 Times in 856 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Record locks on a form

Quote:
Originally Posted by Zampa View Post
should add that at the moment the database is not split, not sure if that matters.
Yes it matters, you need to split the database.

__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 08-14-2019, 06:21 AM   #4
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,164
Thanks: 495
Thanked 904 Times in 856 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Record locks on a form

Quote:
Originally Posted by Zampa View Post
we both see the same record
Once you've split the database, the front end portion is shared to each user. have code in the Front end to capture the PCs name. The PC Name can be used to control which records are displayed to which user. But before you can display such records you will have to allocate the PC Name to each record that the user is going to manage. You could have a manager assign records to individual users, or possibly let the user choose a record themselves, when they select the record then their PC name is allocated to the record. Really it depends what you want to do, more detail about your processes would be good.
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 08-14-2019 at 06:22 AM. Reason: username > PC Name
Uncle Gizmo is offline   Reply With Quote
Old 08-14-2019, 06:26 AM   #5
Zampa
Newly Registered User
 
Join Date: May 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Zampa is on a distinguished road
Re: Record locks on a form

Thanks so far. I'll split the table. The intention was that each user will open the Dbase from a single network drive, in a different folder to where the Back end will be. I think I then need to copy the .accdb it to a folder on each users local C drive for them to launch form there?
Zampa is offline   Reply With Quote
Old 08-14-2019, 07:19 AM   #6
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,164
Thanks: 495
Thanked 904 Times in 856 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Record locks on a form

Quote:
Originally Posted by Zampa View Post
I think I then need to copy the .accdb it to a folder on each users local C drive for them to launch form there?
Yes, that's the usual method, in fact I believe there is some code somewhere that will check the local copy on the user's PC against a master copy on the server. If the server copy is updated, then the updated version is automatically copied to the user's local folder. I have no idea where to find the code, you may find it by searching, it's a "would be nice to have"- but you don't actually need it.
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 08-14-2019, 07:27 AM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Record locks on a form

You don't split the "table", you split the database. You end up with one database that contains ONLY tables. This is referred to as the BE (back end). It is saved to a network drive where it will be shared by ALL users. The second database is the FE (front end). It contains all the forms/reports/etc and LINKED tables. You keep the master copy of this in a safe place, preferably zipped so you don't accidentally update it and then distribute a separate copy to each user which should be run from their local C: drive. When you need to make a modification to the application to add a new report or fix a firm, you update the master and test it and then lock it up again and distribute copies to each individual.

The distribution can be very simple. I use a .bat file. Instead of a shortcut that opens the FE database, the shortcut opens the .bat file (which is located on the server). The .bat file makes a local directory, deletes any existing .accdb, copies the master to the local drive, and then opens it. This way, the users start every day with a new, clean copy of the FE which prevents problems with bloat and corruption.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-14-2019, 07:30 AM   #8
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,164
Thanks: 495
Thanked 904 Times in 856 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Record locks on a form

Quote:
Originally Posted by Pat Hartman View Post
This way, the users start every day with a new, clean copy of the FE which prevents problems with bloat and corruption.
I hadn't thought of that, that's Clever!
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 08-14-2019, 08:37 AM   #9
Zampa
Newly Registered User
 
Join Date: May 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Zampa is on a distinguished road
Re: Record locks on a form

Thanks - back end tables? - bad use of language. all the tables are now split off into a separate network folder and hopefully tomorrow we'll get the chance to create local copies of the front end and go from there.
Thanks all for help given so far.
Zampa is offline   Reply With Quote
Old 08-14-2019, 09:20 AM   #10
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,850
Thanks: 411
Thanked 691 Times in 670 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Record locks on a form

You should likely be only giving them an accde FE and not an accdb FE ?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 08-14-2019, 11:30 PM   #11
Zampa
Newly Registered User
 
Join Date: May 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Zampa is on a distinguished road
Re: Record locks on a form

Thanks Gasman - didn't know that. I'll google and find out more.
where i am at right now is that I've split the database and left the backend tables on a network drive. The front ends are now in a new folder on the C drive of two different, networked, PCs.
The form properties of the form that the agents use to review the data and make their calls, before doing any editing, are set to record locks - Edited record, this still though present the same data to different screens. I then thought it was because i hadn't actually edited any data, so I filled in a couple of data fields on the form on PC1 and then opened the form on PC2, which just showed the same record, including the updated data fields. If i set the record locks property on the form to ALL records, sure enough that locks it out on PC2 for example and stops me accessing nay data at all ( as expected) but that kind of defeats the object really and i might as well use an Excel spreadsheet. Should i perhaps put some code in the on load for the form, to fill in a couple of dummy data fields so that effectively the form is being edited immediately and not just open, which might be allowing the same record to be open? Again thanks in advance for all the help being offered. I've done plenty of Dbases where the user enters data and pushes it into tables which are then later queried and reported on, but never done one where the data is there and is pushed out to a multi user environment.


Last edited by Zampa; 08-15-2019 at 03:20 AM.
Zampa is offline   Reply With Quote
Reply

Tags
forms , locked record , multi user

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Record Locks xPaul Modules & VBA 7 08-15-2013 10:34 AM
Record Locks (Well... maybe?) gray Tables 2 08-21-2009 02:01 AM
Record Locks (Well... maybe?) gray Tables 0 08-19-2009 03:56 AM
Data Locks/Record Locks? Question Durien512 General 2 02-12-2007 12:01 PM
Record Locks? scouser General 10 04-08-2005 04:14 PM




All times are GMT -8. The time now is 12:57 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World