Record locks on a form (1 Viewer)

Zampa

New member
Local time
Today, 15:41
Joined
May 21, 2019
Messages
8
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:

Ranman256

Well-known member
Local time
Today, 11:41
Joined
Apr 9, 2015
Messages
4,339
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.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:41
Joined
Jul 9, 2003
Messages
16,245
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.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:41
Joined
Jul 9, 2003
Messages
16,245
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.
 
Last edited:

Zampa

New member
Local time
Today, 15:41
Joined
May 21, 2019
Messages
8
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?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:41
Joined
Jul 9, 2003
Messages
16,245
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:41
Joined
Feb 19, 2002
Messages
42,988
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.
 

Zampa

New member
Local time
Today, 15:41
Joined
May 21, 2019
Messages
8
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:41
Joined
Sep 21, 2011
Messages
14,056
You should likely be only giving them an accde FE and not an accdb FE ?
 

Zampa

New member
Local time
Today, 15:41
Joined
May 21, 2019
Messages
8
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:

Users who are viewing this thread

Top Bottom