Custom error msg for Req'd Field 1

[leadadmin]

I have a simple form for a simple table. The first several fields are required, which I did at the table level. I have 2 problems: (1) If I do not enter all the req'd fields and click my "Add Record" control button, I get the error "You can't go to the specified record". I would prefer to display a customized message like "Please enter name", and (2) sometimes I get the Access req'd field message "The field xx.xx cannot contain null values," etc., like if I delete a field that was previously entered. I would also like to display my custom error message in that instance.

What do you suggest? Thank you.

 

[willir]

Using the table design to ensure data integrity is a good thing. To display a more meaningful message, you can use the BeforeUpdate event procedure to test before the update. May be a little tedious, but the coding can be fairly simple...

Dim booOkay as Boolean
Dim strMsg as String

booBadRecord = False
strMsg = ""

If Len(Nz(Me.RequiredTextField, "")) = 0 Then
   booBadRecord = True
   strMsg = "Missing data for RequiredTextField." & vbCRLF
   Me.RequiredTextField.SetFocus
End If

If Nz(Me.RequiredNumberField, 0) = 0 Then
   booBadRecord = True
   strMsg = strMsg & "Missing data for RequiredNumberField. & vbCRLF
   Me.RequiredNumberField.SetFocus
End If

If Not IsDate(Me.RequiredDateField) Then
   booBadRecord = True
   strMsg = strMsg & "Missing data for RequiredDateField.
   Me.RequiredDateField.SetFocus
End If

If booBadRecord Then
   MsgBox strMsg
   Cancel = True
End If

Richard

 

[TheAceMan1]

Hoe are ya leadadmin . . . . .

Put a question mark {[blue]?[/blue]) in the [purple]Tag Property[/purple] of the textboxes you wish to validate. Then in the [purple]BeforeUpdate Event[/purple] of the form, copy/paste the following:

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, ctl As Control
   
   DL = vbNewLine & vbNewLine
   
   For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Then
         If ctl.Tag = "[purple][b]?[/b][/purple]" And Trim(ctl & "") = "" Then
            Msg = "Required Data for '" & ctl.Name & "' is missing!" & DL & _
                  "You can't continue until this information is provided!" & DL & _
                  "Enter the data and try again . . . "
            Style = vbCritical + vbOKOnly
            Title = "Required Data Error! . . ."
            MsgBox Msg, Style, Title
            ctl.SetFocus
            Cancel = True
            Exit For
         End If
      End If
   Next[/blue]

Thats it! . . .

See Ya! . . . . . .

 

[leadadmin]

Willir,
Thanks so much. That worked beautifully. Two things, though: (1) my custom message appears, I click OK, and a second error message "You can't go to the specified record" opens. I click OK and I can then enter the required field. How do we stop the second message from appearing? (2) I have 5 req'd fields. If I leave more than one blank, it displays the error msg for the last one, then after filling that field in it steps backwards through the prior empty fields in reverse order. Once it encounters the first error how do I stop all the other field edits from executing and just drop through to the end? Sorry, I don't know this programming language!

Thanks very much.

 

[PHV]

Something like this ?
If Trim(Me.RequiredTextField & "") = "" Then
MsgBox "Missing data for RequiredTextField."
Cancel = True
Me.RequiredTextField.SetFocus
Exit Sub
End If

If Nz(Me.RequiredNumberField, 0) = 0 Then
MsgBox "Missing data for RequiredNumberField."
Cancel = True
Me.RequiredNumberField.SetFocus
Exit Sub
End If

If Not IsDate(Me.RequiredDateField) Then
MsgBox "Missing data for RequiredDateField.
Cancel = True
Me.RequiredDateField.SetFocus
Exit Sub
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[leadadmin]

PH:
I tried your suggestion and still got the same results. First I get my good error message "Missing data...", but then I also get the "Can't go to specified record", then I can enter the req'd data.

his is what my code looks like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim booOkay As Boolean
Dim strMsg As String

booBadRecord = False
strMsg = ""

If Trim(Me.ContactFirstName & "") = "" Then
MsgBox "Missing data for ContactFirstName."
Cancel = True
Me.ContactFirstName.SetFocus
Exit Sub
End If

End Sub

Any other ideas?
Thanks very much.

 

[PHV]

What is the code behind your Add button ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[leadadmin]

Here is my Add Record code:

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

Thanks!

 

[PHV]

You may try something like this:
DoCmd.RunCommand acCmdSaveRecord
If Not Me.Dirty Then DoCmd.GoToRecord , , acNewRec

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244