VBA Code Not Working (1 Viewer)

MajP · Feb 9, 2019

Where is the function located please.?

Not sure of the question. The function the user posted would have to be in the forms module since it uses me

Code:

For Each ctl In Me.Detail.Control

But you could make it generic and place in a module and could be called by any form

Code:

Public Function ValDonorFrm(Frm as access.form) as boolean

Dim bCheck As Boolean
Dim ctl As Control

For Each ctl In frm.Detail.Controls
    With ctl
            If .Tag = "v" Then
                If IsNull(.Value) Then
                    MsgBox ctl.Name & " is empty, this is a required field!", vbOKOnly, "Required Field"
                    ctl.SetFocus
                    valDonorFrm = True
                    Exit Function
                End If
            End If
    End With
Next ctl

End Function

MajP · Feb 9, 2019

Two things I would not like about that function is that you get a message only telling you about 1 field, then you try again and get another message about one field. I would return one message about all the required fields. The other thing is that rarely do most people have control names that are descriptive (cboAccountName or txtBoxL_Name). So I would want the option to put descriptive information in the tag property like Account Name or Last Name. Then if there is a description in the tag property use that instead. This may not work great if you have other tags for other purposes, but in that case you revert back to a group tag. Something like this would give you more flexibility. As previously stated I would use this in conjunction with conditional formatting.

Code:

Public Function RequiredFields(Frm As Access.Form, Optional TheTag As String = "R", Optional UseTag = False) As Boolean
'Return true if all required fields are filled in
Dim ctl As Control
Dim Message As String
Dim counter As Integer
RequiredFields = True
For Each ctl In Frm.Detail.Controls
    If ctl.Tag <> "" Then
       If Trim(ctl.Value & " ") = "" Then
           If ctl.Tag = TheTag Then
             Message = Message & ctl.Name & vbCrLf
           ElseIf UseTag Then
             'Usually the control name are not descriptive. You can instead put a descriptive
             'name in the tag property
             Message = Message & ctl.Tag & vbCrLf
           End If
           RequiredFields = False
           counter = counter + 1
        End If
    End If
Next ctl

    If RequiredFields = False Then
      If counter = 1 Then
       Message = "The following field is empty and is required: " & vbCrLf & Message
      Else
       Message = "The following fields are empty and are required: " & vbCrLf & Message
      End If
      MsgBox Message, vbOKOnly, "Required Field"
    End If
End Function

If I put descriptive tags in there and call it

Code:

msgbox RequiredFields(Me, ,True)

I get

Code:

The following fields are empty and are required: 
Account Type
Account Name
Description
Sub Account
Account No

Versus
Putting a group tag and call it

Code:

msgbox RequiredFields(Me,"V")

I get

Code:

The following fields are empty and are required: 
cboType
Text3
Text11
cboSubAccount
Text23

I do not put the validation directly in the Beforeupdate procedure, but do something like this that returns true or false. That allows you to call the validation from other events.

Micron · Feb 9, 2019

This was covered in post 4 and generated zero interest.

Cronk · Feb 10, 2019

The OP in #6 wrote that the initiating event was a button click. Somehow the thread has got off the track with BeforeUpdate of a particular control.

Tupacmoche, your code should be along the lines (consolidating Paul's posts in #2 and #10)

Code:

function ValDonorFrm()
  ...
  'replace the line Cancel = True with
   ValDonorFrm= true
  ....
end function

private Sub yourButton_click()
   'check all data entered
   If ValDonorFrm() = True Then
     'the function already warned the user and set the focus back to where the data is to be entered
     exit sub
  End If
  'continue on with whatever since all required data is entered
   ....
end sub

VBA Code Not Working (1 Viewer)

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

Micron

AWF VIP

Cronk

Registered User.

Similar threads

Users who are viewing this thread