Assign value to Query Parameter 1

[ShabanaHafiz]

I am using Microsoft Access 2003.

I have a query VHRunning2 (Vehicle Running). In this query, there are three tables; LogBook, Office and Vehicle. For a given DateOfTravel, query shows KM Traveled (Kilometers Traveled) for each OfficeID and Vehicle ID. SQL View of query is as follows:

SELECT Office.OfficeID, Vehicle.VehicleID, Sum([KMAfterTravel]-[KMBeforeTravel]) AS [KM Traveled]
FROM (LogBook INNER JOIN Office ON LogBook.OfficeID = Office.OfficeID) INNER JOIN Vehicle ON LogBook.VehicleID = Vehicle.VehicleID
WHERE (((LogBook.DateOfTravel) Between [Forms]![rpt1Select]![cboFromDate] And [Forms]![rpt1Select]![cboToDate]))
GROUP BY Office.OfficeID, Vehicle.VehicleID;

Then there is another query VHMaint2 (Vehicle Maintenance). In this query, there are two tables; VehicleMaint and Vehicle. For a given InvoiceDate, query shows total cost for each VehicleID. SQL view of query is as follows:

SELECT Vehicle.VehicleID, Sum([FuelRate]*[Quantity]+[RepairCost]+[OilChangeCost]+[MiscCost]) AS [Total Cost]
FROM VehicleMaint INNER JOIN Vehicle ON VehicleMaint.VehicleID = Vehicle.VehicleID
WHERE (((VehicleMaint.InvoiceDate) Between [Forms]![rpt1Select]![cboFromDate] And [Forms]![rpt1Select]![cboToDate]))
GROUP BY Vehicle.VehicleID;

Then there is a third query, VHCostPerKM, which is based on Query1 and Query2 and it calculates Unit Cost for each VehicleID. SQL view of query is as follows:

SELECT Q1.OfficeID, Q1.VehicleID, [Total Cost]/[KM Traveled] AS [Unit Cost]
FROM VHMaint2 AS Q2 INNER JOIN VHRunning2 AS Q1 ON Q2.VehicleID = Q1.VehicleID;

Now in the last query, VHRunningProject, For a given DateOfTravel and for the same InvoiceDate, I have to show KM Traveled and Cost Per Project for each Project. SQL View of this query is as follows:

SELECT Project.Description AS ProjectName, Office.Description AS OfficeName, Vehicle.Description AS VehicleName, Sum([KMAfterTravel]-[KMBeforeTravel]) AS [KM Traveled], Sum(([KMAfterTravel]-[KMBeforeTravel])*[Unit Cost]) AS [Cost per Project]
FROM (((LogBook INNER JOIN Project ON LogBook.ProjectID = Project.ProjectID) INNER JOIN Office ON LogBook.OfficeID = Office.OfficeID) INNER JOIN Vehicle ON LogBook.VehicleID = Vehicle.VehicleID) INNER JOIN VHCostPerKM ON (LogBook.OfficeID = VHCostPerKM.OfficeID) AND (LogBook.VehicleID = VHCostPerKM.VehicleID)
WHERE (((LogBook.DateOfTravel) Between [Forms]![rpt2Select]![cboFromDate] And [Forms]![rpt2Select]![cboToDate]))
GROUP BY Project.Description, Office.Description, Vehicle.Description;

When I execute this query, it asks me for FromDate and ToDate twice; one for DateOfTravel and second for DateOfTravel and InvoiceDate for the query VHRunning2 and VHMaint2

I have a form for report selection criteria; rpt2Select. In this form, there are two combo boxes; cboFromDate and cboToDate. cmdPreviewReport_Click has the following code:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

    Dim stDocName As String

    stDocName = "VHRunningProject"
    
    
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewReport_Click:
    Exit Sub

Err_cmdPreviewReport_Click:
    If Err = 2501 Then
        Resume Exit_cmdPreviewReport_Click
    Else
        MsgBox Err.Description
        Resume Exit_cmdPreviewReport_Click
    End If
    
End Sub

The problem that I am facing is cboFromDate and cboToDate values are passed to Forms!rpt2Select!cboFromDate and Forms!rpt2Select!cboToDate, which is present in VHRunningProject query for DateOfTravel Criteria:

WHERE (((LogBook.DateOfTravel) Between [Forms]![rpt2Select]![cboFromDate] And [Forms]![rpt2Select]![cboToDate]))

But these values also need to be passed to Forms!rpt1Select!cboFromDate and Forms!rpt1Select!cboToDate, which is present in VHRunning2 query for DateOfTravel Criteria:

WHERE (((LogBook.DateOfTravel) Between [Forms]![rpt1Select]![cboFromDate] And [Forms]![rpt1Select]![cboToDate]))

and which is also present in VHMaint2 query for InvoiceDate criteria:

WHERE (((VehicleMaint.InvoiceDate) Between [Forms]![rpt1Select]![cboFromDate] And [Forms]![rpt1Select]![cboToDate]))

I cannot change rpt1Select to rpt2Select in VHRunning2 and VHMaint2 because I have other report selection criteria forms too like rpt3Select, rpt4Select and so on and these forms also need to use VHRunning2 and VHMaint2 queries.

I need to assign values of cboFromDate and cboToDate to rpt1Select parameter. I tried this in cmdPreviewReport_Click for rpt2Select Form:

Forms!rpt1Select!cboFromDate = Forms!rpt2Select!cboFromDate
Forms!rpt1Select!cboToDate = Forms!rpt2Select!cboToDate

But got the following error:
Microsoft Office Access can’t find the form ‘rpt1Select’ referred to in a macro expression or Visual Basic code

 

[PHV]

The rpt1Select form must be open.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ShabanaHafiz]

Thank you.

It worked perfectly.

In cmdPreviewReport_Click of rpt2Select, I added the following lines:

DoCmd.OpenForm "rpt1Select"
Forms!rpt1Select!cboFromDate = Forms!rpt2Select!cboFromDate
Forms!rpt1Select!cboToDate = Forms!rpt2Select!cboToDate

In the report, VHRunningProject, I added the following lines:

Private Sub Report_Open(Cancel As Integer)
Forms![rpt1Select].Visible = False
End Sub

Private Sub Report_Close()
DoCmd.Close acForm, "rpt1Select"
End Sub