Data Validation 'Tag Method'

T

tdefreest

Registered User.
Local time
Yesterday, 22:18
Joined
Dec 16, 2015
Messages
23
Hello,

I am in the process of developing a form for use in sales office to capture opportunity information and have run across a bit of a problem.

I am looking to use the 'tag' data validation method because I wish to have a different input form for the administrator and the sales lead. Sometimes the sales lead will request the administrator to fill in information and not give them the full story, so they still need to get the data in without disrupting the admin's workflow. (We are still working on fixing that process.)

The original post where I captured this method found here: access-programmers[DOT]co.uk/forums/showthread.php?t=207813 - Thank you very much CraigDolphin!! (I had to remove the link due to post count), which uses the following code as a general module:

Code: 
Public Function validateform(myform As Form) As Boolean
'returns true if all required fields have data, or false if not.
'It will also create a popup message explaining which fields need data
 Dim boolresponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolresponse = True
strError = Null
With myform
    For Each ctl In .Controls
      With ctl
        If .Tag = "Required" Then
           If .Value & "" = "" Then
                boolresponse = False
                strError = (strError + ", ") & .Name
            End If
        End If
      End With
    Next ctl
End With
If strError & "" <> "" Then MsgBox "The following information must be entered first: " & strError, vbInformation
validateform = boolresponse
End Function
Then, we call the function using he following code on command click:

Code: 
Private Sub Save_Form_Click()
On Error GoTo Err_Save_Form_Click


If Me.PrimaryID & "" <> "" Then
    If validateform(Me) Then DoCmd.Close
Else
    DoCmd.Close
End If

Exit_Save_Form_Click:
    Exit Sub

Err_Save_Form_Click:
    MsgBox Err.Description
    Resume Exit_Save_Form_Click
    
End Sub
Now, to describe the problem statement.

First, I'm having difficulty getting this code to work correctly on command click and I'm receiving the following Compile Error: Method or Data member not found.

Secondly, I need to modify the code to allow additional code to run as I will also be generating notification emails and creating and saving excel documents upon command click (those codes are already created and working) and (prior to testing this..) I fear that the If Then Else function appears to be closing the form prematurely.

I've only made a few applications with VBA and may just need some discussion to work this problem out. Any help or thoughts would be greatly appreciated.
 
Hi tdefreest,

The code you are using is quite old and has some questionable statements in it. But it should work. First you need to get it compiled.

The tag of every control should have "Required" in its place.

The compile error also shows you where you've gone wrong. It should show you the member/object which you need to declare.

Let me know where it says compile error and we work form there.

HTH:D
 
For your first issue, try
Code: 
validateform(Me[COLOR="Red"].Form[/COLOR])
instead of
Code: 
validateform(Me)

For your second issue, Just add the code you want to execute before you close the form:

Code: 
If Me.PrimaryID & "" <> "" Then
    If validateform(Me.Form) Then 
        'email code here
        'excel code here
        DoCmd.Close
    End If
Else
    DoCmd.Close
End If
 
Fuse3k;XXXXX said:
For your first issue... & For your second issue, Just add the code you want to execute before you close the form
Click to expand...

Thanks for the tips, Fuse3k! I've applied those changes.

Guus2005 said:
Hi tdefreest,

The code you are using is quite old and has some questionable statements in it. But it should work. First you need to get it compiled.

The tag of every control should have "Required" in its place.

The compile error also shows you where you've gone wrong. It should show you the member/object which you need to declare.

Let me know where it says compile error and we work form there.

HTH:D
Click to expand...

So that error occurred at 'PrimaryID', which I now replaced with 'ProposalNumberID'. Now I have a new "compile error: Expected variable or procedure, not module." This occurs at 'validateform(Me.Form).
 
tdefreest,

Is the the procedure "validateform" in the Form's module itself or is it in it's own code module?

If the procedure is in the form's module, the you don't need to pass a reference to the form, you can do this instead:

Your Function:
Code: 
Public Function validateform[COLOR="red"]()[/COLOR] As Boolean
'returns true if all required fields have data, or false if not.
'It will also create a popup message explaining which fields need data
 Dim boolresponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolresponse = True
