Lock/unlock text box to avoid accidental data change

[Chintsapete]

Hi all
I just thought I'll post this, maybe someone else might need it one day. It took me a long time to get it right and I couldn't have done it without vbaInet.
What it does. In a form it locks a text box after update and unlocks it on double click and selects the text box content up to 25 characters for edit. Therefore one avoids accidental data change. (In this example it runs 2 queries first)
With the extra tweak On got focus it unlocks the text boxes which are 0. I had to do this because I do go a day backward sometimes to change something and than it locks the field in the current record. There might be a more elegant way doing the same thing but it works.

After update

Private Sub Morn_AfterUpdate()
DoCmd.RunCommand acCmdRefresh
    DoCmd.OpenQuery "CB01 Morning shift D", acViewNormal, acEdit
    DoCmd.OpenQuery "CB01 Morning shift", acViewNormal, acEdit
    DoCmd.RunCommand acCmdRefresh
With Me
    If Me.NewRecord Then
        .Morn.Locked = False
        
    Else
        .Morn.Locked = (Len(.Morn & vbNullString) > 0)
        
   End If
End With
End Sub

On got focus

Private Sub Morn_GotFocus()
With Me
    If Me.Morn = 0 Then
        .Morn.Locked = False
        .Morn.SelStart = 0
        .Morn.SelLength = 25
    End If
End With
End Sub

On Double click

Private Sub Morn_DblClick(Cancel As Integer)
  Cancel = True
    With Me.Morn
        .Locked = False
        .SelStart = 0
        .SelLength = 25
    End With
End Sub

 

[vbaInet]

It would have been nice to have the Private Sub and End Sub within the Code blocks. Your call anyway, but in the future please enclose those in code tags as well.

 

[Chintsapete]

It's all a learning curve, but now I know what the code tags are for. Looks good I think. Thank you for tutoring.

 

[Chintsapete]

I did realize that the first code posted works, but if one is to work a day back or a previous record and enters data in a field it locks the fields in today's record as well.
I changed the code, so that a field which is 0 doesn't get locked. Works better for me. See below.

Private Sub Morn_AfterUpdate()
DoCmd.RunCommand acCmdRefresh
    DoCmd.OpenQuery "CB01 Morning shift D", acViewNormal, acEdit
    DoCmd.OpenQuery "CB01 Morning shift", acViewNormal, acEdit
    DoCmd.RunCommand acCmdRefresh
With Me
    If Me.Morn = 0 Then
        .Morn.Locked = False
        
    Else
        .Morn.Locked = (Len(.Morn & vbNullString) > 0)
        
   End If
End With
End Sub


Private Sub Morn_DblClick(Cancel As Integer)
 Cancel = True
    With Me.Morn
        .Locked = False
        .SelStart = 0
        .SelLength = 25
    End With
End Sub