Results 1 to 4 of 4
  1. #1
    afa is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2016
    Posts
    22

    How do I repeat a macro x (a specific table value) number of times

    I would like my macro to run x number of times.


    This x value is stored in a table value. [Tables]![Panels_Total]![Total Panels]So if the value in the column Total Panels is 26, the macro would run 26 times.

    Afa

  2. #2
    afa is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2016
    Posts
    22
    This may help retrieve the value from my table..

    Code:
    Function GetGST() As String
    
       Dim db As Database
       Dim Lrs As DAO.Recordset
       Dim LSQL As String
       Dim LGST As String
    
    
       'Open connection to current Access database
       Set db = CurrentDb()
    
    
       'Create SQL statement to retrieve value from GST table
       LSQL = "select GST from GST"
    
    
       Set Lrs = db.OpenRecordset(LSQL)
    
    
       'Retrieve value if data is found
       If Lrs.EOF = False Then
          LGST = Lrs("GST")
       Else
          LGST = "Not found"
       End If
    
    
       Lrs.Close
       Set Lrs = Nothing
    
    
       GetGST = LGST
    
    
    End Function

  3. #3
    afa is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2016
    Posts
    22
    And I would like to reference this value like this

    Code:
    vNum = LGST
    DoCmd.RunMacro "Print Sales", vNum


    something like this maybe?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    To get the GST, try:
    Code:
    Function GetGST() As Integer
    
        Dim db As DAO.Database
        Dim Lrs As DAO.Recordset
        Dim LSQL As String
        Dim LGST As String
    
    
        'Open connection to current Access database
        Set db = CurrentDb()
    
        'default return value
        GetGST = 0
    
    
        'Create SQL statement to retrieve value from GST table
        LSQL = "SELECT GST FROM GST"   '<-- IMHO, it is poor programming practice to use the same name for two or more objects.
        'I would use tblGST for the table name
    
        Set Lrs = db.OpenRecordset(LSQL)
    
    
        'Retrieve value if data is found
        If Lrs.EOF = False Then
            LGST = Lrs("GST")
            GetGST = LGST
        End If
    
    
        Lrs.Close
        Set Lrs = Nothing
        Set db = Nothing
    
    End Function
    If you are using a MACRO, then this would probably work, but there might be issues about timing.
    Code:
    vNum = GetGST
    DoCmd.RunMacro "Print Sales", vNum


    If you are using VBA code, then look at this: (issues about timing)
    Code:
        Dim vNum As Integer
        Dim x As Integer
    
        vNum = GetGST
        
        If vNum > 0 Then
            For x = 1 To vNum
                'do things
                'might need a delay to allow process to finish before the loop starts over
            Next
        End If

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

Similar Threads

  1. Replies: 2
    Last Post: 03-11-2015, 10:08 PM
  2. Macro to open table to a specific record
    By Stretholox in forum Access
    Replies: 5
    Last Post: 12-08-2014, 03:09 PM
  3. How to Run Access Macro in certain times
    By OnatGG in forum Access
    Replies: 6
    Last Post: 03-25-2013, 03:51 AM
  4. Table/Linked Table with Specific number of rows
    By vik808 in forum Database Design
    Replies: 3
    Last Post: 01-03-2013, 02:02 PM
  5. Replies: 30
    Last Post: 08-15-2012, 02:25 PM

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