Subforms are Auto-Locking

[Jason_Rhodes]

I looked and could not find this answered elsewhere. Apologies if it is -- you can point me to the thread if so.

I run a medium sized MS Access 2003 database at a hospital and I have one main form to enter patient information. The different tables of data we collect correspond to a different subform on the main form.

For some reason, out of nowhere, each time we open the form the fields are locked. But they are not literally set to "Locked", but rather when you go to the subform's form properties, "Allow additions", "Allow deletions" and "Allow edits" are all set to "No". I can change them back to yes, but the next time anyone opens the form (the database is stored on a shared network drive), these three settings are set back to "No" for about 5 or 6 of the subforms (not all!).

For now I have created a button with some VB code that sets those back to "Yes" but it's annoying to have to click an "Unlock Form" button everytime you want to enter data. What could be causing the subform form settings to change on their own like that?

Thanks for your help.

Jason

 

[MStef]

It's wery ugly to work in surroundings like that.
I suggest put your code in Sub Form_Load (subform).
In that case you don't need a command button.

 

[Fear Naught]

I suggest that you open each subform in design view individually (seperate from the main form) and ensure that the settings are correct. I have found that sometimes changing properties when a subform is changed from within the main form does not have any effect or the subforom does not save properly.

 

[bliddel]

This thread came closest to the problem I am having.

I'm trying to develop a small database to manage vacation requests. On the initial form, a user selects him/herself from drop-downs for department and name, and clicks a command button. Upon the click event, the user sees another form with personal information (and some extra buttons when the selected user is a manager) on the main form, with a subform of detail records of vacation requests by the selected employee that fall within the specified time period. The user should be able to edit or delete existing requests (most fields, but obviously not the approval flag), or add new records, or close the form and return to the user selection form. Eventually I need to add auto-notification to managers when employees make changes or add requests, but I digress.

If I open the subform directly, in datasheet view, I can edit the fields that I want users to be able to edit, and reset any approval flag, update the record changed date/time, etc. All works perfectly, life is good.

When I open the main form with the exact same subform, using the exact same record source, still in datasheet view, the subform fields all behave as though they are locked. In design view for the subform, the properties lists show these fields are clearly not locked. The main and subforms are not linked with Child/master, because of multiple unrelated selection criteria. I can still delete records.

I thought I was just an idiot, and I might still be, but I haven't seen this symptom anywhere else. I have also seen some other inconsistent behavior, so I begin to suspect database corruption, despite the modest size and newness of the database.

Am I on the right track? Or have I missed some fundamental critical property setting somewhere?

 

[Fear Naught]

I guess that you must be linking the main form and subform somewhere. It is possible that you have a rogue record or indeed a rogue relationship that could be locking the recordset.

If you can post the mdb on here am i am sure that I (or one of the other contributors) will take a look for you.

 

[bliddel]

I created a new database, copied over the relevant objects, and sanitized the employee list. I confirmed the problem is still there.

As you will see, there is no apparent Child/Master link between the "MyRecords" form and the "TimeOutOfOffice" form.

When demonstrating to yourself, you'll get more illustrative results choosing the TIS department, and Bruce Smith as the employee.

I'll appreciate any help. I've wasted way too much time on this already.

 

[boblarson]

Okay, the strange thing is that everything seems to be working fine for me. You aren't using Access 2003 Service Pack 3, by chance, are you?

 

[bliddel]

Wow! Yes, I am using MS Access 2003 SP3 (11.8166.8172). I just installed updates a couple weeks ago.

If you think that the version is the problem, I guarantee I would never have discovered it on my own... I must be using the same version at home also. ... Now comes the obvious question... is there a knowledge-base article I can reference to confirm/resolve this?

 

[boblarson]

I'm guessing that it is SP3 that's causing you issues. I don't have time right now to try to find the info on this (gotta get ready for work). To get back to pre-SP3 you would have to uninstall office completely and reinstall up to SP2.

 

[Fear Naught]

I am getting the same problem with the subform in the MDB that bliddel posted but I am using Access 2003 with SP2. I even created a brand new form and it still happened.

Funny though that I don't get that problem with any databases that I produce!!

There are some anomolies with data relationships in the table structure. I think you need to have some autonumber PKs and link to them rather than your text columns in the Absense Type table however that does not seem to be the source of the problem.

Maybe "Super Moderator" Bob Larson can come up with a solution for you.

 

[bliddel]

Thanks for all the attention. I took this beast home again last night. I'm using Access 2003 SP2 at home, so now at least two of us know first-hand that it isn't SP3 that is causing the problem.

I also created new forms, and based them off similar data, using SP2. I think my subform worked properly until I added a filter to the main form. I need to repeat the experiment and write down each step as I take it, to be sure. But once the subform began to misbehave, locking all the fields instead of only the ones I specified, then nothing that I had time to try would restore the proper behavior. I did not have time to try removing the filter for the main form to see if that would restore proper operation.

Call me "Old School", but I got out of the habit of auto-numbers when they just didn't work reliably in Access 97 and Access 2000. The absence-type table should never grow. The codes listed are unique, and also necessary for exporting to the big system, which I why I selected them for the link. Obviously, with only about a half-dozen codes, I could get by with a single character or even byte for PK. Over time there will be a large number of out-of-office records, so it might be worth doing. I still remember the 1980's when we fought hard to save three bytes per record. But, as you also say, this isn't the cause of the misbehaving form, so it seems lower priority.

I do think there is a learning opportunity here, and what ever we learn is probably worth sharing.

-Bruce

 

[bliddel]

I'm not celebrating yet, because what I found may be a big pain in the rear to circumvent, but here is what I think I have determined:

To lock some but not all fields in a subform in datasheet view, you have to set the Main Form property "Allow Edits" to "Yes" as well as setting the subform property "Locked" = "No" (while in design view for the main form).

It looks as though creating a new main form or editing an existing main form that uses the same subform may sometimes inadvertently set the locked property of the subform to "Yes". I expect, but have not tested, that setting the main form "Allow Edits" to "No" may also set the subform "Locked" to "Yes".

Oh, the things the classes never taught, and the help screens never reveal. This little tidbit just cost me two whole days.

 

[rhett7660]

Do any of the computers have access runtime 2007 installed? or are they all using access 2003. I know there have been issues with 2007 runtime.. don't know if this is one of them thou.... just a thought...

 

[bliddel]

rhett7660: Do any of the computers have access runtime 2007 installed?

Bruce: Speaking only for the ones that I use, no, none of them have runtime 2007. I'm just using Access 2003.

What I thought an hour ago is yet even more restrictive and complicated now. To allow user changes in only some fields on a subform, evidently I have to allow edits=Yes on the main form, set locked = "No" on the subform, and then also set enabled = "no" on each field that I want to prevent changes for, after which I also went in and removed the tab-stops for those fields, so as not to be a tease to users.

In other words, it appears that Microsoft lies when the status bar poses questions such as "Enable Control in form View?" for the enabled property, or "Prevent changes to data in control in form view?" for the Locked property. It seems to me that the locked property for all fields on the subform is getting inherited from the locked property for the main form.

I remember the good old days when computers did what WE told them to do, not necessarily always what Microsoft wanted them to do. Oh well, I've known for years that WYSIWYG is myth, and reality is WYSINAWYG.