Form is all tangled up ... I'm trying to lock only specific controls ... VBA is missing something

[catluvr]

If this should be in the Forms sub-forum, let me know!



My daughter is creating a database at work and she's asked me for some assistance.

She was quite proud of the "Add", "Save", and "Find" buttons that she made all by herself ... (she's 21, she gets excited about this stuff!). I showed her a "slicker" way of doing a find ... with dependent combo boxes. She was thrilled with those.

All was going fine until she asked if she could lock a record on the form so she didn't accidentally overwrite information. She didn't want this to be transient ... she wanted to be able to mark a record as "locked" and have that record always come up as locked. I can do that, no problem, except, when a record is locked, the combo boxes are locked, too ... which makes sense, but I don't want those to be locked!

I did some poking around and found Allen Browne's code at http://allenbrowne.com/ser-56.html

However, his supposition is that ALL records are locked and the user only unlocks the one they are currently working on. I want the records marked "Locked" to load in the form locked, except for the Find combo boxes. Then, she can use the "Lock/Unlock" button to change the setting. If a record is not marked "Locked", then it comes up a editable and all controls work.

I ran into a few hitches ... I tried inserting this code into the "OnCurrent" event of the form:

=IIf([CHKLocked]=True,LockBoundControls([Form],True),LockBoundControls([Form],False))

It kinda sorta worked ... as the records are paged though, the locked records have a red box around them and they are locked, the combo boxes don't work. The unlocked records are, indeed, unlocked, and sometimes the combo boxes worked ... dependent on whether the record is in the edit or select mode.

Also, the records start to come up in the edit mode, meaning the little pencil is showing on the left side of the form. The only way to move from an unlocked record to the next record is to click the undo button, so the select triangle is showing instead.

I did try to modify my original locking code, which was also in the "OnCurrent" event ... it's still there, but parts are commented out. It works without using Allen Browne's code, but didn't leave the controls unlocked that I wanted. When I add in the references to Mr. Browne's code, I get the same issues ... combo boxes not working, records coming up in edit mode, meaning even if the combo box works for selection, it won't go to the correct record because the current record appears to have had changes ... the "undo" button has to be clicked a few times.

I'm sure I could list every single control and lock/unlock them, but if she adds a control, she'd have to update the VBA code. This is a stripped down version of what she's working on, with dummy data.

I also have four other simple questions ... at least I think they are simple ... if the form is marked allowEdits.false, why won't listing a specific control after that with the lock property as false keep that control operational?

And, I've never seen the captioning property done the way he does it ... how does the ampersand work?

frm.cmdLockUnlock.Caption = IIf(bLock, "Un&lock", "&Lock")

At the beginning of Mr. Browne's code, it forces the form to save any unsaved data. if that is working properly, then why does, say, selecting a new record cause an error saying the current record can't be saved?

The AutoID is an autonumber. I have no earthly idea why it went from two digits to 7 digits ...any ideas? She did not add eight million records and then delete them! Does this matter?

I've attached my accdb file, since I think it would be more helpful to see all the other properties.

Okay, so I was going to attach my file, but it's 20 kb over the limit. Sigh ... do you do links??

Wait ... duh ... I can zip it ...

It's a lot, I know!

You folks are wonderful!

Susie
Kansas

[moke123]

I'm wondering if Allens code is overkill for what you want.
It seems to me you could accomplish the same thing using the tag property of those controls you want to lock and unlock.

[isladogs]

Following on from Moke's reply, have a look at the example app which demonstrates how the tag property can be used.
See http://www.mendipdatasystems.co.uk/s...ols/4594398114

[davegri]

Along with others above, this uses tag property. Very large reduction in code lines from original...

catluvr-davegri-v01.zip

[ssanfu]

Here is another set of changes (my changes only). I left the Module1 code alone, but I think it is overkill. I haven't looked at davegri's modifications.....

This version fixes the Red box and the edited (pencil) appearing. I did mess up the OnCurrent code.

No, the autonumber number doesn't matter.


AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.



But it is easy to fix. Delete the "WidgetAutoID", then add it back in to the table.


I modified other parts of the code, so you should compare this code with the original code you posted to see the changes.

[catluvr]

You folks are AWESOME!

TAGS! As soon as I read that in moke123's reply, I recalled someone else telling me that my code (for another database) was too cumbersome and I should use tags! I didn't, but I remembered it!

I looked at the files from both davegri and ssnafu and I think I have a a good handle on things!

Thank you, Thank you!

Susie
Kansas