Lock and un-lock form based on a control event.

Hi,

I have an Access database of employee records.  There is a form where the user will enter a social security number.  The after update event of that box will cause a macro to run which will fill in some of the fields on the form and on a sub form.  What I’m having trouble with is the following:  Upon opening the form, I want the sub form to be locked so the user can’t enter data in it until after a SSN has been entered on the main form.  After a SSN has been entered, I want the sub form to unlock and the SSN field to become locked so the user can’t change it again.  How do I do this?

Thanks,
Bob
bastille2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shanesuebsahakarnCommented:
That's fairly straightforwards. In the OnOpen (or better yet, in the OnCurrent) event, put this:

Me!My
Me!MySubformControl.Enabled=False

In the AfterUpdate event of the textbox where the SSN is entered, put:
Me!MySubformControl.Enabled=True
Me!txtMySSNTextbox.Locked=True

MySubformControl is the name of the control where the subform is held, and txtMySSNTextbox is the name of the SSN textbox (rather obviously :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NestorioCommented:
Private Sub txtSSN_AfterUpdate()
Form_sfrm.AllowEdits = False
' Run Macro
Form_sfrm.AllowEdits = True
End Sub
0
NestorioCommented:
Correction:

Private Sub txtSSN_AfterUpdate()
Form_sfrm.AllowEdits = True
' Run Macro
Form_sfrm.AllowEdits = False
End Sub
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

bastille2Author Commented:
shanesuebsahakarn,

In your example you use both enabled and locked.  My understanding is that locked will keep the item visible but will not allow edits, enabled will grey it out.  I would like no change to the appearance of things, just allow/prevent changes.  Can I use locked for both field and subform?
0
bastille2Author Commented:
Sorry, forgot something...I'm not sure of the difference between OnOpen and OnCurrent.  Could you point me to a list of event definitions?
0
shanesuebsahakarnCommented:
OnOpen only fires once (when the form is opened), but OnCurrent fires every time you move onto another record. You can certainly use Locked for both the field and the subform if you want, it will achieve what you need.
0
bastille2Author Commented:
shanesuebsahakarn,

Whoo Hooo!  Worked like a charm.  Pleasure doing business with you.

Bob
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.