strError = Null
    For Each ctl In [COLOR="Red"]Me.[/COLOR]Controls
      With ctl
        If .Tag = "Required" Then
           If .Value & "" = "" Then
                boolresponse = False
                strError = (strError + ", ") & .Name
            End If
        End If
      End With
    Next ctl
If strError & "" <> "" Then MsgBox "The following information must be entered first: " & strError, vbInformation
validateform = boolresponse
End Function

CommandButton:
Code: 
Private Sub Save_Form_Click()
On Error GoTo Err_Save_Form_Click


If Me.PrimaryID & "" <> "" Then
    If validateform Then DoCmd.Close
Else
    DoCmd.Close
End If

Exit_Save_Form_Click:
    Exit Sub

Err_Save_Form_Click:
    MsgBox Err.Description
    Resume Exit_Save_Form_Click
    
End Sub

Also, you had two With statements, one nested within another. You want to avoid that because it can confuse VB, as it doesn't know which one one you're referring to. The code I pasted above removed on of the With statements.

Please note, the code I provided does not have the revisions you made based on the post by Guus2005
 
Thank you Fuse3k. The "validateform" module is public, on its own. I was hoping it'd help reduce redundancy keeping it that way. See revisions below:

Command Button:
Code: 
Private Sub Save_Record_Click()
On Error GoTo Err_Save_Record_Click
  
 If Me.ProposalNumberID & "" <> "" Then
    If validateform Then DoCmd.Close
Else
    'placeholder for excel doc code
    'placeholder for email notification code
    DoCmd.Close
End If
  
 Exit_Save_Record_Click:
    Exit Sub
  
 Err_Save_Record_Click:
    MsgBox Err.Description
    Resume Exit_Save_Record_Click
    
End Sub

I'm still receiving "Compile Error: Expected variable or procedure, not module" at the "validateform".

Maybe I need to insert this into the form's private procedure?

I haven't yet made the change to remove the first With statement because I'm thinking that the function is declaring myform as a Boolean variable?
 
You don't need to move it, I was just asking because the answer differs depdending on where the procedure is.

Try changing this:

Code: 
Public Function validateform([COLOR="Red"]ByRef frm as Form[/COLOR]) As Boolean
'returns true if all required fields have data, or false if not.
'It will also create a popup message explaining which fields need data
 Dim boolresponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolresponse = True
strError = Null
    For Each ctl In [COLOR="Red"]frm[/COLOR].Controls
      With ctl
        If .Tag = "Required" Then
           If .Value & "" = "" Then
                boolresponse = False
                strError = (strError + ", ") & .Name
            End If
        End If
      End With
    Next ctl
If strError & "" <> "" Then MsgBox "The following information must be entered first: " & strError, vbInformation
validateform = boolresponse
End Function
 
Fuse3k said:
You don't need to move it, I was just asking because the answer differs depdending on where the procedure is.

Try changing this:

Code: 
Public Function validateform([COLOR=red]ByRef frm as Form[/COLOR]) As Boolean
'returns true if all required fields have data, or false if not.
'It will also create a popup message explaining which fields need data
 Dim boolresponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolresponse = True
strError = Null
    For Each ctl In [COLOR=red]frm[/COLOR].Controls
      With ctl
        If .Tag = "Required" Then
           If .Value & "" = "" Then
                boolresponse = False
                strError = (strError + ", ") & .Name
            End If
        End If
      End With
    Next ctl
If strError & "" <> "" Then MsgBox "The following information must be entered first: " & strError, vbInformation
validateform = boolresponse
End Function
Click to expand...

So I've applied all suggested changes to the public function and the error still persists, see current codes below:

Command Button:
Code: 
Private Sub Save_Record_Click()
On Error GoTo Err_Save_Record_Click
 If Me.ProposalNumberID & "" <> "" Then
    If validateform Then DoCmd.Close
Else
    'placeholder for excel doc code
    'placeholder for email notification code
    DoCmd.Close
End If
 Exit_Save_Record_Click:
    Exit Sub
 Err_Save_Record_Click:
    MsgBox Err.Description
    Resume Exit_Save_Record_Click
    
End Sub

