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;