Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Sub Only runs with VBA window open

0 views
Skip to first unread message

Phil Smith

unread,
Dec 3, 2009, 1:49:16 PM12/3/09
to
New symptom here. I have a form. Form has a button. Button has the
following code. If I click the button with the VBA window open, all is
peachykeen. If I hit the button with the window closed, I get:

The expression On Click you entered as the event property setting
produced the following error : Return without Gosub

Seems to me havin gthe VB window opened or closed should make no
difference. Either I have a return or I don't. I don't...


Private Sub Command37_Click()
On Error GoTo Err_Command37_Click

Dim stDocName As String

stDocName = "Load MLR Spreadsheets"
DoCmd.RunMacro stDocName

Exit_Command37_Click:
Exit Sub

Err_Command37_Click:
MsgBox Err.Description
Resume Exit_Command37_Click

End Sub

Tom Lake

unread,
Dec 3, 2009, 3:24:07 PM12/3/09
to

"Phil Smith" <ph...@nhs-inc.com> wrote in message
news:ugnSSlEd...@TK2MSFTNGP02.phx.gbl...

Have you tried it without making the sub private?
Does it make a difference?

Tom Lake

Dirk Goldgar

unread,
Dec 3, 2009, 4:05:36 PM12/3/09
to
"Phil Smith" <ph...@nhs-inc.com> wrote in message
news:ugnSSlEd...@TK2MSFTNGP02.phx.gbl...


I'm not sure what's causing this behavior, but maybe it isn't in that form
itself. With all forms closed. explicitly compile the project (in VBA
Editor, click Debug -> Compile). Are any errors found? If an error is
found, the erroneous line of code should be highlighted.

If nothing is highlighted, maybe there's something in the macro, "Load MLR
Spreadsheets", that depends on the VB Editor being open. What are the
actions in that macro?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Phil Smith

unread,
Dec 3, 2009, 4:21:14 PM12/3/09
to
I don't know why, but removing that from the private realm makes it work
perfectly. Thank you.

Phil

thebigdit

unread,
Jan 10, 2010, 4:49:01 PM1/10/10
to
Hi Phil,

This happened to me with every button on the switchboard, and then when I
open the VBA window and close it again, it seems to work. Until I close the
database and then when I reopen it, the same problem occurred. (I think this
started happening after I performed a defrag on my PC and it seemed to 'lose
the connection' the pointer or something). Anyway, on the very first button
on the switchboard, I just removed the "private" from the first line of code
so that it just starts with 'Sub Command' and voila! It worked. I can now
close the database and reopen it without incident. Strange but true...

"Phil Smith" wrote:

> .
>

0 new messages