Public Function:
Code: 
Public Function validateform(ByRef frm As Form) As Boolean
'returns true if all required fields have data, or false if not.
'It will also create a popup message explaining which fields need data
Dim boolresponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolresponse = True
strError = Null
    For Each ctl In frm.Controls
      With ctl
        If .Tag = "Required" Then
           If .Value & "" = "" Then
                boolresponse = False
                strError = (strError + ", ") & .Name
            End If
        End If
      End With
    Next ctl
If strError & "" <> "" Then MsgBox "The following information must be entered first: " & strError, vbInformation
validateform = boolresponse
End Function

:banghead:
 
Sorry, go back to Private Sub Save_Record_Click() and change:

Code: 
If validateform Then DoCmd.Close

to:
Code: 
If validateform(Me.Form) Then DoCmd.Close
 
No sorry still receiving Compile Error: Expected variable or procedure, not module. on "validateform" of the command button.
 
Ah, I think I see what the problem is.

Try this:
Code: 
If validateform(Me.Form) Then DoCmd.Close [COLOR="Red"]acForm, Me.Name[/COLOR]

Make sure you do the same with the other DoCmd.Close statement in your function.

Explanation: You need to pass two arguments when calling the DoCmd.Close method. Firstis the type of object you want to close (acForm) and the second is the name of the form (Me.name).
 
Fuse3k said:
Ah, I think I see what the problem is.

Try this:
Code: 
If validateform(Me.Form) Then DoCmd.Close [COLOR=red]acForm, Me.Name[/COLOR]
Make sure you do the same with the other DoCmd.Close statement in your function.

Explanation: You need to pass two arguments when calling the DoCmd.Close method. Firstis the type of object you want to close (acForm) and the second is the name of the form (Me.name).
Click to expand...

Hmm.. still no dice. I tried using both Me.Name and Me.frmPROPOSALINFORMATIONInputForm. See code below:

Code: 
Private Sub Save_Record_Click()
On Error GoTo Err_Save_Record_Click
 If Me.ProposalNumberID & "" <> "" Then
    If validateform(Me.Form) Then DoCmd.Close acForm, Me.frmPROPOSALINFORMATIONInputForm
Else
    'placeholder for excel doc code
    'placeholder for email notification code
    DoCmd.Close acForm, Me.frmPROPOSALINFORMATIONInputForm
End If
 Exit_Save_Record_Click:
    Exit Sub
 Err_Save_Record_Click:
    MsgBox Err.Description
    Resume Exit_Save_Record_Click
    
End Sub
 
Strange. Are you getting the same error message or something different?
 
Fuse3k said:
Strange. Are you getting the same error message or something different?
Click to expand...

The same error message.

Code: 
Compile Error: Expected variable or procedure, not module.

And if I move the function into the form's private module, the error changes to:

Code: 
Compile error: wrong number of arguments or invalid property assignment.
 
Ok, we need to drill down further.

Can you change:
Code: 
If validateform(Me.Form) Then DoCmd.Close acForm, Me.Name

to this:

Code: 
If validateform(Me.Form) Then 
     DoCmd.Close acForm, Me.Name
End If

Compile again and tell me which line it highlights when the error is displayed.
 
Fuse3k said:
Ok, we need to drill down further.

Can you change:
Code: 
If validateform(Me.Form) Then DoCmd.Close acForm, Me.Name
to this:

Code: 
If validateform(Me.Form) Then 
     DoCmd.Close acForm, Me.Name
End If
Compile again and tell me which line it highlights when the error is displayed.
Click to expand...

Same error at the same location of the code "validateform".
 
If you're willing to consider a change to your approach you can do the following to get the same exact result. It's also a little more cleaner.

Instead of a function, just validate the code by using the BeforeUpdate event:

Code: 
'Cancels Update if required fields are missing.
'if fields are missing, It will  create a popup message explaining which fields need data
' Form is saved and closed if all required fields have data

Dim strError As String
Dim ctl As Control
Dim blnIsValid As Boolean

strError = "Validation failed for the following reasons: " & vbNewLine
blnIsValid = True

For Each ctl In Me.Controls
    With ctl
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionButton, acOptionGroup, acToggleButton
                If Nz(.Tag) = "Required" And Nz(.Value) = "" Then
                    blnIsValid = False
                    strError = strError & "   - " & ctl.Name & " is required" & vbNewLine
                End If
        End Select
    End With
Next ctl

If blnIsValid = False Then
    'Form data is NOT valid!
    Cancel = True 'Cancel the update
    MsgBox strError, vbInformation
    'EMAIL CODE HERE
    'EXCEL COE HERE
End If

Set ctl = Nothing

I also attached a sample ACCDB file to see it in action.
 

Attachments

Fuse3k said:
If you're willing to consider a change to your approach you can do the following to get the same exact result. It's also a little more cleaner.

Instead of a function, just validate the code by using the BeforeUpdate event:

Code: 
'Cancels Update if required fields are missing.
'if fields are missing, It will  create a popup message explaining which fields need data
' Form is saved and closed if all required fields have data

Dim strError As String
Dim ctl As Control
Dim blnIsValid As Boolean

strError = "Validation failed for the following reasons: " & vbNewLine
blnIsValid = True

For Each ctl In Me.Controls
    With ctl
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionButton, acOptionGroup, acToggleButton
                If Nz(.Tag) = "Required" And Nz(.Value) = "" Then
                    blnIsValid = False
                    strError = strError & "   - " & ctl.Name & " is required" & vbNewLine
                End If
        End Select
    End With
Next ctl

If blnIsValid = False Then
    'Form data is NOT valid!
    Cancel = True 'Cancel the update
    MsgBox strError, vbInformation
    'EMAIL CODE HERE
    'EXCEL COE HERE
End If

Set ctl = Nothing
I also attached a sample ACCDB file to see it in action.
Click to expand...

This looks like it would work. I took a look at your sample database and it's pretty lean. Now when I try the same code in my database, I receive the following error:

"Run-time error '2424': The expression you entered has a field, control, or property name that Microsoft Office Access can't find."

Code: 
                 If Nz(.Tag) = "Required" And Nz(.Value) = "" Then
 
It worked fine for me using Access 2010. Maybe its the nz() function? Try removing them but I'm not sure why you'd get that error. I'm sure you get the gist of it though, using the BeforeUpdate event.
 
IDK I'm able to open your sample database and get that to work flawless using Access 2007. I'm thinking its possible there's something weird going on with the actual fields on my form. I'm going trough each field painstakingly trying to debug this right now..

What would I use instead of Nz? I tried the below:

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Cancels Update if required fields are missing.
'if fields are missing, It will  create a popup message explaining which fields need data
' Form is saved and closed if all required fields have data
 Dim strError As String
Dim ctl As Control
Dim blnIsValid As Boolean
 strError = "Validation failed for the following reasons: " & vbNewLine
blnIsValid = True
 For Each ctl In Me.Controls
    With ctl
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionButton, acOptionGroup, acToggleButton
                'If .Tag = "Required" Then
                'If .Value & "" = "" Then
                If Nz(.Tag) = "Required" Then
                If Nz(.Value) = "" Then
                    blnIsValid = False
                    strError = strError & "   - " & ctl.Name & " is required" & vbNewLine
                
                End If
        End Select
    End With
Next ctl
 If blnIsValid = False Then
    'Form data is NOT valid!
    Cancel = True 'Cancel the update
    MsgBox strError, vbInformation
End If
 Set ctl = Nothing
    'EMAIL CODE HERE
    'EXCEL COE HERE
    Me!EditedWhen = Now()
    
End Sub

and now I received the following error:
---
The expression On click you entered as the event property setting produced the following error: End Select without Select Case.
*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

Show Help >>
 
Last edited:
You must log in or register to reply here.

Similar threads

T
Invalid Qualifier
Replies
5
Views
315
TheSearcher
T
M
acCmdSpelling doesn't move out of field when spell check is done
2
Replies
33
Views
1,128
NauticalGent
NauticalGent
jwcolby54
OpenArgs (my) class way
3 4 5
Replies
89
Views
1,973
Josef P.
J
oxicottin
Solved Multiselect listbox loop showing there isn't data
Replies
4
Views
677
pbaldy
pbaldy
V
Need help with VBA to check for required fields
Replies
6
Views
543
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom