I'm stumped with run-time error

[craigachan]

I've reposted this problem in this forum in hopes to get more feedback.

I have this form that works fine in Access 2007 full version. The form is quite complex but it works. The problem is that when I run it in run-time, I get kicked out. I get "Execution of this application has stopped due to run-time error".

Background:

I have a form with many fields called ms1, ms2, ....ms32. On doubleclk ms1, it changes its value by cycling through 3 values "H-Pk", "GBR", and Null. I do this rather than have cbo or list boxes.

Function msCode handles this value change in each of the ms* doubleclicks.

I've tried the suggestions in this forum to find out what the error is,such as cntr-break before the runtime close, but I get closed out and don't see any error number or discription. I've also tried running the runtime on different PCs but get the same each time.


For this example:

-ParamMs = "ms" This is a constant
-Param = "1" - Changes depending on the field
-Field "aa" is used to move the focus off of "ms1" in order to change the value in ms1.

Here is the event for ms1

Private Sub ms1_DblClick(Cancel As Integer)
On Error GoTo ms1_Err

    Param = "1"
    Call msCode
    
ms1_Exit:
    Exit Sub
    
ms1_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume ms1_Exit
    
End Sub

Here is the Function msCode

'-----------------------------------------------
'   Cycles thru HPack, GBR, Null
'
'-----------------------------------------------
Function msCode()

On Error GoTo msCode_err

If IsNull(Me(Me.ParamMs & [Param])) Then
        DoCmd.GoToControl "aa"
        Me(Me.ParamMs & [Param]) = "H-Pk"
        End
        End If
    If Me(Me.ParamMs & [Param]) = "H-Pk" Then
        DoCmd.GoToControl "aa"
        Me(Me.ParamMs & [Param]) = "GBR"
        End
        End If
    If Me(Me.ParamMs & [Param]) = "GBR" Then
        DoCmd.GoToControl "aa"
        Me(Me.ParamMs & [Param]) = Null
        End
        End If

    If Me(Me.ParamMs & [Param]) = "" Then
        DoCmd.GoToControl "aa"
        Me(Me.ParamMs & [Param]) = Null
        End
        End If

msCode_Exit:
    Exit Function
    
msCode_Err:
    MsgBox ("error: " & Err.Number & "Desc: " & Err.discription)
    Resume msCode_Exit
End Function

In Access 2007 full version, I get no errors. I'm not sure why this does not work in runtime. I'm open to any suggestions. Thank you.

 

[ecawilkinson]

Where you have used "end" on its own, replace it with "exit function", e.g.

    If Me(Me.ParamMs & [Param]) = "" Then
        DoCmd.GoToControl "aa"
        Me(Me.ParamMs & [Param]) = Null
        Exit Function
        End If

Do this everywhere, including 'sub' procedures, where you replace it with "Exit Sub".

I have seen this problem before and it is down to a misuse of the keyword "End". It is something to do with the fact the "end" on its own stops the code abruptly without performing any sort of proper clean up. For some reason, you can get away with it in full versions of Access - although it is bad practice, but not in runtime versions.

HTH,
Chris

 

[ecawilkinson]

An example may explain things better.

Sub sub1()
    MsgBox "before sub call"
    Call sub2
    MsgBox "after sub call"
End Sub

Sub sub2()
    End
End Sub

The code never gets to the second msgbox. Whereas, if you replace "end" with "exit sub", the code runs as expected. It seems that "end" stops code execution dead. This may not be apparent in a situation like a simple event procedure. As to why runtime cannot handle, i'm not sure exactly. From memory, as well as stopping code "end" also resets certain variables which runtime may consider necessary.

HTH again,
Chris

 

[craigachan]

That was it! Thanks. I'll have to remember that for the future. In the mean time I have a lot of Find and Replace to do.

Thanks a Million!

 

[ecawilkinson]

be careful with the find and replace. You don't want to end up with lines like:

exit function if

instead of

end if

Chris