help!!! using a form to set report criteria

[MarkRCC]

Hi.

After reading FAQ # 703-2696, "Use a form and query to send criteria to a report.", I decided to try it.

I have the report and query working ok if I have the query ask for a parameter. For example, i set, [Please enter a last name] in the "LastName" field of the query and when I type in a valid last name, the report prints properly.

I created a report that asks for all the required info using combo boxes.

"Last Name" combo box: references my employeeInfo table and provides a list of all names.
"Office Location" combo box: references the employeeInfo table and provided a list of all office locations.
"End Date" combo box: asks the user to fill in an end date for the report.
"HowManymonths" list box: asks the user for the number of months prior to the end date that they want the report to run for.

Thus, if an end user selects the following:

Last Name: Smith
(First Name: John)
Office Location: Miami
End date: 10/31/2003
How many months: 6

Then, the result should be a report for John Smith from the Miami office showing data from May, 31 2003 to October,31 2003.

Right now, I'm just trying to pass the lastname function to the query to test it, but the query is returning a prompt asking for a last name.

I'm assuming that the problem lies in the fact that the form isn't bound to a record source. Am I correct? Would anyone know how to pass the values properly?

Many TIAs.

Mark

 

[dhookom]

Share:
1) your form and control names
2) your query's SQL view

Duane
MS Access MVP

 

[MarkRCC]

Hi Duane.

Thanks for the prompt reply.

Here's the info:

1)Form Name: CriteriaInput-TopProdMonthlyRpt
Record Source: tblBrokerInfo
Field1:
Name: BrokerName
ControlSource: None
RowSource: SELECT tblBrokerInfo.BrkrID,tblBrokerInfo.BrkrLN,
tblBrokerInfo.BrkrFN,tblBrokerInfo.BrkrCompanyName
FROM tblBrokerInfo;

Field2:
Name: OfficeLocation
ControlSource: None
RowSource: SELECT tblOfficeLocations.OffLoc
FROM tblOfficeLocations;

Field3: EndingDate
ControlSource: None
DefaultValue: Date()

Field4: HowManyMonthsBack
ControlSource: None

Field5: StartDate
ControlSource: =DateAdd("m",-[howmanymonthsback],[EndingDate])

Field6: CmdButtonRunRpt

2)Query's SQL View:

SELECT DISTINCTROW tblBrokerInfo.BrkrLN, tblBrokerInfo.BrkrCompanyName, tblBrokerInfo.BrkrFN, Sum(tblBrokerMonthlyData.BrokerFYC) AS SumOfBrokerFYC, Sum(tblBrokerMonthlyData.BrokerFYCMS) AS SumOfBrokerFYCMS, Sum(tblBrokerMonthlyData.BrokerORMS) AS SumOfBrokerORMS, Sum(tblBrokerMonthlyData.BrokerORLS) AS SumOfBrokerORLS, tblBrokerInfo.BrkrOffLoc, tblBrokerMonthlyData.date
FROM tblCompanyInfo INNER JOIN (tblBrokerInfo INNER JOIN tblBrokerMonthlyData ON tblBrokerInfo.BrkrID = tblBrokerMonthlyData.BrokerID) ON tblCompanyInfo.CmpnyID = tblBrokerMonthlyData.CompanyID
GROUP BY tblBrokerInfo.BrkrLN, tblBrokerInfo.BrkrCompanyName, tblBrokerInfo.BrkrFN, tblBrokerInfo.BrkrOffLoc, tblBrokerMonthlyData.date
HAVING (((tblBrokerInfo.BrkrLN)=[ Forms]![CriteriaInput-TopProdMonthlyRpt]![BrokerName]) AND ((tblBrokerInfo.BrkrOffLoc)=[Forms]![CriteriaInput-TopProdMonthlyRpt]![OfficeLocation]) AND ((tblBrokerMonthlyData.date) Between [Forms]![CriteriaInput-TopProdMonthlyRpt]![EndingDate] And [Forms]![CriteriaInput-TopProdMonthlyRpt]![StartDate]));


The start date calculates ok on the form, but when I click on the command button to create the report, I get prompted for the parameters again.

Thanks again for having a look.

Mark