Bizarre problem with VBA in an Access Form

Peter R Fletcher

I am calling Form_BeforeUpdate directly from a Close (Form) button's onClick Event, to handle various circumstances when the form is only partially filled in but the User wants to bail out. Under certain (perfectly feasible) circumstances, Form_BeforeUpdate is correctly setting Cancel to True within itself, but it is not being passed back to the calling Sub! Subroutine parameters are ByRef by default, but setting Cancel explicitly to ByRef in the definition of Form_BeforeUpdate does not change the misbehavior. Here are the two Subs:

Private Sub cmdClose_Click()
Dim Cancel As Integer
Form_BeforeUpdate (Cancel)
If Cancel Then
If MsgBox("This record contains errors. Do you wish to correct them before closing the form?", vbYesNo, "Errors") = vbYes Then
Exit Sub
Else
If Me.NewRecord Then
MsgBox "The current record will be discarded.", vbInformation
Else
MsgBox "Any changes made to the current record will be discarded.", vbInformation
End If
Me.Undo
End If
End If
blMayClose = True
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As New ADODB.Recordset

If Me.NewRecord Then Contact\_Pt = lbxContacts.Value

Set rst = New ADODB.Recordset
rst.Open "SELECT ID FROM Assets WHERE package\_Pt = " & ID, CurrentProject.Connection, adOpenStatic, adLockReadOnly
If Not (rst.EOF And rst.BOF) Then
    If IsNull(Start\_Date.Value) Or IsNull(End\_Date.Value) Then 'this is where Cancel gets set to True
        MsgBox "You must enter start and end dates for the package before saving it.", vbExclamation, "Dates Missing"
        Cancel = True
    ElseIf End\_Date.Value <= Start\_Date.Value Then
        MsgBox "The end date must be after the start date.", vbExclamation, "Invalid dates"
        Cancel = True
    End If
End If
rst.Close
Set rst = Nothing

End Sub

CHill60

The way you are calling the sub is strange

Form_BeforeUpdate (Cancel)

That should be either

Call Form_BeforeUpdate(Cancel)

or

Form_BeforeUpdate Cancel 'No parentheses

I can't remember what surrounding a variable in parentheses does, other than make that code not work obviously :-D . I'll have a dig around, but in the meantime either insert "Call" or drop the ()

CHill60

Found it! Using the parentheses is forcing VBA to evaluate the bracketed expression (in this case, your variable Cancel) and then pass it as ByVal into the subroutine. Ergo the value remains unchanged in the calling sub. See Matthieu Guindon's solution here [^] for a fuller explanation

Peter R Fletcher

Thanks! I had given up and used a workaround (which actually turned out to have other advantages, so it wasn't all bad). VBA's requirement that you not surround sub/function arguments with parens (unless you make an explicit Call) is always catching me out, since every other language I use require the parens. Fortunately, it mostly affects implicit calls with multiple arguments, where putting in the parens provokes an immediate syntax error in the editor. This case is a particular pain since the bad syntax is not flagged but nevertheless changes the meaning of the call.

Dave Kreskowiak

VBScript and VBA require parenthesis around the parameters of functions that return a value. For functions that don't returns a value, no parenthesis go around the parameter list. It's the stupidest thing I've ever seen. On, and you never need to use "Call" to call another function at all.

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

CHill60

1:

It's the stupidest thing I've ever seen.

I couldn't agree with you more!

2:

and you never need to use "Call" to call another function at all.

Except when you put parentheses around your parameter list. In which case quote 1 applies :laugh:

Dave Kreskowiak

In the mountain of VB6, VBA, and VBScript code I've written, I've never used 'Call'. It just seemed so COBOL to me, YUK!

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

CHill60

:laugh: