Command button VB code

[Amateur]

I am trying to run a Macro from another DB with a command button and have the
following code:

Private Sub Command20_Click()
Call Shell("""C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE""""C:\cps208\writeinvoices.mdb""/X
invoicenumbersdelete", 1)
End Sub

The Paths MSAccess.exe and my database are correct. I get the following
error message:
"The command line you used to start Microsoft Office Access contains an
option that Microsoft Office Access doesn't recognize."
Can someone tell me what that means and how to correct it?
Thanks
Klaus

 

[Wayne-I-M]

Looks OK to me
Have you checked
C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE

All this should be one line
Call Shell("""C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE""" "C:\cps208\writeinvoices.mdb""/X
invoicenumbersdelete", 1)

Do you have a folder on C drive called
cps208

Do you have a DB call
writeinvoices.mdb
In folder cps208


etc
etc

 

[Amateur]

Hi Wayne
Everything is on one line and I can answer all your questions with YES.
Any other idea how to run my Macro from another DB?
Thanks
Klaus

 

[Douglas J. Steele]

You're missing spaces between the name of the executable and the name of the
mdb file, as well as between the name of mdb file and the option:

Private Sub Command20_Click()
Call Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""
""C:\cps208\writeinvoices.mdb"" /X invoicenumbersdelete", 1)

End Sub

 

[Amateur]

Hi Wayne,
I had a space between: /X invoicenumbersdelete", 1) - I closed it to:
/Xinvoicenumbersdelete", 1) --------- but I still don't have my result.
Now, if I click my command button, MS Access opens - but I only would like
that my Macro (invoicenumbersdelete) from writeinvoices.mbd is running.
What am I doing wrong?
Thanks
Klaus

 

[Amateur]

Hi Douglas,
OK, I entered the spaces. It's now looking like this:

Private Sub Command20_Click()
Call Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""""
C:\cps208\writeinvoices.mdb"" /X invoicenumbersdelete", 1)
End Sub

Result - writeinvoices.mdb is opening - but that is not the point, I only
would like to have my Macro (invoicenumbersdelete) running, without anything
is opening or changing in my main window.
Is that somehow possible?
Thanks
Klaus

 

[Douglas J. Steele]

I'm surprised it's opening, because you didn't do it correctly! Rather than

.EXE"""" C:\

, it should be

.EXE"" ""C:\

I'm not sure what you're referring to as the "main window".

Do you mean you don't want to see the new instance of Access open up? Rather
than ,1 in your Shell command, use , 0 (that'll hide the window). However,
you can only do that if your macro finishes by shutting the database down.

On the other hand, if you're wanting the new instance of Access to be
visible, but you don't want to see the database window, etc., you can shut
all those down under Tools | Startup.

 

[Amateur]

I changed into:

Private Sub Command20_Click()
Call Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"" ""
C:\cps208\writeinvoices.mdb"" /X invoicenumbersdelete", 1)
End Sub

Result: Error message - "Microsoft Office Access is unable to open the data
access page. The path specified was invalid, or may be too long".

Puhh, what is this again? I do not want to open any data access page, I
would like my Macro to run - what did I do wrong again now?

Thanks for your help.
Klaus

 

[Amateur]

forget my last post, now I did as you said - I changed into:

Private Sub Command20_Click()
Call Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""
""C:\cps208\writeinvoices.mdb"" /X invoicenumbersdelete", 1)
End Sub

Anyway, the writeinvoices.mbd is opening but my Macro is not running
automatically.
What is still wrong?

And if I use the Shell command 0, how can I shut down the database
writeinvoices.mbd in the same Macro?
I saw in the Macro design vieu the "close" command - but in the list it
doesn't say database.

Klaus

 

[Wayne-I-M]

Hi

There is a way to test what your doing

Create a new blank database (in the same folder as your oringinal DB - the
one that is giving you a problem) - call it test1
In this new DB create a form - call it form1
Open the properties box and in the event column create a macro OnOpen
The macro should be a message box
Title = This is working fine
Message = Hello
Save the macro as testmacro1

Close the DB

Open another new DB call it - test2
Create a form with a button
OnClick of the button shoul be


Private Sub testbutton_Click()
Call Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""
""C:\My Documents\test1.mdb"" /X testmacro1", 1)
End Sub

Note all but the 1st and last line are on one line.
Check the path (it may be different on your machine)

Click it see if it works.
If it does this means your paths are OK and so is your code (I tested the
above and it works fine) - which must mean there is a problem with the macro
you are trying to action in your orginal question.

Hope this helps