pass variables from form into a query

[mcgraw]

Not sure if I am posting this in the right place...

I have a form frmDateRange that has 2 text boxes, startdate and enddate that have afterupdate events attached to them. The only thing that happens is startdate and enddate are put into variables.

What I would like to do is pass those variables into 2 queries that drive a report.

So, when I hit OK in the form, it would pass the variables to the queries and open the report.

1: is this possible in Access 2007, and:
2: does anyone have an example of how to accomplish it?

any help would be appreciated!

 

[boblarson]

1. You don't need the variables. Just refer to the form (as long as you keep it open it will work and you can even hide the form and it will work).

2. You don't need to modify the query. You can set the criteria to be

Between [Forms]![YourFormname]![StartDate] And [Forms]![YourFormname]![EndDate]

or you can pass the date parameters when opening the report

DoCmd.OpenReport "ReportName", acViewPreview, , "[DateFieldInReport] Between #" & Forms!YourFormname.StartDate & "# And #" Forms!YourFormName.EndDate & "#"

 

[mcgraw]

The report I have created has a record source as a query, and then there is a sub report based on another query.

Should I remove the query as the record source, and just put the DoCmd.OpenReport command on the button? If so, how do I still pass that to the sub report as well?

Thanks for the help!

 

[boblarson]

The report I have created has a record source as a query, and then there is a sub report based on another query.

Should I remove the query as the record source, and just put the DoCmd.OpenReport command on the button? If so, how do I still pass that to the sub report as well?

Thanks for the help!

No, you don't need to replace anything. However if the sub report is based on a date range then I would change the criteria on the sub query to look at the form and the main query of the report as well.

When opening using the criteria in the code, you do not have to change anything about your query UNLESS it directly conflicts with the criteria you are passing in using the code. So, for example, I have this sample on my website where I have a generic report using a generic query and I open it in different ways on my form using different criteria in the code.

 

[mcgraw]

ok, I apologize if I am being a bit dense...

I'm looking at your sample DB, and the query you have running the form does not have any parameters set in it, all your parameters are set by the docmd.openreport command, correct?

The report I'm working with has a query with the parameters set as WHERE ((([commenthistory]![Comment_Date]) Between [Start Date] And [End Date])). (the sub query is also set to pull the same thing).

SO, I should remove the where clause from the main query, and have the parameters passed to it from the form, and then set the sub query to pull it's information from forms![frmDateRange]![startdate] And [forms]![frmDateRange]![enddate]

Or do BOTH queries need to pull forms![frmDateRange]![startdate] And [forms]![frmDateRange]![enddate]?

Again, sorry if I am being dense.

 

[boblarson]

ok, I apologize if I am being a bit dense...

I'm looking at your sample DB, and the query you have running the form does not have any parameters set in it, all your parameters are set by the docmd.openreport command, correct?

This is correct.

SO, I should remove the where clause from the main query, and have the parameters passed to it from the form, and then set the sub query to pull it's information from forms![frmDateRange]![startdate] And [forms]![frmDateRange]![enddate]

Or do BOTH queries need to pull forms![frmDateRange]![startdate] And [forms]![frmDateRange]![enddate]?

BOTH queries should reference the form (and I would do it that way since the sub report has need of limiting by date as well, but if it didn't need to be then you could do it either by modifying the main report's query or by just passing the criteria in the open code).

 

[mcgraw]

Ok, I set both queries to pull their param from [Forms]![frmDateRange]![startdate] And [enddate], and it is working...except for one tiny problem.

The main query used to group so each instance of ID was returned once...and now it is returning for each time a comment was made...

SELECT Issues.ID, commenthistory.Issue_ID, Issues.Title, Issues.[Assigned To], Issues.[Opened By], Issues.[Opened Date], Issues.Status, Issues.Priority, Issues.Description, Issues.Due_Date, Issues.Comments, Issues.CMEMO, Issues.Model, Issues.Init_STE, Issues.Final_STE, Issues.TCON, Issues.SGRK_Tech, Issues.Vendor, Issues.Location, Issues.Category

FROM Issues INNER JOIN commenthistory ON Issues.ID = commenthistory.Issue_ID

GROUP BY Issues.ID, commenthistory.Issue_ID, Issues.Title, Issues.[Assigned To], Issues.[Opened By], Issues.[Opened Date], Issues.Status, Issues.Priority, Issues.Description, Issues.Due_Date, Issues.Comments, Issues.CMEMO, Issues.Model, Issues.Init_STE, Issues.Final_STE, Issues.TCON, Issues.SGRK_Tech, Issues.Vendor, Issues.Location, Issues.Category, commenthistory.Comment_Date

HAVING (((commenthistory.Comment_Date) Between [forms]![frmDateRange]![startdate] And [forms]![frmDateRange]![enddate]));

I'm pretty sure it has something to do with the Group By clause, but I'm not seeing what broke. How do i get it to only return 1 record based on ID?

 

[boblarson]

It is because you are not getting the max date of comment history but instead are now looking for commenthistory between those dates.

You should probably use a sub query here to pull the MAX date for comment history, limited by the date range.

 

[mcgraw]

It is because you are not getting the max date of comment history but instead are now looking for commenthistory between those dates.

You should probably use a sub query here to pull the MAX date for comment history, limited by the date range.

The way I was previously pulling was using datediff to get anything between today and 6 days ago, so what is the difference between running it as a datediff and from the form?

 

[boblarson]

Shouldn't be any difference, but in reality you STILL should have used the Max and limited the date range to that to avoid any multiple returns. Not sure why it didn't in the other case but it really is the right way to go if you always want just one record returned for any particular Issue.

 

[mcgraw]

Thanks so much for the help! I REALLY appreciate it!