Results 1 to 10 of 10
  1. #1
    HotelierDream is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    31

    How to shorten code

    Does anyone have an idea how I could shorten this code down?


    I also have a bunch of queries that do the same thing with different criteria, would there be any way to use one code line to do it?

    First code to shorten:
    Code:
    Private Sub Command0_Click()
    Dim dbs As DAO.Database
    Dim qd2 As DAO.QueryDef
    Dim qd3 As DAO.QueryDef
    Dim qd4 As DAO.QueryDef
    Dim qd5 As DAO.QueryDef
    Dim qd6 As DAO.QueryDef
    Dim qd7 As DAO.QueryDef
    Dim qd8 As DAO.QueryDef
    Dim qd9 As DAO.QueryDef
    Dim qd10 As DAO.QueryDef
    Dim qd11 As DAO.QueryDef
    Dim qd12 As DAO.QueryDef
    Dim qd13 As DAO.QueryDef
    Dim qd14 As DAO.QueryDef
    Dim qd15 As DAO.QueryDef
    Dim qd16 As DAO.QueryDef
    Dim qd17 As DAO.QueryDef
    Dim qd18 As DAO.QueryDef
    Dim qd19 As DAO.QueryDef
    Dim qd20 As DAO.QueryDef
    Dim qd21 As DAO.QueryDef
    Dim qd22 As DAO.QueryDef
    Dim var As Variant
    DoCmd.RunCommand acCmdSaveRecord
    'Below Deletes Prev Day Values
    For Each var In Array("G_GRP1", "G_GRP2", "STAT_DMY_SEG", "G_CHK_BALANCE", "G_TRX_CODE", "G_TRX_TYPE", "TRIAL_BALANCE")
       CurrentDb.Execute "DELETE FROM " & var, dbFailOnError
    Next var
    'above deletes prev day values
    
    DoCmd.RunSavedImportExport ("market")
    DoCmd.RunSavedImportExport ("finrpt")
    CurrentDb.Execute "TransQ"
    CurrentDb.Execute "LedgersQ"
    
    Set dbs = CurrentDb
    Set qd2 = dbs.QueryDefs("AAATOTALS")
    Set qd3 = dbs.QueryDefs("ADVPURTOTALS")
    Set qd4 = dbs.QueryDefs("COMPTOTALS")
    Set qd5 = dbs.QueryDefs("CORPTOTALS")
    Set qd6 = dbs.QueryDefs("EMPTOTALS")
    Set qd7 = dbs.QueryDefs("EXTSTAYTOTALS")
    Set qd8 = dbs.QueryDefs("FITTOALS")
    Set qd9 = dbs.QueryDefs("GOVTOTALS")
    Set qd10 = dbs.QueryDefs("GRPCORP")
    Set qd11 = dbs.QueryDefs("GRPGOV")
    Set qd12 = dbs.QueryDefs("GRPLEI")
    Set qd13 = dbs.QueryDefs("GRPOTH")
    Set qd14 = dbs.QueryDefs("INTERNETTOTALS")
    Set qd15 = dbs.QueryDefs("LEIPKGTOTALS")
    Set qd16 = dbs.QueryDefs("LEISURETRANSTOTALS")
    Set qd17 = dbs.QueryDefs("LNRTOTALS")
    Set qd18 = dbs.QueryDefs("MEMRWDSTOTALS")
    Set qd19 = dbs.QueryDefs("OTHERTRANSTOTALS")
    Set qd20 = dbs.QueryDefs("RACKTOTALS")
    Set qd21 = dbs.QueryDefs("TRANSTOTALS")
    Set qd22 = dbs.QueryDefs("MKTRPTTOTALS")
    
    qd2.Parameters("AuditDate").Value = Me.Text9
    qd3.Parameters("AuditDate").Value = Me.Text9
    qd4.Parameters("AuditDate").Value = Me.Text9
    qd5.Parameters("AuditDate").Value = Me.Text9
    qd6.Parameters("AuditDate").Value = Me.Text9
    qd7.Parameters("AuditDate").Value = Me.Text9
    qd8.Parameters("AuditDate").Value = Me.Text9
    qd9.Parameters("AuditDate").Value = Me.Text9
    qd10.Parameters("AuditDate").Value = Me.Text9
    qd11.Parameters("AuditDate").Value = Me.Text9
    qd12.Parameters("AuditDate").Value = Me.Text9
    qd13.Parameters("AuditDate").Value = Me.Text9
    qd14.Parameters("AuditDate").Value = Me.Text9
    qd15.Parameters("AuditDate").Value = Me.Text9
    qd16.Parameters("AuditDate").Value = Me.Text9
    qd17.Parameters("AuditDate").Value = Me.Text9
    qd18.Parameters("AuditDate").Value = Me.Text9
    qd19.Parameters("AuditDate").Value = Me.Text9
    qd20.Parameters("AuditDate").Value = Me.Text9
    qd21.Parameters("AuditDate").Value = Me.Text9
    qd22.Parameters("AuditDate").Value = Me.Text9
    
    
    
    qd2.Execute
    qd2.Close
    qd3.Execute
    qd3.Close
    qd4.Execute
    qd4.Close
    qd5.Execute
    qd5.Close
    qd6.Execute
    qd6.Close
    qd7.Execute
    qd7.Close
    qd8.Execute
    qd8.Close
    qd9.Execute
    qd9.Close
    qd10.Execute
    qd10.Close
    qd11.Execute
    qd11.Close
    qd12.Execute
    qd12.Close
    qd13.Execute
    qd13.Close
    qd14.Execute
    qd14.Close
    qd15.Execute
    qd15.Close
    qd16.Execute
    qd16.Close
    qd17.Execute
    qd17.Close
    qd18.Execute
    qd18.Close
    qd19.Execute
    qd19.Close
    qd20.Execute
    qd20.Close
    qd21.Execute
    qd21.Close
    qd22.Execute
    qd22.Close

    SQL of Query (1 of 21)
    Code:
    INSERT INTO DailyMktRpt ( TotalRms, TotalRev, TotalGrpRms, TotalGrpRev )
    SELECT [DailyMktRpt]!RackTransRms+[DailyMktRpt]!LNRRms+[DailyMktRpt]!InternetRms+[DailyMktRpt]!CorpRms+[DailyMktRpt]!AAARms+[DailyMktRpt]!LeiTRms+[DailyMktRpt]!OthRms+[DailyMktRpt]!LeiPkgTRms+[DailyMktRpt]!GovRms+[DailyMktRpt]!AdvRms+[DailyMktRpt]!FITRms+[DailyMktRpt]!EmpRms+[DailyMktRpt]!MemRwdsRms+[DailyMktRpt]!ExtRms AS TotalRms, [DailyMktRpt]!RackTransRev+[DailyMktRpt]!LNRRev+[DailyMktRpt]!InternetRev+[DailyMktRpt]!CorpRev+[DailyMktRpt]!AAARev+[DailyMktRpt]!LeiTRev+[DailyMktRpt]!OthRev+[DailyMktRpt]!LeiPkgTRev+[DailyMktRpt]!GovRev+[DailyMktRpt]!AdvRev+[DailyMktRpt]!FITRev+[DailyMktRpt]!EmpRev+[DailyMktRpt]!MemRwdsRev+[DailyMktRpt]!ExtRev AS TotalRev, [DailyMktRpt]![GovGrpRms]+[DailyMktRpt]![LeiGrpRms]+[DailyMktRpt]![OthGrpRms]+[DailyMktRpt]![CorpGrpRms] AS GrplRms, [DailyMktRpt]![OthGrpRev]+[DailyMktRpt]![LeiGrpRev]+[DailyMktRpt]![GovGrpRev]+[DailyMktRpt]![CorpGrpRev] AS GrpRev
    FROM DailyMktRpt;

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You could scrap all the query def code. If they are all saved queries, just run the queries in turn.
    Or just execute the SQL statements.
    If, as I suspect, each of your 21 statements are very similar, you may well be able to rationalise this still further.

    However, if you are emptying and repopulating tables each day, there will be a lot of database bloat.
    Why are you doing this? Are these used as 'temporary' tables?
    Consider moving all the tables to a separate linked database
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You could set a TempVar to Me.Text9 and then just refer to that in your queries?

    You might want to start giving some meaningful names to your controls as well.? 6 weeks/months down the line Text9 is not going to mean much to you or anyone else?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    As you do with the first loop, you can use a second loop for the querydefs using an array with their names:
    Code:
    For Each var In Array("AAATOTALS", "ADVPURTOTALS", "COMPTOTALS",...)
        With dbs.QueryDefs(var)
            .Parameters("AuditDate") = Me.Text9 'I don't see this parameter in SQL(?...)
            .Execute
        End With
    Next var
    BTW, I thing that you have to start to study for the GroupBy queries, totals, and calculated fields in Access.

    Good luck with your project!
    John

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The alternative would be to store the text9 value in a settings table and add it to the queries as a unjoined table value and simply use it as the criteria.
    That way it is still set next time the queries are run.

    As usual 6 different ways to skin a cat.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    HotelierDream is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    31
    Quote Originally Posted by isladogs View Post
    You could scrap all the query def code. If they are all saved queries, just run the queries in turn.
    Or just execute the SQL statements.
    If, as I suspect, each of your 21 statements are very similar, you may well be able to rationalise this still further.

    However, if you are emptying and repopulating tables each day, there will be a lot of database bloat.
    Why are you doing this? Are these used as 'temporary' tables?
    Consider moving all the tables to a separate linked database
    So the deletion of the tables is because the values change each day but the tables are required as they are part of a XML import that is done using a save import.

    Due to the entire DB breaking down when trying to change users I would like to move away from BAT files as I suspect they are the cause for error. One thing I am trying to figure out is how to dynamically update the file path of the path name of my import.

    So the data import location would need to be 1. a reformatted selection of the date inserted into the text9 field on my form (instead of mm/dd/yyyy I would need to make it mmddyy) then I would need it to find the xml file with 023_XXX where the XXX changes randomly each day. I currently use a bat file which pull the information from the server into a locally saved folder and renames it to a set name so that access uses the same file name and path each time.

    Is there any way to do this?

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are not enough comments in the code for my liking, but looking at the code, I have some suggestions.

    The close commands (qd2.Close, qd2.Close, etc) are not needed because the execute commands (qd2.Execute, qd3.Execute, etc) only work with ACTION queries.
    It looks like the queries are save queries - why isn't the criteria (Me.Text9) in the saved query? The form that has the control "Text9" must be open to get the values, so add the reference (Forms!TheFormName.Text9) to all of the saved queries.
    Now all of the query def code lines that deal with qd2 thru qd22 can be deleted.

    Without being able to test the code, what is left could be
    Code:
    Private Sub Command0_Click()
        Dim dbs As DAO.Database
        Dim var As Variant
        
        Set dbs = CurrentDb
    
        'DoCmd.RunCommand acCmdSaveRecord
        If Me.Dirty Then
            Me.Dirty = False
        End If
        
        'Below Deletes Prev Day Values
        For Each var In Array("G_GRP1", "G_GRP2", "STAT_DMY_SEG", "G_CHK_BALANCE", "G_TRX_CODE", "G_TRX_TYPE", "TRIAL_BALANCE")
            CurrentDb.Execute "DELETE FROM " & var, dbFailOnError
        Next var
        'above deletes prev day values
    
        DoCmd.RunSavedImportExport ("market")  'Import or Export spec?
        DoCmd.RunSavedImportExport ("finrpt")  'Import or Export spec?
    
        dbs.Execute "TransQ"
        dbs.Execute "LedgersQ"
    
        dbs.Execute "AAATOTALS"
        dbs.Execute "ADVPURTOTALS"
        dbs.Execute "COMPTOTALS"
        dbs.Execute "CORPTOTALS"
        dbs.Execute "EMPTOTALS"
        dbs.Execute "EXTSTAYTOTALS"
        dbs.Execute "FITTOALS"
        dbs.Execute "GOVTOTALS"
        dbs.Execute "GRPCORP"
        dbs.Execute "GRPGOV"
        dbs.Execute "GRPLEI"
        dbs.Execute "GRPOTH"
        dbs.Execute "INTERNETTOTALS"
        dbs.Execute "LEIPKGTOTALS"
        dbs.Execute "LEISURETRANSTOTALS"
        dbs.Execute "LNRTOTALS"
        dbs.Execute "MEMRWDSTOTALS"
        dbs.Execute "OTHERTRANSTOTALS"
        dbs.Execute "RACKTOTALS"
        dbs.Execute "TRANSTOTALS"
        dbs.Execute "MKTRPTTOTALS"
    
        ' clean up
        Set dbs = Nothing
    End Sub
    I would also suggest an error handler should be added.
    Maybe you would post the dB??

  8. #8
    HotelierDream is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    31
    Quote Originally Posted by Minty View Post
    The alternative would be to store the text9 value in a settings table and add it to the queries as a unjoined table value and simply use it as the criteria.
    That way it is still set next time the queries are run.

    As usual 6 different ways to skin a cat.
    Well let me tell you I somehow always seem to over complicate.

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    One thing I am trying to figure out is how to dynamically update the file path of the path name of my import.

    So the data import location would need to be 1. a reformatted selection of the date inserted into the text9 field on my form (instead of mm/dd/yyyy I would need to make it mmddyy) then I would need it to find the xml file with 023_XXX where the XXX changes randomly each day. I currently use a bat file which pull the information from the server into a locally saved folder and renames it to a set name so that access uses the same file name and path each time.

    Is there any way to do this?
    The import spec is saved in access as an xml. I'll have to check my other computer for the app I used this in, but this should give you a start.

    Code:
    '?CurrentProject.ImportExportSpecifications(0).XML
    
    
    
    
    Sub ChangeImportPath(StrNewPath As String, strSpecName As String)
        On Error GoTo Err_Handler
        
    ' MSXML2 requires reference to "Microsoft XML, v6.0"
    ' earlier versions are probably compatible, remember to use the appropriate
    ' DOMDocument object  version.
    ' code borrowed from Jason Hardman's contribution at this link:
    ' https://stackoverflow.com/questions/23350640/how-to-specify-a-different-file-path-for-a-saved-excel-import/38265498#38265498?newreg=649801888011413a8d9e56e187f953f7
        
        ' deleteImport
        
        
        Dim XMLData As MSXML2.DOMDocument60
        Dim ImportSpec As ImportExportSpecification
        Dim XMLNode As IXMLDOMNode
        'Dim StrNewPath As String
        
        ' Get XML object to manage the spec data
        Set XMLData = New MSXML2.DOMDocument60
    
    
        
        ' existing Import Specification (should be set up manually with relevant name)
        Set ImportSpec = CurrentProject.ImportExportSpecifications(strSpecName)
        
        XMLData.LoadXML ImportSpec.XML
        
        
        
        ' change it's path to the one specified
        With XMLData.DocumentElement
            .setAttribute "Path", StrNewPath
        End With
    
    
        ImportSpec.XML = XMLData.XML
        
        ' run the updated import
        'Debug.Print CurrentProject.ImportExportSpecifications(0).XML
        ImportSpec.Execute
    
    
    Exit_Handler:
        Set ImportSpec = Nothing
        Set XMLData = Nothing
        Exit Sub
    
    
    Err_Handler:
        Select Case err.Number
            Case 3011
                MsgBox "Replacement path is invalid", vbCritical 'Profile name is wrong or MS Exchange server is offline
            Case Else
                MsgBox err.Number & " - " & err.Description & vbCrLf _
                       & "Please take note of the error code and contact your System Administrator", vbCritical
        End Select
        Resume Exit_Handler
        
    End Sub
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I have an app that allows you to easily change the XML for any import/export specification. See http://www.mendipdatasystems.co.uk/v...sks/4595119101
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 3
    Last Post: 07-04-2017, 06:00 PM
  2. Replies: 20
    Last Post: 10-13-2015, 09:05 AM
  3. Replies: 3
    Last Post: 10-16-2014, 08:49 AM
  4. Replies: 7
    Last Post: 05-28-2013, 09:11 AM
  5. Replies: 1
    Last Post: 05-04-2013, 12:19 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