How to lock records to prevent user editing?

Good morning! I'm building a database for use by four technicians in a call-center type operation. The call records are purely transactional in nature -- once the call has been logged, the record is closed. If the customer calls again, a new record is opened.

The supervisor doesn't want the techs to be able to go back and edit a record once it's entered -- the records are for quality-control purposes and he doesn't want them gaming the system by going back and changing things after the initial call is logged (let's not discuss the issue of employee psychology, motivation, Office Space, etc.). So I'm trying to figure out how to do that; they all have wheel mice, and I can currently see all the records as I scroll back and forward with my mouse wheel, so I'm assuming they'll be able to, as well.

Any help?

Thanks,
Andreas

Just off the top of my head...are you going to have User or Group Level security?

If so, create 3 forms.
The one they use to create records is set to data entry. The form that allows them to reveiw a record is read only. The form that allows updates to records they don't have permissions to open.

I usually use 2 buttons on my Menu..... One to open the form to add an entry...The other to view records....... (opening the same form two different ways)
On the onClick event of the button to only view the records it would be like this......

On the button to open the form to add data it would be........


I also usually make myself an Admin frontend without the "Read Only" feature... So if needed only I can edit the record.

Thanks for those replies; they were very helpful!

I've never split a database into a FE/BE configuration, but I'll probably be doing that here. Is it possible to create two front-ends -- an admin FE and a user FE?

Thanks,
Andreas

Hmmm... okay, I added group-level permissions to the database, and that's working well, and I split the database, and that's working well, but...

Now when I open up any other Access database on my machine, it asks me for the logon and password! And if I don't enter the permissions for the specific database I was working on, I can't get into databases that never even had group-level permissions applied to them! So, am I not understanding the nature of these permissions? I thought they were specific to the database, but is it a case of them being specific to the copy of Access on my machine? I just don't get it.

Thanks,
Andreas

This might not be exactly what you want... but it's what I do to control previous information from being edited.

Under the form's "On Current" event, try something like the following code.

Private Sub Form_Current()
'If a record exists (meaning it has a VidID) then the information is locked and colored yellow (uneditable)
If IsNull(VidID) Then
Me.VidNum.Locked = False
Me.VidNum.BackColor = -2147483643
Me.VidTitle.Locked = False
Me.VidTitle.BackColor = -2147483643
Me.VidTime.Locked = False
Me.VidTime.BackColor = -2147483643
Me.VidDescription.Locked = False
Me.VidDescription.BackColor = -2147483643
Else
Me.VidNum.Locked = True
Me.VidNum.BackColor = 8454143
Me.VidTitle.Locked = True
Me.VidTitle.BackColor = 8454143
Me.VidTime.Locked = True
Me.VidTime.BackColor = 8454143
Me.VidDescription.Locked = True
Me.VidDescription.BackColor = 8454143
End If
End Sub

VidID is the Primary Key, all entries that are currently existing must have a primary key entered. I used this to determine if the information was "old" or "new".

If the primary key is null (meaning it has no primary key yet, and must be a new entry) then the fields are colored yellow, and are unlocked.

If the primary key exists (meaning it was an old entered record) then all of the fields are colored yellow, and locked.

The On Current event is run everytime the record changes, so if you click "next record" "previous record" or mouse wheel back and forth, this code is run everytime the record switches.

Hope this is helpful!

Sorry for the double post, but to answer your FE/BE question, yes it is possible to make as many front ends as you wish.

Once you split the database, the tables only are in the BE, and the FE links to the tables. You can create a copy of the FE, and name it adminFE or something similar, and edit it appropriately for the admin users.

I usually make a new database file and create "Link Tables" to the back end, and then create the forms from scratch again, this is another way.

Once again, hope this information is helpful.

That's good stuff, Dev! Thanks for sharing! I think I was able to figure out my issues around employee access using the splitter, but I'm going to tuck away your code for future reference.

Thanks,
Andreas

Hmmm... this split database is extremely sloooooow... even though the FE's and BE are stored in the same folder at the moment. I wonder how this is going to fare when I put the BE on a network folder and the FE's on individual machines.

Hey,

Sorry for the late reply, I'm not at work as much as I used to be. I am running it off of a network drive and it tends to be slow when more than one user is accessing the same table at a time.

I have not found a good way around this yet. If you're running really slow and it's not on a network drive that could either be the machine being slow. One way to try and help is to do a "Compact and Repair".

This can be found under Tools -> Database Utilities -> Compact and Repair.

This is the only thing I can think of, hopefully it is of some help. Glad you found the earlier code useful!

when you use the fe/be properly it wont be slow

you should not put the fe on the network. each user needs there own copy on their own pc. otherwise the data/images etc etc have to be fetched onto their machine for use, which is causing your slow performance.

when only the be data file is on the network, this is not so much of a problem, although sometimes you will need to use some more advanced techniques to limit the quantity of data that has to be fetched to your pc, from the back end

gemma-the-husky said:

when you use the fe/be properly it wont be slow

you should not put the fe on the network. each user needs there own copy on their own pc. otherwise the data/images etc etc have to be fetched onto their machine for use, which is causing your slow performance.

when only the be data file is on the network, this is not so much of a problem, although sometimes you will need to use some more advanced techniques to limit the quantity of data that has to be fetched to your pc, from the back end

Click to expand...

Hmmm... it's even slow with the FE and BE sitting in the same folder here on my PC. Maybe something about the way I have built the database is the problem. There are only two tables, and one is bound directly to a form (the FE form for the technicians), and there are only three reports. It isn't that big or complex, yet it's running quite slowly. My machine has 2Gb RAM and a 3GHz processor, so it's a pretty good machine. I can't imagine that a simple database should run so slowly.

I've attached the database in a ZIP file (it's only populated with made-up data anyway), in case anyone wants to poke around in its innards and see if I've caused a problem somewhere.

Thanks,
Andreas