VBA stops running (1 Viewer)

evanscamman · May 23, 2010

This is really weird. I've been using this little function for years with no problem. It generates a message box where the first sentence is in bolt type (similar to the built in MS Access messages.)

It has always worked until today. Now (and only in one case on one form), it will generate the message but then VBA abruptly aborts. No further code is run after that point - it doesn't even finish the procedure.
No results are returned.

Code:

Function CustomMsgBox(strBold As String, strMsg As String, intButton As Integer, strTitle As String) As Integer
    On Error GoTo ErrLog
    'For some reason Eval statement used with MsgBox will provide a line in bold text
    CustomMsgBox = Eval("MsgBox ('" & strBold & "@" & strMsg & "@@'," & intButton & ",'" & strTitle & "')")
 
    Exit Function
ErrLog:
    Call ErrorLog(Err.Number, Err.Description, "modCommon.CustomMsgBox")
    Resume Next
End Function

I don't think the eval statement itself is the problem, because it works in every other instance. In fact, the same statement that calls the CustomMsgBox routine works just fine in another instance - the only thing that has changes from ine instance to the next is the value of the fields on the form.

I would post the rest of my code and form here, but there are so many subroutines that it would be totally confusing. I'm wondering can anybody shed some light on what would cause VB to stop code execution with no errors?

Thank you,
Evna

Banana · May 23, 2010

Since you say it only happens in this one instance... what are the actual values for all those parameters in that instance?

evanscamman · May 23, 2010

Banana - thanks for the reply.

First, I've ruled out the eval statement...
Just a plain old message box will do the trick.

It appears that something happens on my form, and then the next message box event will cause code execution to stop abruptly.

Code:

MsgBox "1"
MsgBox "2"

In one instance, I will get both msg boxes subsequently.
I click around on the form a little bit, change a field from 100 to 0 and then I only get the first msgbox - then code stops.

It's hard to say what has changed on my form, because there are lots of variables, subforms and fields, etc...

Before I get into that, I wondering if there are any general pointers of things that may cause code to stop with no warning.

Or, are there ways to repair code to get rid of 'bugs' that may have crept in?

Thanks,
Evan

The_Doc_Man · May 23, 2010

Code won't stop - but it might get hijacked. Do you know if something fires either an event or a trap? Both of those will have the appearance of stopping code from running, though in fact it is running. Just not where you thought.

Subforms are notorious for hijacking "locus of control" as are some of the less obvious subroutines associated with filesearch or fileopen objects. I'm fighting one case right now where I select a file name and return it but code somehow isn't running where I think it SHOULD be running. I can prove that because if - as you describe - you "poke around" on the form, other events and changes occur. But the code I was expecting to take a breakpoint doesn't do so.

evanscamman · May 23, 2010

I stepped through the code line by line. After the msgbox event it simply goes nowhere - just ends abruptly.

Banana · May 23, 2010

Evan, since you asked, two options to clean out the dust and roaches:

1) Create a new blank database, turn off AutoCorrupt, then import in all objects into this new blank database.

2) De-compiling. There's lot of articles on this if you google.

I prefer to do #1 though #2 is good if _sparingly_ used.

The_Doc_Man:

Well, my experience has been that lot of the jumping around can be caused by any kind of actions that causes a requery, an event that is more likely to happen on a complex form with lot of subform. For this reason, if I can code something that does not require a requery, much the better, IMHO.

To provide a concrete example for everybody's benefits - I never would do something like this:

Code:

Public Sub AddNewRecordToMyForm()

DoCmd.RunSQL "INSERT INTO MyTable (...) VALUES (...);"

Me.Requery

End Sub

Nor would I do this:

Code:

Public Sub AddNewRecordToMyForm()

DoCmd.GoToRecord acNew
With Me
  !MyTextBox = "Something"
  !MyComboBox = 1
End With

End Sub

Rather, I'd sooner do this:

Code:

Public Sub AddNewRecordToMyForm()

