Here's the rest of the code for the form with the DCount function:
Option Compare Database _
Private Sub cboEmployeeID_AfterUpdate() _
'Move the focus to AuditorComments after a
'record update
Me.AuditorComments.SetFocus _
End Sub _
Private Sub cmdCancelAndClose_Click() _
'Cancel observation and close this form _
On Error Resume Next _
DoCmd.RunCommand acCmdUndo _
DoCmd.Close acForm, Me.Name _
End Sub _
Private Sub _
cmdCancelAndSelectDifferentCriteria_Click() _
'Cancel the current observation, close this
'form, and then open the
'frmSelectFloorProgCriteria
On Error Resume Next _
DoCmd.RunCommand acCmdUndo _
DoCmd.OpenForm _
"frmSelectFloorProgCriteria", acNormal, , , _
acFormAdd, acWindowNormal _
DoCmd.Close acForm, Me.Name _
End Sub _
Private Sub cmdSaveAndClose_Click() _
'Save the current record and create a new
'record
On Error Resume Next _
DoCmd.Close acForm, Me.Name _
End Sub _
Private Sub cmdSaveAndMakeAnother_Click() _
'Temporarily set the default value for new
'records to the same FloorProgCriteriaID
'Save the current record and create a new
'record
Me![FloorProgCriteriaID].DefaultValue = _
Chr$(34) & Me![FloorProgCriteriaID] & Chr$(34) _
On Error Resume Next _
DoCmd.GoToRecord , , acNewRec _
Me.FloorProgCriteriaID = _
Me.FloorProgCriteriaID _
'Move the focus to AuditorComments after a
'record update
Me.AuditorComments.SetFocus _
End Sub _
Private Sub _
cmdSaveAndSelectDifferentCriteria_Click() _
'Save the current observation, close this
'form, and then open the
'frmSelectFloorProgCriteria
On Error Resume Next _
'If an error occurs, start running code here _
DoCmd.OpenForm _
"frmSelectFloorProgCriteria", acNormal, , , _
acFormAdd, acWindowNormal _
DoCmd.Close acForm, Me.Name _
End Sub _
Private Sub Form_BeforeUpdate(Cancel As _
Integer) _
'Limits the number of records allowed based
'on the field FloorProgMaxObservations.
If DCount("*", "tblFloorProgAudit", _
"[AuditID] = " & Me.AuditID & " " & "AND _
[FloorProgCriteriaID] = " & _ _
Me.FloorProgCriteriaID & " " & "AND _
[AuditorID] = """ & Me.AuditorID & """") = _ _
Me.FloorProgMaxObservations.Value _
Then _
MsgBox "You're attempting to exeed the _
maximum number of observation allowed for _
this criteria set. Click OK and then delete this _
observation." _
Cancel = True _
End If _
'Require a comment for any records where
'Unsatisfactory is Yes.
If IsNull(Me.AuditorComments) And _
(Me.Unsatisfactory) = True Then _
MsgBox "You must add auditor _
comments for all unsatisfactory observations.", _
vbOKOnly _
Cancel = True _
End If _
'Require an EmployeeID for any records are
'from Program 20 and Unsatisfactory is Yes.
If (Me.FloorProgID) = 20 And _
IsNull(Me.EmployeeID) Then _
MsgBox "You must include the _
Employee's ID for all Employee Interview _
Questions.", vbOKOnly _
Cancel = True _
End If _
End Sub _
Private Sub Form_Current() _
'Display the number of observations that
'remain available for the selected criteria
Me.CountOfObservations.Value = _
Me.FloorProgMaxObservations.Value - _
(DCount("*", "tblFloorProgAudit", "[AuditID] = _
" & _ _
Me.AuditID.Value & " And " & _
"[FloorProgCriteriaID] = " & _
Me.FloorProgCriteriaID.Value & " And " & _
"[AuditorID] = '" & _ _
Me.AuditorID.Value & "'")) _
End Sub _
Private Sub Form_Open(Cancel As Integer) _
'Open form at a new record with criteria
'selected from frmSelectFloorProgCriteria
DoCmd.GoToRecord , , acNewRec _
Me.FloorProgCriteriaID = _
[Forms]![frmSelectFloorProgCriteria]![lstFloorP _
rogCriteria] _
'Enable the EmployeeID combo box for
'program 20 only.
If (Me.FloorProgID) = 20 Then _
Me.cboEmployeeID.Enabled = True _
End If _
If (Me.FloorProgID) <> 20 Then _
Me.cboEmployeeID.Enabled = False _
End If _
'Enable the Add New Employee command
'button for program 20 only.
If (Me.FloorProgID) = 20 Then _
Me.cmdAddNewEmployee.Enabled = True _
End If _
If (Me.FloorProgID) <> 20 Then _
Me.cmdAddNewEmployee.Enabled = False _
End If _
'Enable the EmployeeName text box for
'program 20 only.
If (Me.FloorProgID) = 20 Then _
Me.txtEmployeeName.Enabled = False _
Me.txtEmployeeName.Locked = True _
End If _
If (Me.FloorProgID) <> 20 Then _
Me.txtEmployeeName.Enabled = False _
Me.txtEmployeeName.Locked = False _
End If _
Me.Unsatisfactory.SetFocus _
End Sub _
Private Sub Form_Undo(Cancel As Integer) _
'Disable the employee section of the form
'after a record undo
Me.cboEmployeeID.Enabled = False _
Me.cmdAddNewEmployee.Enabled = False _
Me.txtEmployeeName.Enabled = False _
Me.txtEmployeeName.Locked = False _
End Sub _
Private Sub cmdDelete_Click() _
On Error GoTo cmdDelete_Click_Err _
On Error Resume Next _
DoCmd.GoToControl _
Screen.PreviousControl.Name _
Err.Clear _
If (Not Form.NewRecord) Then _
DoCmd.RunCommand acCmdDeleteRecord _
End If _
If (Form.NewRecord And Not Form.Dirty) _
Then _
Beep _
End If _
If (Form.NewRecord And Form.Dirty) Then _
DoCmd.RunCommand acCmdUndo _
End If _
If (MacroError <> 0) Then _
Beep _
MsgBox MacroError.Description, _
vbOKOnly, "" _
End If _
cmdDelete_Click_Exit: _
Exit Sub _
cmdDelete_Click_Err: _
MsgBox Error$ _
Resume cmdDelete_Click_Exit _
End Sub _
Private Sub Previous_Record_Click() _
On Error GoTo Previous_Record_Click_Err _
On Error Resume Next _
DoCmd.GoToRecord , "", acPrevious _
If (MacroError <> 0) Then _
Beep _
MsgBox MacroError.Description, _
vbOKOnly, "" _
End If _
Previous_Record_Click_Exit: _
Exit Sub _
Previous_Record_Click_Err: _
MsgBox Error$ _
Resume Previous_Record_Click_Exit _
End Sub _
Private Sub Next_Record_Click() _
On Error GoTo Next_Record_Click_Err _
On Error Resume Next _
DoCmd.GoToRecord , "", acNext _
If (MacroError <> 0) Then _
Beep _
MsgBox MacroError.Description, _
vbOKOnly, "" _
End If _
Next_Record_Click_Exit: _
Exit Sub _
Next_Record_Click_Err: _
MsgBox Error$ _
Resume Next_Record_Click_Exit _
End Sub _
Private Sub Last_Record_Click() _
On Error GoTo Last_Record_Click_Err _
DoCmd.GoToRecord , "", acLast _
Last_Record_Click_Exit: _
Exit Sub _
Last_Record_Click_Err: _
MsgBox Error$ _
Resume Last_Record_Click_Exit _
End Sub _
Private Sub cmdAddNewEmployee_Click() _
On Error GoTo _
cmdAddNewEmployee_Click_Err _
DoCmd.OpenForm "frmAddEmployee", _
acNormal, "", "", , acNormal _
cmdAddNewEmployee_Click_Exit: _
Exit Sub _
cmdAddNewEmployee_Click_Err: _
MsgBox Error$ _
Resume cmdAddNewEmployee_Click_Exit _
End Sub _
