How to conditionally Lock data fields in Access forms using VBA

[kobamfo]

Hi Guys,
I am developing a solution for Document Management in my Organization; Now I want a particular field named "testsub" to be Locked once another field named "subject_dl " is not null, otherwise, the field testsub should be unlocked for data entry.
So I had tried a code in the "On_Current " Event of the Form Named "outfrm" Example:

If Is Not Null(subject_dl.Value) Then
   Me.testsub.Locked = True
       Else: Me.testsub.Locked = False
       End If

But this has'nt helped me at all. Any help please :-(

[twinnyfo]

kobamfo,

You should put that code in the AfterUpdate event of the subject_dl field. This way, every time that field is updated, it checks the value and locks/unlocks the testsub field.

[Seth Schrock]

Actually, you need it in both the form's OnCurrent and subject_dl's AfterUpdate events. This will make it so that when you move between records it will check to see if subject_dl is null and act accordingly and when you change subject_dl it will check it. What I often do is make a function in the form's VBA code that has the code you want. I then call the function from both events. This makes it so that if I ever want to make a change to what is being checked, I only have to make the change one place.

[kobamfo]

Guys, thank you for your quick responses but Seth, could you help me with your function code? It seems to me the function approach is a "neat job". But I have tried your "On_Current " and "After_Upda te" suggestions with problems: Compiler error "Sub or function not defined" with "NotNull" highlighted.
I must admit that my VBA is below average :-)
Here is the Code i used:
[If NotNull(subject _dl.Value) Then
Me.testsub.Lock ed = True
Else: Me.testsub.Lock ed = False
End If]

[twinnyfo]

Good catch, Seth! I was just thinking about the current record.

kobamfo,
the correct function is

If Not IsNull(subject_dl.Value) Then....

[kobamfo]

Great!
The Code below did the magic on the After_Update Event of the "subject_dl "

If IsNull(subject_dl.Value) Then
   Me.testsub.Locked = False
       Else: Me.testsub.Locked = True
       End If

Very Glad this is working just as I want it. Thank you Guys

[Seth Schrock]

Please remember to use the <CODE/> button when posting code.

I missed that error in your code. Replace "NotNull" with "Not IsNull"

[kobamfo]

twinnyfo, I have reverted to use your correction since that is the right way. I appreciate your help.

[NeoPa]

As Seth so cleverly indicated, what you really need is the code encapsulated in a separate procedure which is called by both the subject_dl_Afte rUpdate() and the Form_Current() event procedures.

To illustrate this, as you say your coding skills are at an early stage, try :

Private Sub Form_Current()
    Call LockTestsub()
End Sub

Private Sub subject_dl_AfterUpdate()
    Call LockTestsub()
End Sub

Private Sub LockTestsub()
    With Me
        .testsub.Locked = (Not IsNull(.subject_dl))
    End With
End Sub

[TheSmileyCoder]

Note that you can also call an event from within another event. I sometimes call the Form_Current from within the Form_AfterUpdat e event. The best approach will depend on the individual circumstances.

[NeoPa]

That's certainly true Smiley, but event procedures are designed to be triggered by events. I always advise creating a separate procedure so that it's clear that the code is not only being invoked from the Access event, but also from elsewhere, which can be imprortant in some circumstances, and helps anyone maintaining the code to appreciate better what goes on where and when. As such I make it a rule never to call event procedures from my code, and advise others to do likewise.

[twinnyfo]

Yes... one can get into endless loops by accidentally calling an event procedure which refers back to itself. Been there... Done that... Good recommendation Neo!

[TheSmileyCoder]

@NeoPa
I do see your point.

I feel on the other hand that the code I have is appropriately placed in the OnCurrent, and having it directly there is (for me) the logical place, as opposed to creating a separate sub-rutine.

Most of my applications have a difference in functionality for new records and already existing records. I find it simpler to call the OnCurrent event (again) as the record is updated.

[NeoPa]

As long as the reasoning has been outlined and understood (as it has been) then readers are in a position to make up their own minds. I'm quite happy that the different perspectives have been clearly expressed :-)

[kobamfo]

And my understanding has only improved just by listening in on your discussions. Actually this Post was part of an attempt to prevent storage of redundant data in several tables other than the foreign keys which make the joins happen between tables.