Validating Form (Require Entry in fields) (1 Viewer)

MickJav

AWF VIP
Local time
Today, 12:40
Joined
Nov 28, 2005
Messages
1,432
his is used with the beforeupdate of the form
This is just one way of doing this.
2020-02-21 (2).png

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo HandleErr

Cancel = Not TestRecord

HandleExit:
    Exit Function
  
HandleErr:
    Select Case Err.Number
        Case 2501 'Cancel = True
            Exit Function
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select
End Sub
You can add as many options as you like just copy from the "If" to "End If" for one field and past/Update to suit.
Code:
Function TestRecord() As Boolean
Dim Txt As String
Dim Er As Boolean
On Error GoTo HandleErr
Er = False
    If IsNothing(Me![STxt_Question]) Then
        Txt = Txt & vbCrLf & "You must add a question"
        Er = True
    End If
    If IsNothing(Me![STxt_Answer]) Then
        Txt = Txt & vbCrLf & "A Question requires a answer"
        Er = True
    End If
    If IsNothing(Me![SCbo_CategoryID]) Then
        Txt = Txt & vbCrLf & "Category is empty"
        Er = True
    End If
    If IsNothing(Me![SCbo_Qstatus]) Then
        Txt = Txt & vbCrLf & "Please select a status"
        Er = True
    End If
     If IsNothing(Me![SCbo_LevelID]) Then
        Txt = Txt & vbCrLf & "Please select a level for this question"
        Er = True
    End If
   
    If Er = True Then
        MsgBox "The following data errors have been found" & Txt & vbCrLf & _
        "These errors must be corrected before I can save this record", vbCritical + vbOKOnly, "Incomplete Record"
        TestRecord = False
    Else
        TestRecord = True
    End If

HandleExit:
    Exit Function
    
HandleErr:
    Select Case Err.Number
        Case 2501 'Cancel = True
            Exit Function
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select
End Function
I did not write this function but it was never taged with the developer
Code:
Function IsNothing(varToTest As Variant) As Boolean
'Function found on web
On Error GoTo HandleErr

    IsNothing = True

    Select Case VarType(varToTest)
        Case vbEmpty
            Exit Function
        Case vbNull
            Exit Function
        Case vbBoolean
            If varToTest Then IsNothing = False
        Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
            If varToTest <> 0 Then IsNothing = False
        Case vbDate
            IsNothing = False
        Case vbString
            If (Len(varToTest) <> 0 And varToTest <> " ") Then IsNothing = False
    End Select
  
HandleExit:
    Exit Function
  
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select
End Function
 
Last edited:

zeroaccess

Active member
Local time
Today, 06:40
Joined
Jan 30, 2020
Messages
386
To clarify: this is a way of consolidating entry validation errors into one text box?
 

Micron

AWF VIP
Local time
Today, 07:40
Joined
Oct 20, 2018
Messages
2,038
MickJav, are you familiar with the approach of looping through form controls where the control type and its Tag identifies a field as being required and then you check its value as in
If Nz(Me.ctl,"") = "" Then << the field is either null or an empty string
It's a lot less code. Am I missing something about that code, such as a capability that the other method doesn't provide?
 

MickJav

AWF VIP
Local time
Today, 12:40
Joined
Nov 28, 2005
Messages
1,432
Yes but I wanted to give each field it's own message so the user know exactly what Is missing.

Also I may not wish to validate every field

@zeroaccess All this does is create a sting if there are any empty fields that you require your user to fill in plus it gives the user a bit ot help as each field error can have a messges telling the use what you expect and or how to corrected it look at the image above the code thats what it creates.

mick
 
Last edited:

Micron

AWF VIP
Local time
Today, 07:40
Joined
Oct 20, 2018
Messages
2,038
Yes but I wanted to give each field it's own message so the user know exactly what Is missing.
Actually, that is part of what I usually use. I use the label caption in a message if they are attached labels (txtCust would be confusing, Customer Name not so much). I also code it so that all missing fields are announced at once, not 4 separate messages because they forgot 4 fields. If I need a custom message, I'd probably get it by splitting the Tag but I've only ever formed messages such as

