0

I want to disable specific fields in one row in Continuous Form in Ms Access using VBA when certain value of one field in the same row is selected. I used the below code but it applies on all rows. 

Private Sub AttendanceType_AfterUpdate()
Me.[Enter Time].Enabled = True
Select Case Me.AttendanceType
Case "Annual Vacation", "Casual Leave", "Sick Leave"
Me.[Enter Time].Enabled = False
End Select
End If 
End Sub

The above code works in Single Form view where we have only one row. However, in Continuous Form view, it disables the raw named "Enter Time" in all rows.

In this attached picture, see the first row. When attendance type is Sick Leave, it disables Enter Time fields in all Rows

enter image description here

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • 1
    Won't work in VBA because there is only the one control that is instantiated for multiple records. Property settings apply to all instances of control. Use Conditional Formatting. – June7 Sep 09 '17 at 16:07
  • Conditional Formatting works exactly as I want it. Thank you all for your contribution, and sorry for the duplicate question. –  Sep 09 '17 at 17:12

1 Answers1

0

It's unposibble what you want. I guess you want to protect the [Enter Time] field before making changes. You can use a different approach based on event BeforeUpdate for the [Enter Time] field:

Private Sub [Enter Time]_BeforeUpdate(Cancel As Integer) 
if Me.AttendanceType = "Annual Vacation" or _
   Me.AttendanceType = "Casual Leave" or _
   Me.AttendanceType = "Sick Leave" Then
   MsgBox "Enter Time has already been entered in the database." 
   Cancel = True 
   Me![Enter Time].Undo 
End If 
End Sub
adarti
  • 77
  • 3
  • I wanted to disable/enable '[Enter Time]' based on the value of 'Attendance Type' per row. As they suggested above, conditional Formatting can achieve that. Right click on the required control and then Conditional Formatting. Thanks to all for your comments. –  Sep 09 '17 at 17:15