Time-bound Form Mode Change ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Sometimes, you may want to allow data entry or editing only during specific time periods, and at all other times keep the form locked in a read-only mode. This requirement usually arises in time-bound workflows, where users are permitted to update records only within fixed working slots.

A similar request was once raised in a Microsoft Access discussion forum, and I decided to explore a practical solution.

In the following example, we will enable data entry and editing on a form only during these time periods:

06:00 – 07:00 Hours
11:00 – 13:00 Hours
17:00 – 19:00 Hours

At any other time, the form will remain restricted to data view only. The Data Entry Control Function.

The function written for this task is given below. Copy it into the Standard Module:

Public Function Data_Entry(ByVal frmName As String)
Dim T1S, T1E, T2S, T2E, T3S, T3E
Dim frm As Form

Set frm = Forms(frmName)

D = Date
T1S = TimeValue("06:00:00")
T1E = TimeValue("07:00:00")

T2S = TimeValue("11:00:00")
T2E = TimeValue("13:00:00")

T3S = TimeValue("17:00:00")
T3E = TimeValue("19:00:00")

Select Case time
      Case T1S To T1E, T2S To T2E, T3S To T3E
          With frm
            If .AllowAdditions = False Then
               .AllowAdditions = True
               .AllowEdits = True
               .lblMsg.Visible = False
'change .subFrmName to match the control (window) name of the sub-form
               .subFrmName.Enabled = True
            End If
          End With
          frm.Refresh
      Case Else
          With frm
            If .AllowAdditions = True Then
                .AllowAdditions = False
                .AllowEdits = False
                .lblMsg.Visible = True
 'change the next line to set focus on any field on the main form
                .EmployeeID.SetFocus
'change .subFrmName to match the control (window) name of the sub-form
                .subFrmName.Enabled = False
            End If
          End With
          frm.Refresh
End Select

Set frm = Nothing
End Function

NB: You must make changes wherever applicable to point the code to correct control names on your Form, which I have marked with comments.

Some Changes to the Form.

Open your Form in Design View.
Add a Label control on the main Form where you want to display 'Entry not allowed', change the Name property value to lblMsg, and write the message in the Caption property.
Display the Form Property Sheet.
To implement this feature, we can take advantage of the Form’s Timer event.
1. Set the Timer Interval property of the form to 60000 (i.e., 60,000 milliseconds = 1 minute).
  - This means Access will automatically check the current system time once every minute.
  - If you want more frequent checks, increase the interval in 1000 millisecond increments (1,000 = 1 second).
2. In the Form_Timer event procedure, we can write a simple VBA routine to check whether the current time falls within the allowed edit periods. If it does, the form will be set to data entry/edit mode. Otherwise, it will automatically switch to view-only mode by disabling edits.
This way, the form will self-adjust every minute, ensuring that users can only enter or edit data during the specified hours. Select the On-Timer() Event, select [Event Procedure] from the drop-down control, and click on the build (...) Button to open the VBA module.
Copy and paste the following lines of Code, replacing the existing two lines displayed there:

Code:
```
Private Sub Form_Timer()
   Data_Entry Me.Name
End Sub
```
Save and Close the Form.

Tracking the Time for Form Mode Change.

The Timer setting ensures that the program checks every minute to determine whether the current time falls within the allowed time slots specified in the code. If the condition is true, both the main form and its subform will be enabled for data entry and editing; otherwise, they will remain locked in read-only mode.

When the form is opened, however, there will be an initial delay of one minute before Access performs the first check. To avoid this delay, you can call the Data_Entry() function from the Form_Current event procedure. This ensures that the time-check routine runs immediately upon opening the form, rather than waiting for the first timer tick.

Earlier Post Link References: