Lock Fields if Another Field is Not Null

[PSSMargaret]

I have a form that the user should only enter one of three scenarios.



Enter data in Textbox 1 and Textbox 2 or
Enter data in Textbox 3 or
Enter data in Textbox 4

If data is entered into Textbox 1 and Textbox 2, I would like textboxes 3 and 4 to be locked.
If data is entered into Textbox 3, I would like textboxes 1, 2 and 4 to be locked
If data is entered into Textbox 4, I would like textboxes 1, 2, and 3 to be locked.

I implemented this one step at a time with the below code and haven't gotten past the first step. While it does work by locking Textbox 1 when I populate Textbox 3, if I remove the data from Textbox 3, it never unlocks Textbox 1.

I also tried it differently by creating a function and then calling it in the Textbox3_AfterUpdate event and the Form_Current event but it never seems to unlock Textbox 1 if I populate Textbox 3.

Any guidance on how to implement this would be greatly appreciated.

Private Sub Form_Current()


    If Not IsNull(Me.Textbox3) Then
        Me.Textbox1.Locked = True
    Else
        Me.Textbox1.Locked = False
    End If


End Sub

Private Sub Textbox3_AfterUpdate()
    
    If Not IsNull(Me.Textbox3) Then
        Me.Textbox1.Locked = True
    Else
        Me.Textbox1.Locked = False
    End If
    
End Sub

[Bulzie]

Try:

Private Sub Textbox3_AfterUpdate()

If IsNull(Me.Textbox3) Or Me.Textbox3 = "" Then

Me.Textbox1.Locked = False

Else

Me.Textbox1.Locked = True

End If

End Sub

Put a breakpoint at the If line and step through, make sure it is doing what you want it to do based on the data in that text box.

[davegri]

Add this sub:

private sub subBoxes(arg as long)
    txtbox1.locked = false
    txtbox2.locked = false
    txtbox3.locked = false
    txtbox4.locked = false    
    select case arg
        case 1
            if not isnull(txtbox2) then
                txtbox3.locked = true
                txtbox4.locked = true
            endif    
        case 2
            if not isnull(txtbox1) then
                txtbox3.locked = true
                txtbox4.locked = true
            endif
        case 3
            txtbox1.locked = true
            txtbox2.locked = true
            txtbox4.locked = true    
        case 4
            txtbox1.locked = true
            txtbox2.locked = true
            txtbox3.locked = true
    end select
end sub

Then in the after_update events:
for txtbox1: If not isnull(txtbox1) then call subBoxes(1)
for txtbox2: If not isnull(txtBox2) then call subBoxes(2)
and so on.

Instead of the after_update event you may have to use the lost_focus event in case the textbox is entered and then exited without any change. In that case the after_update event will not fire.

[jwhite]

A variation to consider. In the AfterUpdate event of each textbox, call this private sub to control locking/unlocking.

Private Sub LockStatus ()
    If Len(Me.Textbox1 & vbNullString) <> 0 And Len(Me.Textbox2 & vbNullString) <> 0 Then
        Me.Textbox3.Locked = True
        Me.Textbox4.Locked = True
    Else
        Me.Textbox3.Locked = False
        Me.Textbox4.Locked = False
        
        If Len(Me.Textbox3 & vbNullString) <> 0    Then
            Me.Textbox1.Locked = True
            Me.Textbox2.Locked = True
            Me.Textbox4.Locked = True
        Else
            Me.Textbox1.Locked = False
            Me.Textbox2.Locked = False
            Me.Textbox4.Locked = False
        End If


        If Len(Me.Textbox4 & vbNullString) <> 0    Then
            Me.Textbox1.Locked = True
            Me.Textbox2.Locked = True
            Me.Textbox3.Locked = True
        Else
            Me.Textbox1.Locked = False
            Me.Textbox2.Locked = False
            Me.Textbox3.Locked = False
        End If
    End If
End Sub

[PSSMargaret]

Thank to all who responded. Truly appreciated. Unfortunately, I haven't been able to get any of them to work. Not sure what I'm doing wrong so I'm going to pick one to respond to and hopefully figure out what's wrong.

