Hi Ben,
Try using the Form's BeforeUpdate procedure to validate all user entry.
Cancel the update, with the appropriate message box to the user telling them
what the problem is, if your validation tests fail. For example, you may have
one or more text boxes whose entry is required. You can check that something
is there using the len function. For example:
If len(txtTextboxName & "") > 0 Then 'User made some entry
You can do all sorts of validations, to help ensure that the data is proper
before attempting to commit it to the server. Here is an example of what I am
talking about, from one of my own databases. As you can see, there is a LOT
of validation going on here:
Option Compare Database
Option Explicit '<---Do make sure you include this
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
Dim blnValidate As Boolean
blnValidate = Validate
If blnValidate = False Then
Cancel = True
Err.Number = acDataErrContinue
Else
<snipped code--not critical for this example>
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_BeforeUpdate event procedure..."
Resume ExitProc
End Sub
The validate function that is called is shown here:
Private Function Validate() As Boolean
On Error GoTo ProcError
Dim strTitle As String
strTitle = "Missing Required Value..."
Validate = False 'Initialize return value
' Check for required field entries, if the form is dirty
If Len(cboProject & "") = 0 Then
MsgBox "Please Select a Project or click on the" _
& vbCrLf & "Undo button to discard all changes.", _
vbCritical, strTitle
Me.cboProject.SetFocus
Exit Function
End If
If Len(txtRequestTitle & "") = 0 Then
MsgBox "Please Enter a Title or click on the" & vbCrLf _
& "Undo button to discard all changes.", _
vbCritical, strTitle
Me.txtRequestTitle.SetFocus
Exit Function
End If
If Len(cboStatus & "") = 0 Then
MsgBox "Please Select a Status or click on the" & vbCrLf _
& "Undo button to discard all changes.", _
vbCritical, "Missing Required Value..."
cboStatus.SetFocus
Exit Function
End If
If Len(cboPriority & "") = 0 Then
MsgBox "Please Select the Priority Level or click on the" _
& vbCrLf & "Undo button to discard all changes.", _
vbCritical, "Missing Required Value..."
cboPriority.SetFocus
Exit Function
End If
If Len(cboRequestedBy & "") = 0 Then
MsgBox "Please Select a Requestor or click on the" _
& vbCrLf & "Undo button to discard all changes.", _
vbCritical, "Missing Required Value..."
cboRequestedBy.SetFocus
Exit Function
End If
If Len(txtRaisedDate & "") = 0 Then
MsgBox "Please Enter the Date Raised or click on the" _
& vbCrLf & "Undo button to discard all changes.", _
vbCritical, "Missing Required Value..."
txtRaisedDate.SetFocus
Exit Function
Else
If Date < txtRaisedDate Then
MsgBox "You must not enter a future date", _
vbCritical, "Incorrect Date Entry..."
txtRaisedDate = Null
txtRaisedDate.SetFocus
Exit Function
End If
End If
If Len(cboAssignedTo & "") = 0 Then
MsgBox "Please Select the Assigned Person or click on the" _
& vbCrLf & "Undo button to discard all changes.", _
vbCritical, "Missing Required Value..."
cboAssignedTo.SetFocus
Exit Function
End If
If Len(txtRequiredDate & "") = 0 Then
MsgBox "Please Enter the Date Required or click on the" _
& vbCrLf & "Undo button to discard all changes.", _
vbCritical, "Missing Required Value..."
txtRequiredDate.SetFocus
Exit Function
Else
If txtRequiredDate < txtRaisedDate Then
MsgBox "The Required Date must occur on" _
& "or after the Raised Date", _
vbCritical, "Incorrect Date Entry..."
txtRequiredDate = Null
txtRequiredDate.SetFocus
Exit Function
End If
End If
If Len(cboRespArea & "") = 0 Then
MsgBox "Please Select the Group Responsible" _
& "or click on the" & vbCrLf _
& "Undo button to discard all changes.", _
vbCritical, "Missing Required Value..."
cboRespArea.SetFocus
Exit Function
End If
If Len(txtRequestDescription & "") = 0 Then
MsgBox "Please Enter a Description or click on the" _
& vbCrLf & "Undo button to discard all changes.", _
vbCritical, "Missing Required Value..."
txtRequestDescription.SetFocus
Exit Function
End If
'Approved date must be entered
If Not IsNull(cboApprovedBy) Then
If IsNull(txtApprovedDate) Then
MsgBox "Please Enter the Date Approved or click on the" _
& vbCrLf & "Undo button to discard all changes.", _
vbCritical, "Missing Required Value..."
txtApprovedDate.SetFocus
Exit Function
Else
If txtApprovedDate < txtRaisedDate Then
MsgBox "The Approved Date must occur on" _
& "or after the Raised Date", vbCritical, _
"Incorrect Date Entry..."
txtApprovedDate = Null
txtApprovedDate.SetFocus
Exit Function
End If
End If
End If
Validate = True 'If we get this far, then all validation checks passed.
Me.txtLastUpdate = Date
ExitProc:
Exit Function
ProcError:
Select Case Err.Number
Case 2110
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Validate procedure..."
End Select
Validate = False
Resume ExitProc
End Function
Private Sub cmdClose_Click()
On Error GoTo ProcError
'Save any changes first
If Me.Dirty = True Or mblnRegression = True Then
If Validate = True Then
'Save the record, requery QBF if it is open, and close this form.
Me.Dirty = False
DisplayQBF (True)
mblnRegression = False
DoCmd.Close acForm, Me.Name
Else
'Record was dirtied, but changes do not pass validation rules, so
bail out.
Exit Sub
End If
Else
'Record was opened for viewing only, but user made no changes, so we can
'avoid the overhead of requerying the QBF form.
DisplayQBF (False)
DoCmd.Close acForm, Me.Name
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdClose_Click event procedure ..."
Resume ExitProc
End Sub
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
