Form to Report query transfer

[Mike Magnant]

Form Demo. Allows the choice of Customer Name and Project Name to be printed.

There is a column in the query in the report that seems to relate to the Form Demo but I do not know how to change the query below to show only the project selected in the form?

SELECT Tasks.ID AS TaskNo, Customers.[Customer Number] AS CustomerNumber, Customers.Notes, Customers.Notes2, Customers.Company, Tasks.*, Projects.Origin AS Origin, Customers.CustomerName AS CustName, Projects.FSC, Projects.FSCPercent, Projects.Destination AS Destination, Projects.Category AS Category, Projects.EmployeeRate AS EmployeeRate, Projects.ProjectName AS ProjectName, Projects.CustomerRate, Users.FullName AS UserFullName, Customers.Terms, Projects.PO_Number, IIf([Tons]>0,([Tons]*[CustomerRate]),IIf([Yards]>0,([Yards]*[CustomerRate]),IIf([Hours]>0,([Hours]*[CustomerRate]),IIf([Gross]>0,(([Gross]-[Tare])/2000)*[CustomerRate],[CustomerRate])))) AS Result
FROM Customers, Projects, Tasks, Users
WHERE (((Projects!CustomerID)=Forms!Demo!cmbCustomerName And (Projects!CustomerID)=Customers!ID) And ((Projects!ID)=Tasks!ProjectID) And ((Tasks!AssignedToUserID)=Users!ID) And ((Tasks!Status)='10'))
ORDER BY ProjectName;

THANKS

 

[gbnz]

Hi Mike,

You would change it in the WHERE Clause to reflect the Primary Key that the Form (Demo) is using to display the current record. But I wouldn't change this Query, I would make a copy of this Query and change the copy until you get the results that you want. That way you always have your original to fall back on. Hope this helps

 

[Mihail]

Design a report that show all the customers and related projects.
Open the report (from the form) by using the WHERE clause:

Dim strWhere As String
strWhere = "ID_Project = " & Me.ID_Project & " And ID_Customer = " & Me.ID_Customer
DoCmd.OpenReport "ReportName", acViewPreview,,strWhere