Me.Recordset.AddNew
With Me.Recordset
   .Fields("Text") = "Something"
   .Fields("Lookup") = 1
End With

End Sub

Be aware that this does not necessarily resolve the "jumping around" completely though I would say it does reduce the need to do so and by working at low level (e.g. the recordset) rather than at form object or application (e.g. DoCmd), the behavior is more predictable. At least that's what my experience has been.

evanscamman · May 23, 2010

Good tip, Banana. This could certainly help me out with the "data has changed" errors I deal with frequently.

My bug seems to have gone away... I'd feel better If I had solved it in a concrete manner, rather than it just disappearing - but at least it is gone.

Thanks!
Evan

Banana · May 23, 2010

evanscamman said:
Good tip, Banana. This could certainly help me out with the "data has changed" errors I deal with frequently.

Indeed it will. The problem is that any RDBMS has no way of knowing whether two connections actually are from same client. So if a client open a connection, and get a lock on an object (could be a row, could be a page, could be a table, depending on which backend we're talking about and what operations it is doing) then opens another connection to the same backend, the backend will perceive it as a new client even though it seems to come from the same client and thus treat the request to same area as a conflicting request.

The techniques of using Recordset or Form object ensure that you submit changes via the same connection so the conflict do not happen.

My bug seems to have gone away... I'd feel better If I had solved it in a concrete manner, rather than it just disappearing - but at least it is gone.

Frustrating, indeed. A thought, though. Could it be actually external to the source? That is, maybe there's another application running or maybe it was running in a different OS or something like that could interfere with the normal operation?

evanscamman · May 23, 2010

I suppose it could be possible that it was an external application, however, as the bug was 100% repeatable, when certain conditions were met, it seems unlikely.

gemma-the-husky · May 24, 2010

well, it would end wouldnt it.

in your code, you call eval(msgbox), and then just end the function.
(note that eval (msgbox) seems pointless in itself)

thats it - you now have no active event running

what are you execting to happen?

DCrake · May 24, 2010

In my example below it only show singe @ signs in the OP it shows @@ together. Is this the issue?

Call the formatted message box:

Code:

FMsgBox "User name is a mandatory item.@Cannot save without a valid user name@", vbExclamation + vbOKOnly, "Save Failed"

Public Function:

Code:

Function FMsgBox(Prompt As String, Optional Btns As Integer, Optional Ttl As String = vbNullString, _
 Optional HFile As Variant, Optional Context As Variant) As Integer

If IsMissing(HFile) Or IsMissing(Context) Then
    FMsgBox = Eval("msgbox(""" & Prompt & """, " & Btns & ", """ & Ttl & """)")
Else
    FMsgBox = Eval("msgbox(""" & Prompt & """, " & Btns & ", """ & Ttl & """, """ & HFile & """, " & Context & ")")
End If

End Function

evanscamman · May 25, 2010

Gemma, by 'ending' I meant that the code did not even get to the 'End Function' statement. No value was returned, and the calling procedure did not resume either. Turns out that the eval statement was not the culprit. It had something to do with the msgbox. At certain times on my form a msgbox statement would cause VB to terminate abruptly. The problem eventually went away.

Thanks for the help.

merrywhale · Nov 6, 2010

Guyss! I faced the same problem and finally figured out what caused such a weird behavior.
This kind of thing occurs if OnTimer event fires whilst a MsgBox is open (waiting for user's reaction). So the workaround is simple: just make sure that TimerInterval is set to 0 before calling MsgBox. You may still re-enable timer after the call to MsgBox is completed. That's it!

VBA stops running (1 Viewer)

evanscamman

Registered User.

Banana

split with a cherry atop.

evanscamman

Registered User.

The_Doc_Man

Immoderate Moderator

evanscamman

Registered User.

Banana

split with a cherry atop.

evanscamman

Registered User.

Banana

split with a cherry atop.

evanscamman

Registered User.

gemma-the-husky

Super Moderator

DCrake

Remembered

evanscamman

Registered User.

merrywhale

New member

Similar threads

Users who are viewing this thread