Results 1 to 4 of 4
  1. #1
    mjlex is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    3

    Need VBA to open new DB upon click command and run macro

    I'm very self-taught at VBA and have hit a snag that I cannot solve. I have an Access 2010 database from which I need to click a command button that will open another existing database, run a macro in that database, and then close it so I am just left with my original database open. Here's what I have so far...

    Code:
    Public Sub Command109_Click()
            Dim accapp As New Access.Application
            With accapp
                .OpenCurrentDatabase ("C:\HR\Staging 2.accdb"), False
                .Visible = True
                .DoCmd.RunMacro "Import File"
            End With
                
    End Sub
    I've reached this after tons of permutations of code borrowed from 2003 databases that accomplish a similar task and random bits and pieces I've found around the internet. As of right now, when I click the button the other database opens, and I know that macro begins to run because I get a pop-up box that is part of the macro, but then before the macro completes (all it is doing is importing a text file to the 2nd database...) I get run-time error 2501 that my macro action has been cancelled and I can't figure out why.

    Could someone please help me get this code to the point where it either opens the database, runs the macro, and closes the database, or I think I would also be comfortable setting it up to open the database and to have the macro up to autoexec if I can make the sub close the database when the autoexec macro is complete. Any help would be sincerely appreciated!

  2. #2
    mjlex is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    3
    I forgot to mention, the macro runs fine on its own, so I don't think the issue is with the macro. But I could be wrong.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I did a test and the db opens and runs macro that just pops up messagebox then the db close, which is a mystery to me, why does it close and not stay open? I expect this is what you are encountering. After clicking OK on message box the db closes. I added another message box to the macro and it does not pop and now I get the same error message you see.

    The following opens db, runs macro, and leaves db open:

    Shell "C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe C:\Users\June\Forums\Database.mdb /x Macro1", vbMaximizedFocus

    Unfortunately, that won't allow programmatically closing the db because there is not an object opened that VBA can manipulate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    mjlex is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    3
    Quote Originally Posted by June7 View Post
    I did a test and the db opens and runs macro that just pops up messagebox then the db close, which is a mystery to me, why does it close and not stay open? I expect this is what you are encountering. After clicking OK on message box the db closes. I added another message box to the macro and it does not pop and now I get the same error message you see.
    I fixed it! Sort of. My macro had two message boxes (one asking to verify that you want to run the macro, and another signifying that it was finished) and orders to turn errors off/on and turn the hourglass on/off. I didn't touch the code, but I got rid of everything in the macro except that basic function of importing the file, since based on your reply the problem seemed to be somewhere in the msg boxes. Now when I click the command, the other db opens, imports, and closes immediately, though I'm still mystified as to why since I don't have anything in the sub to close it.

    I don't know whether to keep trying to figure it out, or just trust it now that it's doing what I want it to.

    Thank you for the epiphany!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-19-2013, 07:09 AM
  2. Text Box Update After Command Click
    By Joopster in forum Access
    Replies: 3
    Last Post: 02-17-2013, 04:52 PM
  3. Replies: 4
    Last Post: 04-14-2012, 07:22 PM
  4. Command button click event
    By R_jang in forum Programming
    Replies: 10
    Last Post: 10-29-2010, 10:13 PM
  5. Command buttom on click
    By miziri in forum Programming
    Replies: 7
    Last Post: 08-27-2009, 08:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums