Preventing new records on conditions.

[roelandkim]

I have a question on preventing users from adding a new record to a continous subform if a certain condition on the current record they are working on is not met. Let me explain…

I created a database (with the tons of insight from these forums)for tracking incoming phone calls to my department. It basically tracks the information about the call and what the issue is. Once a call is entered, a supervisor assigns the call to an individual. I have a form and a subform to do this, the Inquiry Form tracks the basics of the phone call, and the Assignee Form (subform) tracks all the individuals that the call has been assigned to. Each call gets a unique ID number, and each Assignment record for that call is linked on that number. The subform contains one field called “Completed”, which the user clicks when they have completed that particular step. Once they mark it completed I want them to be able to assign the case to another individual (essentially start a new record), but not until they complete their step. The way my system is setup now people can have multiple steps marked incomplete, which I don’t want to happen. The subform is set up as a continuous form, since the next person working on a step to complete the inquiry needs to be able to see what went on before it was assigned to them.

I hope this makes sense and any help would be appreciated. Attached is a screen shot of the form.

Thanks,
Roelandkim

 

[doran_doran]

data check before continue....

On the form properties, put the following event under before update section.

====================================
Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = Not dataOK
End Sub
====================================

Put following for DataOK. CUSTOMIZE THE DATAOK ACCORDING TO YOUR NEEDS.


====================================
Private Function dataOK() As Boolean
'Assume success
dataOK = True

'Now test completed
If Nz(Me![1st_rjct], "") <> "" And Nz(Me![1st_rjct_reason], "") = "" Then
MsgBox "You have not entered the first reject reason", vbOKOnly + vbExclamation
dataOK = False
ElseIf Nz(Me![2nd_rjct], "") <> "" And Nz(Me![2nd_rjct_reason], "") = "" Then
MsgBox "You have not entered the second reject reason", vbOKOnly + vbExclamation
dataOK = False
ElseIf Nz(Me![3rd_rjct], "") <> "" And Nz(Me![3rd_rjct_reason], "") = "" Then
MsgBox "You have not entered the third reject reason", vbOKOnly + vbExclamation
dataOK = False

'auditing census receive check box and census date received fields
ElseIf Nz(Me![Census_Recd] = True) And Nz(Me![Census_Rcvd_Date], "") = "" Then
MsgBox "You have not entered the census received date", vbOKOnly + vbExclamation
dataOK = False

ElseIf Nz(Me![Census_Recd] = False) And Nz(Me![Census_Rcvd_Date] > 0) Then
MsgBox "You have not check the Census Received Box", vbOKOnly + vbExclamation
dataOK = False

'auditing Invoice waived check box and Waived Reasons
'ElseIf Nz(Me![Invoice_Waived] = True) And Nz(Me![Waiver_Reason], "") = "" Then
' MsgBox "You have not select waiver reason", vbOKOnly + vbExclamation
' dataOK = False

'ElseIf Nz(Me![Invoice_Waived] = False) And Nz(Me![Waiver_Reason], "") <> "" Then
' MsgBox "You have not check the Invoice Waived Box", vbOKOnly + vbExclamation
' dataOK = False

'auditing force comments on detail audit b/c manager or tl has to approved detail audit and admin must put a comment whenever they choose detail audit.
ElseIf Nz(Me![AuditType] = "Detail") And Nz(Me![AuditComments], "") = "" Then
MsgBox "You must enter a comment for Detail Audit", vbOKOnly + vbExclamation
dataOK = False

ElseIf Nz(Me![Audit] = True) And Nz(Me![AuditType], "") = "" Then
MsgBox "You have not select Method of Audit", vbOKOnly + vbExclamation
dataOK = False

ElseIf Nz(Me![Audit] = False) And Nz(Me![AuditType], "") <> "" Then
MsgBox "You have not check the Audit Box", vbOKOnly + vbExclamation
dataOK = False

End If
End Function

 

[roelandkim]

Thanks, I'll give it a try.