Getting mixed results with the code from jwhite. If I implement the code as suggested, I get the below results:
Scenario #1 - If data is entered into Textboxes 1 and 2. It won't allow data entry in Textboxes 3 and 4.
Scenario #2 - If data is entered into Textbox 3 it allows data entry in Textboxes 1 and 2 but it doesn't allow data entry in Textbox 4. It won't allow me to go back and delete the data in Textbox 3 but I can go back and delete the data in Textboxes 1 and 2.
Scenario #3 - If data is entered into Textbox 4 it won't allow data entry into Textboxes 1, 2 and 3.

I also tried calling this code with the textboxes' GotFocus events and received the below results:
Scenario #1 - If data is entered into Textboxes 1 and 2, it allowed data entry in Textbox 3 but not Textbox 4. It would not allow me to go back and delete the data in TextBox 3 but I could go back and delete the data in Textboxes 1 and 2.
Scenario #2 - If data is entered into Textbox 3, it allowed data entry in Textboxes 1 and 2 but not Textbox 4. It would allow me to go back and delete the data in Textbox 3 as well as Textboxes 1 and 2.
Scenario #3 - If data is entered into Textbox 4, it allowed data entry in Textbox 3 but not Textboxes 1 and 2. It would not allow to me to go back and delete data from either Textbox 4 or 3.

Should the first If statement have it's own End If?

[davegri]

Attachment 28656
Too complex to display here. Try the database. The form shows green and red lights next to the textboxes to show if locked or not.

[Micron]

Did you consider disabling rather than locking? A locked text box isn't obvious to a lot of users.

[Micron]

I had an issue with the posted db (Method or Data Member not found). Perhaps it depends on another db for a library reference. Anyway, this does work provided the control names match.

Private Sub textbox1_AfterUpdate()

If Not IsNullEmpty(Me.textbox1) Then
 DisableControl Me.textbox3, True
 DisableControl Me.textbox4, True
Else
 DisableControl Me.textbox3, False
 DisableControl Me.textbox4, False
End If

End Sub

Private Sub textbox2_AfterUpdate()

If Not IsNullEmpty(Me.textbox2) Then
 DisableControl Me.textbox3, True
 DisableControl Me.textbox4, True
Else
 DisableControl Me.textbox3, False
 DisableControl Me.textbox4, False
End If

End Sub

Private Sub textbox3_AfterUpdate()

If Not IsNullEmpty(Me.textbox3) Then
 DisableControl Me.textbox1, True
 DisableControl Me.textbox2, True
 DisableControl Me.textbox4, True
Else
 DisableControl Me.textbox1, False
 DisableControl Me.textbox2, False
 DisableControl Me.textbox4, False
End If

End Sub

Private Sub textbox4_AfterUpdate()

If Not IsNullEmpty(Me.textbox4) Then
 DisableControl Me.textbox1, True
 DisableControl Me.textbox2, True
 DisableControl Me.textbox3, True
Else
 DisableControl Me.textbox1, False
 DisableControl Me.textbox2, False
 DisableControl Me.textbox3, False
End If

End Sub

Private Sub DisableControl(ctl As Control, mode As Boolean)

If mode = False Then 'control is not to be disabled
  ctl.Enabled = True
Else
  ctl.Enabled = False
End If

End Sub

and if this function is in a standard module (could be in the form module as well, but then can't be used by any other db code)

Public Function IsNullEmpty(ctl As Control) As Boolean

IsNullEmpty = False
If IsNull(ctl) Or ctl = "" Then IsNullEmpty = True

End Function

As long as you have a way of navigating off of the only enabled control when you want to delete its contents. Tab will work, but not all users may know that.
Feel free to lock instead of disable.

[PSSMargaret]

The code provided by davegri (Post #6) does work but only after data is entered into one of the textboxes. If you leave the record and then go back you can click on one of the other textboxes and it allows you to enter data. Do you think something like this would work for the four textboxes GotFocus events?

[davegri]

Attachment 28657
OK, you didn't mention the form reacting to existing data scrolling. So take a look at this.

[PSSMargaret]

Davegri,

My apologies for not mentioning existing data scrolling. Sometimes you don't know what you need until you implement something and then it leads to another needed feature.

The code works as far as locking the cells. The back coloring of the cells is a little wonky. It doesn't always display correctly in a continuous form until you click on a record. I'm not worried about that. Was thinking of using conditional formatting for the fields back coloring anyway.

Thank you again for your assistance. I wasn't going in this direction at all and haven't used select case before. Another new lesson. Now I have to read up on it.

[davegri]

Attachment 28666
Here's a version that colors the textbox.backcolor.