Turn on/off Form Edit mode. (1 Viewer)

GregoryWest

Registered User.
Local time
Today, 09:34
Joined
Apr 13, 2014
Messages
161
Here is a fun one, that comes out of an end user request:


What the user would like is a check box, or something, that will allow them to turn on the ability to edit a form. The normal state of this box would be false (ie Read Only). The reason for this is pretty simple, they are in the program looking up data a lot, with no need to edit. Now with editing on, there have been a number of times when something was accidentally changed. This causes extra work and time to find and correct. Now at the same time they are looking up data, and notice an error. Instead of stopping what they are doing going to another screen and fixing the error, they would like to be able to turn on editing, fix the error and carry on as if nothing happened.


What I thought of doing is putting the field 'form.allow-editing' (or what ever it might be called) up in the header of the form. That way the user can just click on the box and presto editing is now on.


Can something like this be done?
What would be the name of the control field.
I do have other ways of doing this, but would require a global variable to be made available. If there such a thing in Access? How do I create it and assign it values?
 

isladogs

MVP / VIP
Local time
Today, 14:34
Joined
Jan 14, 2017
Messages
18,186
Its easy enough to lock the form using a checkbox but then you can't unlock the form again as the checkbox is locked.

Easy to do with a command button however. See attached
A toggle button would probably work as well
 

Attachments

  • TestLock.zip
    22 KB · Views: 213

GregoryWest

Registered User.
Local time
Today, 09:34
Joined
Apr 13, 2014
Messages
161
DOH!! Forgot about the form locking the form lock button. Guess back to something like a global variable. Is there such a thing in Access? Just need it floating around while the program is running, then do not care about it.


Just looked at your code. Very ingenious! Question is the field 'Allow Edits' local to each and every form it is contained in? ie Could I have the same code in a form, and a subform. Each controlling their individual fields?
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 14:34
Joined
Jan 14, 2017
Messages
18,186
Did you look at my example .... that does work!

Global variable do exist but aren't needed here
 

isladogs

MVP / VIP
Local time
Today, 14:34
Joined
Jan 14, 2017
Messages
18,186
In answer to the extra points you added after my last reply....

AllowEdits is a form property and I've not saved the value anywhere.
That means it just applies to the form where the button is located.
If you unlock the form, the subform stays locked and vice versa.
That could cause some potential issues depending on how your form/subform combination are setup
However you could adapt the code to unlock both together if you wish.

Or you could add a similar button and code to the subform to manage that independently.

If you wanted to lock the entire database, that would be possible with a global variable but I wouldn't recommend it, especially if this is a multi user application
 

GregoryWest

Registered User.
Local time
Today, 09:34
Joined
Apr 13, 2014
Messages
161
Got it. Implemented your idea, now have a very odd problem. When I go onto the form with the locking enabled, the form is locked and the correct background coloir is there (light grey in my case). Problem is none of the fields or sub-reports are visible. If in Access I go into 'Form design' with the form open, then back to normal view everything shows up normally and the button works as expected.


I have never had this happen to me before, any idea what might be causing it?
Here is the slightly revised code I am using:
Option Compare Database
Option Explicit

Private Sub cmdEdit_Click()
If Me.cmdEdit.Caption = "Allow Edits" Then
Me.cmdEdit.Caption = "Lock Form"
Me.AllowEdits = True
Me.Section(acDetail).BackColor = RGB(90, 255, 255)
Else
Me.cmdEdit.Caption = "Allow Edits"
Me.AllowEdits = False
Me.Section(acDetail).BackColor = RGB(220, 220, 220)
End If

End Sub

Private Sub Form_Load()
Me.AllowEdits = False
Me.cmdEdit.Caption = "Allow Edits"
Me.Section(acDetail).BackColor = RGB(220, 220, 220)
End Sub
 

isladogs

MVP / VIP
Local time
Today, 14:34
Joined
Jan 14, 2017
Messages
18,186
Sounds odd. Nothing obvious I can think of.
Can you upload a stripped down copy of your database for someone to look at
 

GregoryWest

