Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error From Macro Executing VBA Code (Access 2003 SP2 (11.6566.8036))

    Hello All,

    A new responsibility was recently dropped in my lap. For several years, we've been uploading a children's immunizations data file, monthly, to the State. The process for creating the file is currently manual, requiring the results of a report to be saved as a delimited text file. The file is then imported into an Access table, which is checked and manipulated by several queries to insure the data meets State formatting requirements. The table is then renamed to include the current date (Format: "ALERT yyyymmdd"), and kept as an archived file per government retention requirements. It is then exported to a delimited file which is transmitted to the State.
    Since I have enough on my plate without adding another hour and a half procedure, each month, I decided to automate the Access portion of the process using a Macro. Realizing that I couldn

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error From Macro Executing VBA Code (Access 2003 SP2 (11.6566.8036))

    Make sure that you put a pair of parentheses after the function name in the Function name argument. Valid "looks" for the argument are
    <code>
    RenameALERT()
    </code>
    or
    <code>
    =RenameALERT()
    </code>
    but *NOT* merely
    <code>
    RenameALERT</code>

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error From Macro Executing VBA Code (Access 2003 SP2 (11.6566.8036))

    Hans,

    I've tried it with and without the parentheses, with and without the equals (=) sign, and it always produces the same error.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error From Macro Executing VBA Code (Access 2003 SP2 (11.6566.8036))

    Could you do the following::
    - Create a copy of the database and open the copy.
    - Remove everything (tables, queries, forms, reports, other macros) except the module and the macro causing the problem.
    - Select Tools | Database Utilities | Compact and Repair Database...
    - Quit Access.
    - Zip the copy and attach it to a reply.
    Thanks in advance.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error From Macro Executing VBA Code (Access 20

    Hi Jamie

    This is really weird, I have just used similar code to yours ( I renamed the table in my test database and it runs fine....Can you strip the database down in any way so that we can look at it, you know, second pair of eyes etc.
    Jerry

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Error From Macro Executing VBA Code (Access 20

    Like Jerry I found your code worked OK for me.

    Have you attached the macro to a command button on a form, or do you just run it from the db window.

    If you have a command button, try taking the one line of code from your function, and attaching it driectly the command button, so that you leave out the macro and the function call.

    DoCmd.Rename "ALERT " & Format(Date, "yyyymmdd"), acTable, "ALERT Current"
    Regards
    John



  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error From Macro Executing VBA Code (Access 2003 SP2 (11.6566.8036))

    Hans, Jerry, John,

    I can't explain it, but, it's working now. I had been running the macro from within the design view by clicking on the "bang" (!). It errored out every time. Then, after you requested I send a copy, I ran it one more time (by accident) while it was not open, and it worked. Now it works whether the macro's open or closed, or even when I double click on the macro icon. I think there may be gremlins in the machine.
    Anyway, thank you for all of your time and thought. It's very reasuring that all of you are there for me (and everyone else) to inquire of.

Posting Permissions

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