Locking Fields

Topic

New Reply

I need to find a way to lock down a field so the people doing the work on an issue will not change their start dates. I am wanting to disable the field after the answer the question whether they want to finalize the date or nor. Below is the code that does not work.

Private Sub Days_AfterUpdate()
Dim response As Integer
response = MsgBox(“Is this value correct?”, vbYesNo)
If response = vbYes Then
Me.Days.Enabled = True
Else
Me.Days.Enabled = False

End If

End Sub

The else part is the thing that does not work. Thanks for the help.

Reply | Quote

Replies

You probably need to move the focus away from the textbox before you disable it (assuming Days is a textbox).

Reply | Quote

Private Sub Days_AfterUpdate()
Dim response As Integer
response = MsgBox(“Is this value correct?”, vbYesNo)
If response = vbYes Then
Me.Days.Enabled = True
Else
Me.Days.Enabled = False
End If
End Sub

Haven’t you got this the wrong way round? If they say yes (It is correct) you should set enabled to false … and leave enabled as true it if they say no…

And I agree with SteveH, you should set the focus to another control before you disable this control.

Reply | Quote

You are right I have it backwards. How would the code change by changing focus? I am still learning how to code in Access.

Reply | Quote

You are right I have it backwards. How would the code change by changing focus? I am still learning how to code in Access.

The Error number is 2164 – You can’t disable a control while it has the focus.

Your code should look something like:

Private Sub Days_AfterUpdate()
Dim response As Integer
response = MsgBox(“Is this value correct?”, vbYesNo)
If response = vbYes Then
Me.SomeOtherControl.SetFocus
Me.Days.Enabled = False
Else
Me.Days.Enabled = True
End If
End Sub

Reply | Quote

It might have been easier just to use the Locked property rather than the Enabled property.
You can set this while a field has the focus, because it just makes it Read Only,
rather than completely disabled.

Code:

Private Sub Days_AfterUpdate()
Dim response As Integer
response = MsgBox("Is this value correct?", vbYesNo)
If response = vbYes Then
    Me!Days.Locked= True
Else
    Me!Days.Locked= False
End If
End Sub

Also when referencing a forms field in Access you should
really use either the Me!FieldName syntax or Me(“FieldName”) syntax rather than Me.FieldName
Although if the field is a control on the form toy can use just FieldName as long as it is the same as the Control name.

e.g. Me!Days.Locked is the same as Days.Locked

It is also worth putting something into the On Current Event to check on existing Records as you change from Row to Row, otherwise once this question is answered on one record, the field will be locked on the next one as well.

Usually an After Update Event such as this that does a form or Field Locking will have an accompanying equivalent on On Current, although omitting the question.
Not sure what the Criteria would be on yours.
Maybe checking for NULL and locking if Not Null.

Indeed IF you need to have Finalised Dates and Non Finalised dates as well, and want to Lock when the Date is Finalised, then you may well need an Additional Field to Flag DaysFinalised (type Yes/No) Default False, and also set that to True after update response is Yes. Then you could use that as the On Current Trigger Locking Criteria.

Reply | Quote

It works and thanks for your help.

Reply | Quote

I am needing more help for I need to lock down 3 fields and I am stuck on the coding. I need them to put a creation date in then lock it down, then expected date and lock the value and later on the closed date and lock that value down. I need it to not allow them to go back and reverse their dates because I need to truly see what the real dates are so they can’t cheat on finishing projects.

Reply | Quote

Have a look at this example.
I have used Locked and conditional formatting to colour cells rather than Enabled.
This is because of my earlier reasons.
Also because on a continuous form a colour or enabled change appears the same on all records within the field
But the conditional formatting by formula is on each value.
I have also added some automatic flagging fields to the table for the 3 fields
so it is easier to know that they have been locked when using on current.
This does allow an entry to be made BUT not agreed and changed later.
Once agreed it becomes locked.
As an alternative strategy, you could just clear the field back to NULL if they say NO, but you would have to move focus
and then reset.
I have added another form with that as the alternative strategy of Clearing data when answer is NO
It has slightly different code and rules for conditional formatting

Reply | Quote

It looks good but the question I have now is if they answer no then they can type any date they want to in there and change it later. I need to make all the fields required but not the closed date because that will be in the future and they wouldn’t know that at the time of entry. I guess I could make all fields required and locked except for the closed and tell them that any date they type in can’t be changed. Any advice would help? I just don’t know how to keep people from messing with the data and cheating. Thanks in advance for your help.

Reply | Quote

I looking back your answer I think the alternative strategy would work if I just just put required on expected days and creation dates but not required the closed date because they wouldn’t know that at the time of entering. Your thoughts.

Reply | Quote

Your strategy seems reasonable to me – if you simply use the field validation rule that those dates are required, then if they fail to complete one of them, you can use the validation message to tell the user that they must enter that date. Once the record is saved you may want to use a second copy of the form that locks those controls so the data cannot be edited. Your data entry version of the form would not have those locked.

Reply | Quote