Registered User.
Local time
Today, 09:34
Joined
Apr 13, 2014
Messages
161
Not sure if I can do that. The program is dealing with government data that is sensitive. It contains names and addresses, so you can imagine the loops you have to jump through just to get access.


Let me keep working on it and see if I can find the root cause. I am guessing has to have something to do with the background.
 

isladogs

MVP / VIP
Local time
Today, 14:34
Joined
Jan 14, 2017
Messages
18,186
Perhaps you could replace all data with a few dummy records?
We only need to see the form causing you grief, not the whole database
 

Dreamweaver

Well-known member
Local time
Today, 14:34
Joined
Nov 28, 2005
Messages
2,466
What about just taging the record when found so a manager/Etc can do the edits That would only need a button with an extra field.
 

GregoryWest

Registered User.
Local time
Today, 09:34
Joined
Apr 13, 2014
Messages
161
Perhaps you could replace all data with a few dummy records?
We only need to see the form causing you grief, not the whole database


Here is a file!
 

Attachments

  • Copy.zip
    375.1 KB · Views: 122

isladogs

MVP / VIP
Local time
Today, 14:34
Joined
Jan 14, 2017
Messages
18,186
I've spent half an hour or so but failed to fix it

When I open your Roll-Legal form on its own I can see the controls but its permanently locked. The 'sub-sub form' is editable

When opened as a subform, I can't see the controls in the subform
The main form is editable

Unless I'm missing something, I can only assume your form(s) is/are corrupted or there's an issue with your record source ...or both. I suggest you make a fresh form and see if that solves it.

Does it work if you remove all the lock / unlock code?

FWIW I've attached a modified version of my example where the form being locked is a subform & I've added colour changes like yours. It works!
 

Attachments

  • TestLock_v2.zip
    29.5 KB · Views: 110

GregoryWest

Registered User.
Local time
Today, 09:34
Joined
Apr 13, 2014
Messages
161
You are right. Now click on one of the control buttons at the top. Owner would be a good one, the two sub-forms become totally grey.
 

isladogs

MVP / VIP
Local time
Today, 14:34
Joined
Jan 14, 2017
Messages
18,186
Yes I can see that. What I was saying was I couldn't see why it happens.

However looking at it again I've just spotted the problem.
Its nothing to do with the lock/unlock form code

The query 'Roll' used as your subform recordsource has been set as Dynaset (Inconsistent Updates) in order to make it editable. This is presumably needed as you haven't linked the 2 tables used using a PK field

So your subform also needs to use Dynaset (Inconsistent Updates)
Once that is done it works.
You will need to do the same on other subforms loaded when you click the main form buttons

However that recordset type should be avoided where its not essential

Do you have table where RollLiknID is the PK field? If so try including that as a junction table in your Roll query then reverting to Dynaset in both querry & form. Does that work?

Also:
1. Each table needs a PK field - Control has none - there may be others
2. Suggest you use a naming system to make it obvious what each object is e.g. tblControl, qryRoll, frmMainMenu etc
 

Attachments

  • Copy_v2.zip
    367.9 KB · Views: 125
Last edited:

GregoryWest

Registered User.
Local time
Today, 09:34
Joined
Apr 13, 2014
Messages
161
Unfortunately the RollID field is not unique in any of the tables. Most of the tables, other than a couple import/control tables all have ID as an autonumber for the PK. Control is a necessary evil as I do need to store a couple user dependent variables across sessions. These are thrown into Control just for safe keeping.


As for dynaset references, yup just put in in order to allow for table editing. Will only get worse in the next phase where main tables are moved out of access into PostgreSQL corporate DB.
 

GregoryWest

Registered User.
Local time
Today, 09:34
Joined
Apr 13, 2014
Messages
161
Looks like your solution is working good for me also!!! Can not heap enough thanks on you for your insights!
 

isladogs

MVP / VIP
Local time
Today, 14:34
Joined
Jan 14, 2017
Messages
18,186
You're welcome.

Suggest you modify your table structure to avoid the need for kludges like inconsistent updates before you even think of moving to PostgreSQL or similar.
 

Users who are viewing this thread

Top Bottom