Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39

    Unanswered: Access 2003 Module Question 'how to play' automatically

    Hi, every day at work I import data into a db in access 2003 via a macro using transfer text into a table from a download. From that main table I run a few queries until I end up appending data to a final table which contains the information that I wish to work with. Because there is often overlapping days or duplicated data from the download, I have a module which I found that deletes the duplicate information. But it only does this if the module is open and I click on the play button which then opens another window to carry out the actual function of deleting the duplicates. I started to create a macro that automates the import of the .txt file. Then runs the queries in order, but when I get to the point where I want to open the module and 'play it' I don't know how to automate that part.

    If anyone can help with this I would really appreciate it.

    Regards,

    Timmeh

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You're saying you have a macro and you want to run the code from it? You'd use the RunCode action with the name of the function. If you want to start it when the db starts, a macro named "autoexec" will run when the db opens.
    Paul

  3. #3
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    That's right, I thought I would put all the queries that need to run within a macro and I was going to also hoping to make part of that macro open or automatically run the module that deletes the duplicate information from the table that I import the data into initially. I have tried to use the runcode selection and have entered the module name in the field but it tells me there are 'arguments' needed or something. When I run the module manually by pressing the play button on the visual basic toolbar it opens another window with the selection to deletedups which I do then I have to close that window. I then go and check the table and hey presto the duplicates have gone leaving one record.
    Tim

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    To clarify, a module contains functions and subs. You want the name of a function, not the name of the module. What is the function and how are you calling it in the macro?
    Paul

  5. #5
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Quote Originally Posted by pbaldy View Post
    To clarify, a module contains functions and subs. You want the name of a function, not the name of the module. What is the function and how are you calling it in the macro?
    I haven't got the faintest idea about the name of the function and how I am meant to call it in the macro. I better do some research on how to identify which part is the actual function and then how to call it in a macro. Thanks for leading me through this so far.

    Tim

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Does this help?
    Attached Thumbnails Attached Thumbnails RunCode.JPG  
    Paul

  7. #7
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Quote Originally Posted by pbaldy View Post
    Does this help?
    That certainly helps. But does this match. The module code I have found doesn't seem to contain a public function. The attachment shows how the code begins.
    Attached Thumbnails Attached Thumbnails Remove Dups.jpg  

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The code should be in a standard module, not behind a form, and I'm pretty sure it has to be a function, not a sub. That part is as easy as changing "Sub" to "Function", in both the starting and ending lines.
    Paul

  9. #9
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Quote Originally Posted by pbaldy View Post
    The code should be in a standard module, not behind a form, and I'm pretty sure it has to be a function, not a sub. That part is as easy as changing "Sub" to "Function", in both the starting and ending lines.
    Thanks so much. I did as you suggested and changed the "Sub" to "Function" then entered "Remove_Duplicates()" in the runcode Function name part in the macro and it ran like a charm. I really appreciate your help guys.

    Tim

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •