checking a Required Field

[Steve]

I have a form with some fields of names and address on it and the LastName
field is a required field set at table level.
Im having dificulty finding a way to check the forms fields and making sure
the LastName field is filled in before the error message gets thrown up. It
seems so unprofessional to allow this to happen.
I want to put my own warning messge up saying this field has to be completed
and set the focus back onto the field or give the choice that the whole
record is a mistake and delete it.
Any ideas guys would be most helpful
Steve - from a land down under

 

[Allen Browne]

First port of call is the BeforeUpdate event procedure of the *form*. This
event fires even if the user never visits the control.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String 'MsgBox string.

If IsNull(Me.LastName) Then
Cancel = True
strMsg = strMsg & "Last Name cannot be blank." & vbCrLf
End If

'Repeat for other fields you want to check.

If Cancel Then
strMsg = strMsg & vbCrLf & "Complete the entry, or press Esc to
undo."
MsgBox strMsg, vbExclamation, "Invalid Data"
End If
End Sub

Beyond that, you can use the Error event of the form to trap the error.

 

[Steve]

Thanks alan thats works fine
there si somthing that seems to be troubling and thats after the warbibg I
want it to goto the field and so I have setfocus to it but it throws up an
error mesage 438 Oject doesnt support this property or method.
Why is this ? here is the code used

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String 'MsgBox string.
If IsNull(Me.ELastName) Then
Cancel = True
strMsg = strMsg & "Last Name cannot be blank." & vbCrLf
End If
'Repeat for other fields you want to check.
If Cancel Then
strMsg = strMsg & vbCrLf & "Complete the entry, or press Esc to undo."
MsgBox strMsg, vbExclamation, "Invalid Entry"
End If
Forms!frmEmployee![ELastName].SetFocus

 

[Powderfinger]

I always have the same problem. I have given up setting required fields at
the table level. The error msg seems so ugly. I do all this type of checking
at the form. I usually put a SaveAndClose button on my forms with code like
this. In this way I have all of the required fields in one area, although it
really doesn't prevent users from

Private Sub SaveAndCloseButton_Click()
DoCmd.RunCommand acCmdSaveRecord
If DoConsistencyCheck Then
DoCmd.Close
End If
End Sub


Private Function DoConsistencyCheck() As Boolean
Dim bolSuccess As Boolean, strCriteria As String
bolSuccess = True

If IsNull(Me.School) And (IsDate(Me.StartDate) Or
[Forms]![frmStudent]![frmAppointmentsSubForm].Form.RecordsetClone.RecordCoun
t > 0) Then
bolSuccess = False
DisplayErrorMsg "Please enter a school."
Me.School.SetFocus
ElseIf IsNull(Me.LName) Then
bolSuccess = False
DisplayErrorMsg "Please enter a last name."
Me.LName.SetFocus
ElseIf IsNull(Me.FName) Then
bolSuccess = False
DisplayErrorMsg "Please enter a first name."
Me.FName.SetFocus
ElseIf Not IsNumeric(Me.Status) Then
bolSuccess = False
DisplayErrorMsg "Please enter a status."
Me.Status.SetFocus
Me.Status.Dropdown
ElseIf Not IsNumeric(Me.GenderID) And IsDate(Me.StartDate) Then
bolSuccess = False
DisplayErrorMsg "Please enter a Gender."
Me.GenderID.SetFocus
Me.GenderID.Dropdown
ElseIf Me.JobPlacement And IsNull(Me.Employment) Then
bolSuccess = False
DisplayErrorMsg "You must select an Employment Status for students
in Job Placement."
Me.Employment.SetFocus
Me.Employment.Dropdown
ElseIf IsDate(Me.StartDate) Then
If Not IsNumeric(Me.TotalPrice) Then
bolSuccess = False
DisplayErrorMsg "Please enter a total price."
End If
End If

DoConsistencyCheck = bolSuccess
End Function

 

[Allen Browne]

The field is bound to a table/query that has a field named ELastName.

Do you also have a text box named ELastName on the form?
Or is the text box named something else?

If the text box was called Text99 (say), Access would see the ELastName
field in the underlying recordset, but it would not be able to set focus to
it (since it is not a control.)

Either that, or the form is not named frmEmployee.
Or perhaps it's a subform (so not part of the Forms collection.)
Try:
Me.ELastname.SetFocus

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks alan thats works fine
there si somthing that seems to be troubling and thats after the warbibg I
want it to goto the field and so I have setfocus to it but it throws up an
error mesage 438 Oject doesnt support this property or method.
Why is this ? here is the code used

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String 'MsgBox string.
If IsNull(Me.ELastName) Then
Cancel = True
strMsg = strMsg & "Last Name cannot be blank." & vbCrLf
End If
'Repeat for other fields you want to check.
If Cancel Then
strMsg = strMsg & vbCrLf & "Complete the entry, or press Esc to
undo."
MsgBox strMsg, vbExclamation, "Invalid Entry"
End If
Forms!frmEmployee![ELastName].SetFocus

First port of call is the BeforeUpdate event procedure of the *form*.
This
event fires even if the user never visits the control.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String 'MsgBox string.

If IsNull(Me.LastName) Then
Cancel = True
strMsg = strMsg & "Last Name cannot be blank." & vbCrLf
End If

'Repeat for other fields you want to check.

If Cancel Then
strMsg = strMsg & vbCrLf & "Complete the entry, or press Esc to
undo."
MsgBox strMsg, vbExclamation, "Invalid Data"
End If
End Sub

Beyond that, you can use the Error event of the form to trap the error.