I have maximize and minimize buttons on the left side of the excel spreadsheet that I don't want. I want separate tabs on the bottom of the spreadsheet that show the two queries separately from one another. But I don't know how to do all of this?
Here's my code...what can I do?
Expand|Select|Wrap|Line Numbers
- Option Compare Database
- Public Function ExportRequest()
- Dim whereCond As String
- Dim LCS As String
- Dim Contractor As String
- Dim PDDivision As String
- Dim OpArea As String
- Dim WorkType As String
- Dim appExcel As Object
- Dim workBook As Object
- Dim workSheet As Object
- Forms!frmPrintPlan!cmbLCS.SetFocus
- LCS = Forms!frmPrintPlan!cmbLCS.Text
- Forms!frmPrintPlan!cmbContractor.SetFocus
- Contractor = Forms!frmPrintPlan!cmbContractor.Text
- Forms!frmPrintPlan!cmbPDDivision.SetFocus
- PDDivision = Forms!frmPrintPlan!cmbPDDivision.Text
- Forms!frmPrintPlan!cmbOpArea.SetFocus
- OpArea = Forms!frmPrintPlan!cmbOpArea.Text
- Forms!frmPrintPlan!cmbWorkType.SetFocus
- WorkType = Forms!frmPrintPlan!cmbWorkType.Text
- SetPlanRecordSource
- DoCmd.OutputTo acOutputReport, "rptPlan", acFormatXLS, "S:\Workgroups\APC Power Delivery-Contract Services\Herbicide and Tree Trimming Database\Reports Export\Budget Plan.xls", True
- ' Open an existing spreadsheet
- Set appExcel = GetObject("S:\Workgroups\APC Power Delivery-Contract Services\Herbicide and Tree Trimming Database\Reports Export\Budget Plan.xls")
- ' Show spreadsheet on screen
- appExcel.Application.Visible = True
- appExcel.Parent.Windows(1).Visible = True
- ' Autofit all columns on a worksheet
- Set workSheet = appExcel.Worksheets(1)
- workSheet.Cells.EntireColumn.AutoFit
- workSheet.Cells.Columns("N").ColumnWidth = 55
- workSheet.Range("N:N").WrapText = True
- workSheet.Cells.EntireRow.AutoFit
- ' Turn prompting OFF and save the sheet with original name
- appExcel.Application.DisplayAlerts = False
- appExcel.Application.Save
- appExcel.Application.DisplayAlerts = True
- ' Release objects
- Set workSheet = Nothing
- Set workBook = Nothing
- Set appExcel = Nothing
- End Function
- '*************************************************************'
- Public Function SetPlanRecordSource()
- Dim rptPlanRoutineMainSQL As String
- Dim rptPlanSplitYearMainSQL As String
- 'Set the record source for rptPlanRoutine
- rptPlanRoutineMainSQL = "SELECT tblFeeder.*,tblTypeofPlan.TypeofPlan AS WorkType, tblDivisions.DivisionName, DatePart('q',tblFeeder.PlanDate) AS Qtr, DatePart('yyyy',tblFeeder.PlanDate) AS [Year], Format(DateAdd('m',(tblFeeder.DefaultCycle),tblFeeder.PlanDate),'yyyy') AS NextYear, Format(DateAdd('m',(tblFeeder.DefaultCycle),tblFeeder.PlanDate),'m/d/yyyy') AS NextPlan, (tblFeeder.UTMiles*tblFeeder.UTCost) AS UTTotal, (tblFeeder.MTMiles*tblFeeder.MTCost) AS MTTotal, (tblFeeder.RLMiles*tblFeeder.RLCost) AS RLTotal, (tblFeeder.RTMiles*tblFeeder.RTCost) AS RTTotal, qryCombineTreeTrimming.StartDate, Left([tblTypeofPlan].[TypeOfPlan],1) AS type, tblOpArea.PDDivision, tblCrews.Contractor, tblCrews.CrewCode " & _
- "FROM (tblOpArea INNER JOIN (((tblDivisions INNER JOIN tblFeeder ON tblDivisions.Division = tblFeeder.Division) LEFT JOIN qryCombineTreeTrimming ON tblFeeder.FdrID = qryCombineTreeTrimming.FdrID) LEFT JOIN tblTypeofPlan ON tblFeeder.TypeOfPlan = tblTypeofPlan.TypeID) ON tblOpArea.OpAreaID = tblFeeder.OpArea) LEFT JOIN tblCrews ON tblFeeder.Crew = tblCrews.CrewID"
- SetSQL "qryRptPlanRoutine", rptPlanRoutineMainSQL & GetPlanRoutineWhereCond
- 'Set the record source for rptPlanSplitYear
- rptPlanSplitYearMainSQL = "SELECT tblPlanSplitYearFeeders.*, tblFeeder.FeederName, tblFeeder.FeederNumber, [SYUTCost]*[SYUTMiles] AS SYUTTotal, [SYMTCost]*[SYMTMiles] AS SYMTTotal, [SYRTCost]*[SYRTMiles] AS SYRTTotal, [SYRLCost]*[SYRLMiles] AS SYRLTotal, [SYUTMiles]+[SYRTMiles]+[SYRLMiles]+[SYMTMiles] AS SYTotalMiles, tblFeeder.DefaultCycle, tblFeeder.LCS, tblFeeder.OpArea, tblCrews.CrewCode, tblCrews.Contractor, tblOpArea.PDDivision FROM ((tblFeeder INNER JOIN tblPlanSplitYearFeeders ON tblFeeder.FdrID = tblPlanSplitYearFeeders.FdrID) LEFT JOIN tblCrews ON tblPlanSplitYearFeeders.Crew = tblCrews.CrewID) INNER JOIN tblOpArea ON tblFeeder.OpArea = tblOpArea.OpAreaID"
- SetSQL "qryRptPlanSplitYear", rptPlanSplitYearMainSQL & GetPlanSplitYearWhereCond
- End Function
Thanks in advance!!!