Check Records

[rjvega]

I am creating a database which tracks jobs to be worked on for an area. When
a job is released, the status changes to 1 (Released). There is then a
continuous form which shows all of the records currently at status 1.

The idea is that an employee (using a shared computer) looks at the released
jobs, selects the one they're going to work on, selects their name from a
combo box, changes the status from 1 (Released) to 2 (In Process) using a
combo box and then closes the form (ideally using a button, but I know I need
to account for if an employee closes the form by another method).

What I want to do is create a check so that when the form is closed it looks
to see that for all the jobs that were just changed to status 2, the employee
field was not left blank and vice versa; for all fields where an employee was
selected, the status was changed to 2. If it finds a problem, I want it to
not close the form and leave it open for the employee to fix.

I was thinking that I could write some code to quickly run through the
records with status 2 and make sure employee is not "", and also run through
the records where employee was not "" and make sure the status is not 1.
Immediately upon finding a problem, I would abort the Close command. I'm not
sure how to code this though.

Any help would be appreciated.

 

[John W. Vinson]

What I want to do is create a check so that when the form is closed it looks
to see that for all the jobs that were just changed to status 2, the employee
field was not left blank and vice versa; for all fields where an employee was
selected, the status was changed to 2. If it finds a problem, I want it to
not close the form and leave it open for the employee to fix.

You can cover both of these by using the Form's BeforeUpdate event. It fires
whenever a record is changed. You can cancel the update if the record isn't
correct. For instance:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Select Case Me!Status
Case 1
If Not IsNull(Me!EmployeeID) Then
MsgBox "An employee should not be selected for open job", vbOKOnly
Cancel = True
End If
Case 2
If IsNull(Me!EmployeeID) Then
MsgBox "Please select an employee", vbOKOnly
Cancel = True
Me!EmployeeID.SetFocus
End If
Case Else
MsgBox "Oops! There is no such status!", vbOKOnly
Cancel = True
End Select
End Sub

John W. Vinson [MVP]