Solved Problem calling Form (Class Object) Code from a Module.

[Marshall Brooks]

Somewhat Obfuscating, but ...

I have a form named "Form A" and another form named "Form B". Both forms have a Field named "REFERENCE". Both Forms have a Private Sub "Reference_AfterUpdate()" which validates the data in the field. That works fine for manual input, but does not work if the field is changed via VBA.

I have a "Duplicate Record" sub on each form that copies SOME of the fields to a new record in the same form. That was not doing the validation, so I added "Call Reference_AfterUpdate" and that works.

In a module, I have a Sub called "Duplicate_Form_A_To_Form_B". This has to be in a main module (not a form module), b/c I am calling it from the right-click menu of a label on Form A. Form A is already open (it is run from there), and it opens Form B, creates a new records, and copies and modifies some of the Form A fields to the fields in the new record in Form B.

However, since it is being done via VBA - it doesn't validate any of the info - might not be an issue since the VBA code should create valid info anyway, but I would like it to check.

If I add:
Call [Form B].REFERENCE_AfterUpdate
I get a compile error - "External Name Not Defined"

If I add:
Call Forms![Form B].REFERENCE_AfterUpdate
I get a syntax error.

Changing the REFERENCE_AfterUpdate from Private to Public didn't make a difference.

Finally - if it can work and changing it to public is required, will I end up with "Ambiguous Name Detected" since I also have "REFERENCE_AfterUpdate" on Form A and a "Duplicate Form B to Form A" subroutine in the Module.

Thanks in advance!!!

 

[Minty]

What is the validation for the REFERENCE field?

I would have thought you could validate the data, without trying/needing to reference the forms control values?
After all you are creating the data via VBA why not validate it in that process.

 

[Marshall Brooks]

The validation checks format and the principle reason is a check to avoid duplicate entries:

Private Sub REFERENCE_AfterUpdate()
' https://www.educba.com/vba-regex/
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
' Begins with I- and then at least 4 and up to five digits and then underscore
' | is Boolean OR
    .Pattern = "^I-\d\d\d\d\d?_"
End With
Do While RegEx.test(Me.REFERENCE.value & "") = False
            InptbxResult = InptBx("Please verify the Reference/Item Number and click OK." & vbCrLf & vbCrLf & _
            "The format should be " & Chr(34) & "I-XXXX_<Suffix><_C, Supp or RXX USCG, USMC, Kuwait" & Chr(34) & ".", "Verify Reference Field:", Me.REFERENCE)
            If InptbxResult = "vbCancel" Then
                Exit Sub
            Else
                Me.REFERENCE.value = InptbxResult
                Me.REFERENCE.BackColor = RGB(255, 235, 0)
            End If
Loop
Set RegEx = Nothing
Dim I_Number As String
I_Number = Me.REFERENCE.value & ""
Screen.MousePointer = 11
     If Nz(ELookup("[REFERENCE]", "[Table B]", "[REFERENCE] = '" & Me.[REFERENCE] & "'"), 0) <> 0 Then
     Screen.MousePointer = 1
        If Box(I_Number & " already exists. Do you want to insert anyway?", vbOKCancel, "Duplicate Warning") = vbCancel Then
            Me.Undo
            Exit Sub
        End If
    End If
Screen.MousePointer = 1
End Sub

I have a couple of fields that get validated to this level.

As you suggest, one simple solution would be to put this code in my module subroutine (and change all the .Me's to Forms![Form B], and that would work.

However (no offense), that sounds to me like saying "You don't need to call this 10-line subroutine 100 times, you can just get rid of the subroutine repeat the 10 lines of code 100 times and have 1000 lines of code rather than 110.

 

[Minty]

No, not at all, I was suggesting if appropriate (and I needed to see what you where doing) moving the validation to a function in a module and passing the value to test into it, rather than referring to form specific controls all the time, that way you can validate from anywhere.

Function fncValidateX(sTheThingToValidate as Variant) as Boolean

fncValidateX = False
If IsNull(sTheThingToValidate)  Then Exit Function

Validation stuff goes here and 
if Validation is ok
     fncValidateX = true
End If

End Function

 

[Marshall Brooks]

That approach should work.

If I understand correctly ...

Instead of having the code above in Form B Reference_AfterUpdate, I would create a new module modDataValidation and move the code to a subroutine there named Form_B_Reference_Afterdupdate() (And change all the .Me references). That code could be called from anywhere, so I would call it from my Duplicate Routine and in Reference_AfterUpdate() I would just add a call to the new procedure in the new module.

I'd end up with the same number of lines as I started with and only one place to make changes/updates.

I like the idea - although it is more coding than I was planning, but ....

THANK YOU!!!

 

[moke123]

As you suggest, one simple solution would be to put this code in my module subroutine (and change all the .Me's to Forms![Form B], and that would work.

You can also pass the form or control objects to your procedure-

Function SomeFunction(frm as form, ctl as control) as boolean

edit -
If you pass the form object you only need change the Me. ' s to frm.

If frm.Somecontrol = x

another edit -
I think if you try to call code in another form you need to reference the form module not the form
Form_frmSomeForm.AfterUpdate

 

[Marshall Brooks]

I think if you try to call code in another form you need to reference the form module not the form
Form_frmSomeForm.AfterUpdate

That was it!!!

Call [Form_Form A].ReferenceAfterUpdate

I also had to change ReferenceAfterUpdate from Private to Public, but changing the same subroutine to public in a different form didn't cause any issues.

It's lazy, but you saved me a bunch of Code re-writing! Thank you!!!

 

[moke123]

it's lazy, but you saved me a bunch of Code re-writing!

Not really .

Public Sub ANewSubName(frm as form)
' https://www.educba.com/vba-regex/
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
' Begins with I- and then at least 4 and up to five digits and then underscore
' | is Boolean OR
    .Pattern = "^I-\d\d\d\d\d?_"
End With
Do While RegEx.test(frm.REFERENCE.value & "") = False
            InptbxResult = InptBx("Please verify the Reference/Item Number and click OK." & vbCrLf & vbCrLf & _
            "The format should be " & Chr(34) & "I-XXXX_<Suffix><_C, Supp or RXX USCG, USMC, Kuwait" & Chr(34) & ".", "Verify Reference Field:", frm.REFERENCE)
            If InptbxResult = "vbCancel" Then
                Exit Sub
            Else
               frm.REFERENCE.value = InptbxResult
                frm.REFERENCE.BackColor = RGB(255, 235, 0)
            End If
Loop
Set RegEx = Nothing
Dim I_Number As String
I_Number = frm.REFERENCE.value & ""
Screen.MousePointer = 11
     If Nz(ELookup("[REFERENCE]", "[Table B]", "[REFERENCE] = '" & frm.[REFERENCE] & "'"), 0) <> 0 Then
     Screen.MousePointer = 1
        If Box(I_Number & " already exists. Do you want to insert anyway?", vbOKCancel, "Duplicate Warning") = vbCancel Then
           frm.Undo
            Exit Sub
        End If
    End If
Screen.MousePointer = 1
End Sub

 

[Pat Hartman]

That works fine for manual input, but does not work if the field is changed via VBA.

That is the expected behavior. The solution is to move the validation code to the Form's BeforeUpdate event. You would have needed validation code there anyway if the field was required since there is no way to validate "required" in the control level events because the control level events do not fire unless the control gets the focus which is exactly what is happening here. When you populate a control using VBA, the control doesn't get the focus, therefore the control level events don't run.