How to prevent new record until condition is met

[Darrell Childress]

I have a table that is used (through a form interface) to track the
start time and stop time of jobs in our shop. The user will click on a
button which fills in the TimeStart field with the current time. When
the job is completed, s/he clicks on a button which fills in the
TimeStop field.

I need to do something so that a user CANNOT start a job until the
active job is stopped. In other words, there can only be one record that
has a filled-in TimeStart field and a blank TimeStop field. Any
suggestions as to how to achieve this?
Thanks,
Darrell

 

[Allen Browne]

In the BeforeInsert event of your form, perform a DLookup() to see if there
is already a record where the TimeStop field is Null. If so, cancel the
event to stop the user entering the new record.

This is the basic idea:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

strWhere = "(TimeStop Is Null)"
varResult = DLookup("JobID", "JobTable", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Job " & varResult" & " already has a record where TimeStop
is blank."
End If
End Sub