Results 1 to 5 of 5
  1. #1
    veejay is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2018
    Location
    Montreal, Canada
    Posts
    46

    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.

    Code:
    Private Sub cmdTranspose_Click()
    Module1.Transpose
    End Sub
    And the modulecode:

    Code:
    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]"
            Next
            .SetWarnings True
        End With
     
        rs.Close
        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:

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



    EDIT:

    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 08: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
    Location
    Nevada, USA
    Posts
    22,518
    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:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

    Module1.Transpose

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

    Transpose

    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 foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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 08:08 PM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    veejay is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2018
    Location
    Montreal, Canada
    Posts
    46
    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, 03:26 PM
  2. Trouble with macro within a subform
    By MLangendorf in forum Forms
    Replies: 12
    Last Post: 12-13-2016, 01:22 PM
  3. UI Macro Trouble
    By beetgirl in forum Access
    Replies: 0
    Last Post: 07-06-2011, 10:17 AM
  4. launching migration script
    By MrGrinch12 in forum Programming
    Replies: 0
    Last Post: 06-23-2010, 08:28 PM
  5. Launching Access DB from the Web
    By jeffcia in forum Access
    Replies: 1
    Last Post: 12-03-2009, 08: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
  •  
Other Forums: Microsoft Office Forums