Please provide missing data for
- customer name
- shipping date
- PO number

and then exit the sub. It's just a personal opinion but your code looks more complicated than it needs to be, unless I've misunderstood something perhaps.
 

MickJav

AWF VIP
Local time
Today, 12:40
Joined
Nov 28, 2005
Messages
1,432
Er = True
It is a bit as I don't normally use the above I would test for empty string in the message string.
It just gives me more control over the information I give to the user I.E. if the field needs any special comments I can add them.
I would expect only one or two data errors as on some fields there may be validation in other forms.
@Micron post your way 2 ways are better than none
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:40
Joined
May 21, 2018
Messages
2,789
You might make it even easier to develop if you had a table. You could use it on every form without having to tag things. Then you do not have to worry about labels and typing into tag properties. Might reduce some development time.

TblValidationMessage
-- FieldName 'lastName
--UserFriendlyName 'Last Name
--ValidationMessage 'The Last Name was left empty, please fill it in.

So the ones that you want validated, loop the control. Check the control source. Then you can find the User Friendly Field Name and the message. Two helper functions.

GetUserFriendly(ctrlSource as string) as string
GetValidationMessage(ctrlsource as string) as string
 

isladogs

CID Moderator
Staff member
Local time
Today, 12:40
Joined
Jan 14, 2017
Messages
12,554
I've moved this thread to the unmoderated Modules & VBA forum to make it easier for those who wish to reply / comment on the code provided
 

Micron

AWF VIP
Local time
Today, 07:40
Joined
Oct 20, 2018
Messages
2,038
The table idea would certainly help if you can't have or don't like attached labels or maybe need custom messages per field, but as for being a bother to put Reqd in Tag property, I don't see an issue. You grab all controls and enter the tag value once and that's it. Other than remembering my method is for attached controls, I don't understand what having to bother with them means. In the end, there are 100 ways to handle this sort of thing and I don't say mine is better, it's just been my method up to now. Here's an approximation of what I use (if there were many more types to worry about I might use a Select Case block.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
Dim strMsg As String

For Each ctl In Me.Controls
  If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
    If ctl.Tag = "Reqd" And Nz(ctl, "") = "" Then strMsg = strMsg & "- " & ctl.Controls(0).Caption & vbCrLf
  End If
Next

If strMsg <> "" Then
  strMsg = "Please enter data for " & vbCrLf & strMsg
  MsgBox strMsg
  Cancel = True
  Exit Sub
End If

End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:40
Joined
May 21, 2018
Messages
2,789
The table idea would certainly help if you can't have or don't like attached labels or maybe need custom messages per field, but as for being a bother to put Reqd in Tag property, I don't see an issue.
I am not suggesting it is better or worse. Just saying in some instances it could save development time. If I want to repeat the checks on other forms I would have to update the tag properties or I would have to copy the controls from another form. This may or not be an issue. With a table it is the same code on all forms.
 

MickJav

AWF VIP
Local time
Today, 12:40
Joined
Nov 28, 2005
Messages
1,432
From what I see I think we will have to agree we all have our ways of doing this job I'm of the view if it works then It works,
each has it's merits but each also has it's drewbacks.
at the end of the day it's down to the individuals preference.
mick
 

MickJav

AWF VIP
Local time
Today, 12:40
Joined
Nov 28, 2005
Messages
1,432
As an after thought there may be a way to mod the code posted by @Micron correct me if I'm wrong but the tag property can hold text of say 255 or more?

so we could combine the regured with the message that I like to use?

Like "Regd:A Question requires a answer"
Think you can see what I did if no regd no message is needed there by leaving the control blank

mick
 

Micron

AWF VIP
Local time
Today, 07:40
Joined
Oct 20, 2018
Messages
2,038
I think the limit is 2048 characters but I'd probably use a table rather than split strings - especially if I wanted to incorporate visibility for controls. In that case a record could hold required option, message, user level visibility or whatever you need.
 

MickJav

AWF VIP
Local time
Today, 12:40
Joined
Nov 28, 2005
Messages
1,432
Sorry @Micron I really dont like the table idea it adds a lot of complications when I post my alteration to your code I'll include suggestions for alterations
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:40
Joined
Feb 19, 2002
Messages
28,619
Although editing all fields and producing this type of multiple error message is not wrong. I don't think it is helpful for the user. Keep in mind that the user has to close the message box before he can fix anything and given my own short term memory, I might remember one or two errors but probably not more than that.

I've been doing this for a very long time and I find that most users once the newness wears off, rarely make multiple errors so the increase in code complexity isn't worth it. I validate one field at a time. If an error is found, I set focus to the field, cancel the update, and provide an error message explaining the error.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:40
Joined
May 21, 2018
Messages
2,789
This is not really my area, but it seems to me the standard now on the Internet is to highlight and identify the required fields. What is the pro/cons of that approach in your opinion? Required.png
I would think this could be shorter to code. I do not see this done as much in Access.
 

MickJav

AWF VIP
Local time
Today, 12:40
Joined
Nov 28, 2005
Messages
1,432
Thanks @theDBguy Was thinking of the back ground But will download it, I have an additional complication with my new programs As I also have to deal with my Style Manager So resetting will be fun but will download allens example thanks
@Pat Hartman I would only expect to have one or two fields that may fail the form level validation if they click out of a record before completing it, My image only showed more as I was testing ALL the required fields just to make sure it all worked.
 

MickJav

AWF VIP
Local time
Today, 12:40
Joined
Nov 28, 2005
Messages
1,432
I have edited @Micron Code so it is now a global function And can be used with any form as below
Calling Function used in the beforeupdate of the form
Code:
On Error GoTo HandleErr

Cancel = Not ValidateForm(Form)

HandleExit:
    Exit Sub
  
HandleErr:
    Select Case Err.Number
        Case 2501 'Cancel = True
            Exit Sub
        Case Else
            MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
            Resume HandleExit
        Resume
    End Select
Add function below to a Module
Code:
Function ValidateForm(frm As Form) As Boolean
'Credit Micron
'Edited By Michael Javes, Database Dreams 24 Feb 2020
Dim ctl As Control
Dim strMsg As String
On Error GoTo HandleErr

For Each ctl In frm.Controls
  If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
    If Left(ctl.Tag, 5) = "Reqd:" And IsNothing(ctl) Then strMsg = strMsg & Replace(ctl.Tag, "Reqd:", "") & vbCrLf
  End If
Next

If strMsg <> "" Then
    MsgBox "The following data errors have been found" & vbCrLf & strMsg & _
    "These errors must be corrected before I can save this record", vbCritical + vbOKOnly, "Incomplete Record"
        Exit Function
End If
ValidateForm = True

HandleExit:
    Exit Function
  
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select
End Function
Function used in above
Code:
Function IsNothing(varToTest As Variant) As Boolean
'Function found on web
On Error GoTo HandleErr

    IsNothing = True

    Select Case VarType(varToTest)
        Case vbEmpty
            Exit Function
        Case vbNull
            Exit Function
        Case vbBoolean
            If varToTest Then IsNothing = False
        Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
            If varToTest <> 0 Then IsNothing = False
        Case vbDate
            IsNothing = False
        Case vbString
            If (Len(varToTest) <> 0 And varToTest <> " ") Then IsNothing = False
    End Select

HandleExit:
    Exit Function

HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select
End Function
The image below shows how to enter the tag info
2020-02-24.png

This is the message generated NOTE: It shows ALL fields only because I am testing it
2020-02-24 (1)S.png
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom