Locking a field after data entry (1 Viewer)

[Glen]

I have a Project tracking database in MS Access 97. I want to add a field to contain a "Baseline" date for the planned completion of the project. Once this date has been entered I want to lock it so it cannot be altered, although I still want it to be visible to all on the form.
I thought it would be quite simple using the "Form_Current" event to test if the field was null or not. If null then lock it. Unfortunately it seems to lock the field on ALL records regardless of whether there is a date in it or not (blank). I would greatly appreciate any help........The code I am using is shown below.......


Private Sub Form_Current()
Dim strBaselineDate As String

strBaselineDate = IIf(Not IsNull(Me.txtBaseline), Me.txtBaseline, "NULL")

If strBaselineDate <> "NULL" Then
Me.txtBaseline.Enabled = False
Me.txtBaseline.Locked = True
End If

End Sub

 

[Abby N]

Hello Glen. I think your problem is being caused by zero length strings (""). They have no characters but are not considered Null. The code below should do it.

Private Sub Form_Current()
Dim bolBaselineDate As Boolean

bolBaselineDate = IIf(Nz(Me.txtBaselineDate, "") = "", False, True)

If bolBaselineDate = True Then
Me.Name_Of_Next_Control.SetFocus
Me.txtBaselineDate.Enabled = False
Me.txtBaselineDate.Locked = True
Else
Me.txtBaselineDate.Enabled = True
Me.txtBaselineDate.Locked = False
End If
End Sub

A couple notes about the above code. First, you can see I changed the data type from ‘String’ to ‘Boolean’. I did this because Boolean variables require less memory than strings. Next, in the IIf statement, I use the Nz function to convert Null values to zero length stings. So now, if txtBaselineDate is Null or a zero length string, then bolBaselineDate is False. But, if txtBaselineDate has any value, bolBaselineDate is True. Next, in the second line of the block If you can see the focus is set to another control. (You’ll have to change it to the name of a control on your form, of course.) I did this because you can not change the Enabled or Locked properties of a control while you’re in it. Lastly, I added an else clause to enable the control when you move to a record where txtBaselineDate is empty.

You may also want to use this code in txtBaselineDate’s ‘AfterUpdate’ event. That will cause the control to lock down as soon as you exit it. The ‘OnCurrent’ event occurs when you move between records. So, if you code only the ‘OnCurrent’ event, the field won’t lock until you leave the record. I apologize if this post is a little brusque. I’m still on my first cup of coffee. Anyway, I hope this helps. Good luck.

~Abby

 

[Glen]

Abby, You can be as brusque as you like - it works great and your help is very much appreciated........Thanks, Glen