Results 1 to 5 of 5

Trouble launching macro

  1. #1
    veejay is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2018
    Montreal, Canada

    Trouble launching macro

    On a previous version of my database I created a form with a button. On click Visual Basic would run a module.

    Private Sub cmdTranspose_Click()
    End Sub
    And the modulecode:

    Sub Transpose()
        ' requires reference to Microsoft DAO
        Dim rs As DAO.Recordset
        Dim Counter As Long
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM [TableXYZ]")
        With DoCmd
            .SetWarnings False
            For Counter = 5 To rs.Fields.Count
                .RunSQL "INSERT INTO [PermanentTable] ([DATE],[USER],[XYZ],[APP], [ORRR], [GRADE]) " & _
                    "SELECT [DATE],[USER],[XYZ],[APP], '" & rs.Fields(Counter - 1).Name & "', " & _
                    "[" & rs.Fields(Counter - 1).Name & "] " & _
                    "FROM [TableXYZ]"
            .SetWarnings True
        End With
        Set rs = Nothing
        MsgBox "Done"
    End Sub
    For some reason when i'm on the old version of the database this button is working.
    On the new version when I click I don't get anything.
    The code is exactly the same and I have no idea where to look for an explanation to this problem.

    This is really problematic as I can no longer move the information from my tableXYZ to the PermanentTable which is I would say the base for every query/form/report i'm using.

    I have no idea what change as this wasn't updated.

    Any idea where to start looking for a culprit?

    I have updated the code from the button to this:

    Private Sub cmdTranspose_Click()
    On Error GoTo Err_cmdTranspose_Click
        Exit Sub
        MsgBox Err.Description
        Resume Exit_cmdTranspose_Click
    End Sub
    And it's returning the following error: "Too few parameters. Expected 2"


    Just to add a little bit more information here the expected result
    From TableXYZ:

    Date User XYZ APP A1A A2A A3A A4A

    05/12/2018 001 003 127462 5 2 1 5
    04/12/2018 002 003 457215 3 1 2 5

    What it looks like in Permanent Table:

    Date User XYZ APP ORRR Grade

    05/12/2018 001 003 127462 A1A 5
    05/12/2018 001 003 127462 A2A 2
    05/12/2018 001 003 127462 A3A 1
    05/12/2018 001 003 127462 A4A 5
    04/12/2018 002 003 457215 A1A 3
    04/12/2018 002 003 457215 A2A 1
    04/12/2018 002 003 457215 A3A 2
    04/12/2018 002 003 457215 A4A 5

    If anyone has a better idea on how to achieve this.
    It needs to remain an "onclick" thing I don't want this to happend all the time as this slows down my database considerably.
    Last edited by veejay; 12-05-2018 at 07:31 AM. Reason: updated code from visual basic

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Nevada, USA
    I've never specified the module name. That error usually means something is spelled wrong in the SQL. Use a variable and this method so you can see the finished SQL:
    Paul (wino moderator)
    MS Access MVP

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Richmond (Virginia, not North Yorkshire!)
    While using the module name, such as


    does seem to work, I, too, have never seen it used in VBA for Access...generally you'd simply use


    Also...per Access Help

    (With...End With) executes a series of statements on a single object or a user-defined type.

    And DoCmd is neither. Testing it, trying to use this syntax, does, indeed, error out.

    What version of Access was the original db, that works, developed in...or was it actually written in Access? You did, after all, mention that "On click Visual Basic would run a module..." Was it actually written in Visual Basic? Visual Basic is not the same as VBA for Access, even though the names of many things are similar.

    Linq ;0)>
    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Ontario, Canada
    To the good advice proffered and questions above I'd add that I think this is the type of behaviour one can expect when you turn warnings off and execute action queries. The reason I "think" that is because I haven't done so for a long time, preferring the Execute method of CurrentDatabase along with failure parameter, as in
    Dim db as DAO Database
    Set db = CurrentDb
    db.Execute sql, dbFailOnError

    I'm rusty on exactly what is the scope of warnings that will be repressed.

    The new version must be missing something that the old one had, although I wouldn't expect it to be a library reference given the error you're saying occurs. Maybe other lurking error traps:
    - you don't check to see if the opened recordset even contains records before trying to use it, which is dicey.
    - if you try to close a recordset that didn't open, another error
    - if your transpose code errs out in the right place, warnings will remain off either until the db is reopened or they happen to be turned on again. Neither it or the calling code will turn them back on.

    If you're simply trying to remove a letter from a field value, the Replace function might be much simpler. Maybe provide more info about what you have and what you're trying to accomplish rather than what you're doing. It looks to me that the Fields(#) reference is the same for both Fields.
    Last edited by Micron; 12-06-2018 at 07:08 PM. Reason: spelin and gramur
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  5. #5
    veejay is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2018
    Montreal, Canada
    Alright thanks for your answers. Will be looking into this!

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

Similar Threads

  1. Replies: 2
    Last Post: 10-18-2017, 02:26 PM
  2. Trouble with macro within a subform
    By MLangendorf in forum Forms
    Replies: 12
    Last Post: 12-13-2016, 12:22 PM
  3. UI Macro Trouble
    By beetgirl in forum Access
    Replies: 0
    Last Post: 07-06-2011, 09:17 AM
  4. launching migration script
    By MrGrinch12 in forum Programming
    Replies: 0
    Last Post: 06-23-2010, 07:28 PM
  5. Launching Access DB from the Web
    By jeffcia in forum Access
    Replies: 1
    Last Post: 12-03-2009, 07:29 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
Tech Forums: Microsoft Office Forums