How to use this VBA code to use as Public Subroutine in Access VBA-CodePudding

I have got this code below that restricts users to leave an empty field in a form. Now I want to use this in all of my forms. I've tried to use in Public Subroutine using a module. But it doesn't work.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control


nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls
  If ctl.ControlType = acTextBox Then
    If ctl.Tag = "*" And Trim(ctl & "") = "" Then
     msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
           "You can't save this record until this data is provided!" & nl & _
           "Enter the data and try again . . . "
      Style = vbCritical   vbOKOnly
      Title = "Required Data..."
      MsgBox msg, Style, Title
      ctl.SetFocus
      Cancel = True
      Exit For
    End If
  End If
Next

End Sub

I just want to use this in all of my forms. How do I accomplish this?

CodePudding user response：

Good question, and good idea.

So, keep in mind that "me" is just the current form you are working with.

So, create a plane jane standard code module. And drop in your function like this with a "few" changes.

Public Function CheckRequired(MyMe As Form) As Boolean


  Dim msg        As String
  Dim Style      As Integer
  Dim Title      As String
  Dim nl         As String
  Dim ctl        As Control

  nl = vbCrLf             ' crlf gives you one line

  CheckRequired = False   ' assume everything ok
  
  For Each ctl In MyMe.Controls
    If ctl.ControlType = acTextBox Then
      If ctl.Tag = "*" And Trim(ctl & "") = "" Then
       msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
             "You can't save this record until this data is provided!" & nl & _
             "Enter the data and try again . . . "
        Style = vbCritical   vbOKOnly
        Title = "Required Data..."
        MsgBox msg, Style, Title
        ctl.SetFocus
        CheckRequired = True
        Exit Function
      End If
    End If
  Next

End Function

Now, in the forms event (which has that cancel), then you do this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
   Cancel = CheckRequired(Me)
 
End Sub

CodePudding user response：

As @June7 mentioned, Me is only valid behind forms and reports. It is shorthand alias for the form/report name/object. To achieve what you are looking for, you can try this concept. Create the global routine like below :

Public Function Validate_BeforeUpdate(frm As Form) As Integer

    Dim msg As String, Style As Integer, Title As String
    Dim nl As String, ctl As Control
    

    nl = vbNewLine & vbNewLine

    For Each ctl In frm.Controls
      '''' your other code
      Validate_BeforeUpdate = 1
      Exit For
    Next

End Sub

And to use this from your other forms, do it like below :

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Validate_BeforeUpdate(Me) = 1 Then
        Cancel = True
    End If

End Sub

This is not a tested code, if you follow this idea, you should be okay to have what you are trying to do.