Validate fields & display just 1 message

[KevinSlater]

Hi, I have the following code bellow to check 2 fields in a form to see if they are filled in or not, & displaying a message if not. If the user does not fill in both fields it will display both messages, but this is a bit annoying having 2 messgae box popups, is it possible to make only one of the messages display if both fields are empty?, maybe theres a better way of validating fields?, any help would be great.


Private Sub Form_BeforeUpdate(Cancel As Integer)

If FIRSTNAME = 0 Then

MsgBox "Please complete: firstname field"

Cancel = True
End If

If SURNAME = 0 Then
MsgBox "Please complete: surname field"

End If

End Sub

 

[RV]

How 'bout joining the conditions:

If FIRSTNAME = 0 OR SURNAME = 0 Then

MsgBox "You must complete both first name and sur name"

Cancel = True
End If

PS I'd validate on either of the conditions otherwise one of the text boxes could still be 0.

PPS Are you really using a default value 0 (zero) on your fields / tablre coumns.
I'd expect that you'd check whether the text boxes are not empty:

If IsNull(FIRSTNAME) OR IsNull(SURNAME) Then

MsgBox "You must complete both first name and sur name"

Cancel = True
End If

RV

 

[WayneRyan]

Kevin,

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhatField As String

strWhatField = ""

If Nz(Me.FIRSTNAME) = "" Or Then
   strWhatField = "FirstName"
End If

If Nz(Me.SURNAME) = "" Or Then
   strWhatField = "SurName"
End If

If strWhatField <> "" Or Then
   MsgBox "Please complete required " & strWhatField & " field."
   Cancel = True
End If

End Sub

Wayne

 

[KevinSlater]

Thank you for your replies, there a great help, but i cant get your code to work wayne (i tried taking the word: "or" out but nothing happens), the 2 if statements below appear in red so i guess theres something wrong in the code.

If Nz(Me.SURNAME) = "" Or Then
If Nz(Me.FIRSTNAME) = "" Or Then

one more thing I would like to do if possible: depending on what field the user leaves empty & after displaying the appropriate message is it possible to make the cursor appear in the corresponding field? (ie if surname is blank & message appears the cursor then appears in the surname field)

 

[WayneRyan]

Kevin,

Oops, Cut & Paste Error ...

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhatField As String

strWhatField = ""

If Nz(Me.FIRSTNAME) = "" Then
   strWhatField = "FirstName"
End If

If Nz(Me.SURNAME) = ""  Then
   strWhatField = "SurName"
End If

If strWhatField <> "" Then
   MsgBox "Please complete required " & strWhatField & " field."
   Cancel = True
   Me.Controls(strWhatField).SetFocus
End If

End Sub

Wayne

 

[KevinSlater]

Thanks, thats a great help

 

[workmad3]

Spotted one problem with that wayne. It only tells you about one field if both of them are empty.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhatField As String
Dim strFocusField As String

strWhatField = ""

If Nz(Me.FIRSTNAME) = "" Then
   strWhatField = "FirstName"
   strFocusField = "FirstName"
End If

If Nz(Me.SURNAME) = ""  Then
   If strWhatField = "" Then
       strWhatField = "SurName"
   Else
       strWhatField = strWhatField & " and SurName"
   End If
   strFocusField = "SurName"
End If

If strWhatField <> "" Then
   MsgBox "Please complete required " & strWhatField & " field."
   Cancel = True
   Me.Controls(strFocusField).SetFocus
End If

End Sub

The above will tell you if both the fields are empty, and set the focus to the surname field if both are empty.

not tested, but should work

 

[Cereldine]

hi, ive adapted this code to fit my application and it works. Any ideas on how i could make this into a global module so it can be reused through out application?

ive tried the following but i get an error if i try to assign a null value to a string? are their any ways round this?

Form code

Private Sub chkEmployee_Click()
Dim single_field As String
Dim many_fields As String
'' set up the string values
strFirst = Me.Parent.FirstName
strLast = Me.Parent.LastName
strCompany = Me.Parent.Company
single_field = ""

'' pass the values to the generic string

strUpdate_one = strFirst
strUpdate_two = strLast
strUpdate_three = strCompany


'' finally set up a string to hold field name
strField_one = " first name"
strField_two = " last name"
strField_three = " company"

'' code starts here___________________________________________

If chkEmployee.Value = True Then

Call Global_update(strUpdate_one, strUpdate_two, strUpdate_three, strField_one, strField_two, strField_three)

Else
'' build a message box saying which fields are filled in
MsgBox "you have successfully updated employee section"
End If

End Sub

Global code

Public Sub Global_update(strUpdate_one As String, strUpdate_two As String, strUpdate_three As String, strField_one As String, strField_two As String, strField_three As String)

single_field = ""

If Nz(strUpdate_one) = "" Then
single_field = strField_one
many_fields = strField_one
End If

If Nz(strUpdate_two) = "" Then
If single_field = "" Then
single_field = strField_two
Else
single_field = single_field & ", " & strField_two
End If
many_fields = strField_two
End If

If Nz(strUpdate_three) = "" Then
If single_field = "" Then
single_field = strField_three
Else
single_field = single_field & ", " & strField_three
End If
many_fields = strField_three
End If

If single_field <> "" Then
MsgBox "You need to complete the following: " & single_field & " fields"
Cancel = True
''Me.Controls(many_fields).SetFocus
End If
End Sub

 

[shaz123]

Hi Guys,

ive tried the coding shown by workmad3 and i keep getting the follwing error message, Runtime error 438, "object does not support this property or method"

It does not like the coding in red.

If strWhatField <> "" Then
MsgBox "Please complete required " & strWhatField & " field."
Cancel = True
Me.Controls(strFocusField).SetFocus End If

Any Ideas, I would also like to check more then two fields would this coding be appropriate


"
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhatField As String
Dim strFocusField As String

strWhatField = ""

If Nz(Me.[Cylinder Serial Number]) = "" Then
strWhatField = "[Cylinder Serial Number]"
strFocusField = "[Cylinder Serial Number]"
End If

If Nz(Me.[Cylinder Barcode Label]) = "" Then
If strWhatField = "" Then
strWhatField = "[Cylinder Barcode Label]"
Else
strWhatField = strWhatField & " and [Cylinder Barcode Label]"
End If
strFocusField = "[Cylinder Barcode Label]"
End If

If Nz(Me.[Last Test Date]) = "" Then
If strWhatField = "" Then
strWhatField = "[Last Test Date]"
Else
strWhatField = strWhatField & " and [Last Test Date]"
End If
strFocusField = "[Last Test Date]"
End If


If strWhatField <> "" Then
MsgBox "Please complete required " & strWhatField & " field."
Cancel = True
Me.Controls(strFocusField).SetFocus

End If

End Sub"

 

[shaz123]

Its ok it seems to be working by itself now, however i have another question, whn i do click on the add button when there i have not filled in all the fields the message box warning that all the fields are not filled in does appear, however another message also appears saying "the cylinder number has already been added please try another". This error message should only appear if the cylinder number in the text field (cylinder serial number) matches one in the table, when it doesnt match it still comes up with this error message. This did work before i added the the validation for the empty textboxes.

"Private Sub Command39_Click()
On Error GoTo Err_Command39_Click

DoCmd.GoToRecord , , acNewRec

Exit_Command39_Click:
Exit Sub

Err_Command39_Click:
MsgBox ("The Cylinder Serial Number You Are Trying To Add Has Already Been Added Please Try Another")

Resume Exit_Command39_Click


End Sub"

Any ideas

 

[shaz123]

Its ok it seems to be working by itself now, however i have another question, whn i do click on the add button when there i have not filled in all the fields the message box warning that all the fields are not filled in does appear, however another message also appears saying "the cylinder number has already been added please try another". This error message should only appear if the cylinder number in the text field (cylinder serial number) matches one in the table, when it doesnt match it still comes up with this error message. This did work before i added the the validation for the empty textboxes.

"Private Sub Command39_Click()
On Error GoTo Err_Command39_Click

DoCmd.GoToRecord , , acNewRec

Exit_Command39_Click:
Exit Sub

Err_Command39_Click:
MsgBox ("The Cylinder Serial Number You Are Trying To Add Has Already Been Added Please Try Another")

Resume Exit_Command39_Click


End Sub"

Any ideas

 

[gemma-the-husky]

The error message is just trapping an error in the gotonewrec statement. Because you have trapped the error you see your message instead of the system message, which may or may not be apposite.

Going from earlier posts, in order to move to a new record, it first has to save the current record. If there is an untrapped error in the save (say there is some missing mandatory field), the gotonewrecord statement fails, and gives you the error message.

While testing, before your error message put
msgbox("Error: " & err.number & " Desc: " & err.description) to see what is REALLY happening.