Use Yes/No box to remove editing ability for an entire record (1 Viewer)

Jennesa

Registered User.
Local time
Today, 17:54
Joined
Jun 24, 2016
Messages
23
I have a large table I am working on for work, I am very familiar with Microsoft Access but I am a novice with programming. I need to find a way for someone in the table to check mark a box called "No Work" that will make the entire record unable to be edited. (I also need a fail safe that an administrator would be able to go in and change this if it gets checked on accident).

We have "Company", "Address", "Contact", "Phone Number", etc. and if it is a customer we no longer wish to work for I need something to automatically run when you open the database table that will not allow the records checked yes to be edited. If any one can help me write a code (as well as how to use it in Access I would be extremely grateful) Please let me know if you have any questions from me that could help, thank you!
 

Jennesa

Registered User.
Local time
Today, 17:54
Joined
Jun 24, 2016
Messages
23
Is there any way at all to do something like this in the table? I can create users and passwords if there is an easy way to remove the editing ability from the developer access level. It might even be better for me to do it that if it is plausible. Thank you for your help!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:54
Joined
Aug 30, 2003
Messages
36,118
To my knowledge, no. I think the old security could keep them out of tables, but not individual record editing. I could be wrong. Most of us never let users see tables or queries anyway, just forms and reports.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:54
Joined
Feb 28, 2001
Messages
27,001
If your users can see the raw tables (i.e. your Navigation Pane is open), you cannot block edit abilities, PERIOD. Only a form can impose this kind of security in Access.
 

Jennesa

Registered User.
Local time
Today, 17:54
Joined
Jun 24, 2016
Messages
23
Okay, so I found a form that I can use and it looks just like the table. Now my question is where and how should I type this code in order to keep the someone from editing that record (I was able to code a form that creates a log in screen when you open up the database). For instance, I have a No work check box, when checked it should automatically remove the editing ability of that full record. I have tried codes such as listed below:

If No_Work = "Yes" Then
Me.No_Work.AllowEdits = False

End If

I have also tried .Locked and .Enabled; however, when I try to test it out it does not do what I had hoped for.

If it helps I am using Access 2013. Thank you!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:54
Joined
Feb 28, 2001
Messages
27,001
If your form looks just like the table, are you using a multi-record (a.k.a. continuous) form somewhat similar to a datasheet form? That really doesn't work very well because that won't treat individual records similarly. If you want to block one record and allow edits to other records, you need a single-record form.

You MIGHT do as well to use the Form Wizard to build a form for this table, then go back in and build a Form_Current event (you can look this up online or in this forum easily enough). In the Form_Current event, you would include

Code:
    Me.AllowEdits = Not No_Work

among any other tasks needed when the form first loads. This would lock the (single-record) form for all editing but would still allow navigation. Be aware the forms generated through the Form Wizard are rudimentary and probably need a lot of tailoring. The wizards by themselves are dumber than a box of rusted garden tools, but they DO make decent scaffolding for any code upgrades you might wish to add. It is in that latter application that the wizards are invaluable.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:54
Joined
Aug 30, 2003
Messages
36,118
Doc's method should work, but so you know what was going on, you wouldn't want quotes around Yes. Any of these should work:

If No_Work = Yes Then
If No_Work = True Then
If No_Work = -1 Then

You'd also want an Else to turn editing off when appropriate. Doc's method also accounts for that. Code in the current event should work fine for continuous or datasheet views.
 

Jennesa

Registered User.
Local time
Today, 17:54
Joined
Jun 24, 2016
Messages
23
Awesome! I created a new type of form and AllowEdits was available. I have set this on my detail view under On Got Focus.

If Me.No_Work = True Then
Me.AllowEdits = False
Else
Me.AllowEdits = True
End If

And when I tested it out it worked! Thank you all so much for all of your help it is very much appreciated!

Now the problem I am running into seems to be a kind of loophole. Oddly enough if I click on something in the record that is check No Work it does not allow me to edit (Yay!) but if I select a different line then go back it does allow me to edit it again.. Any last suggestions to fix this? Thank you again!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:54
Joined
Aug 30, 2003
Messages
36,118
You want the code in two places, the after update event of the checkbox, and the current event of the form. The first handles when you change the setting, the second when you change records.
 

Users who are viewing this thread

Top Bottom