Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

problem passing dates

Status
Not open for further replies.

Lhuffst

Programmer
Jun 23, 2003
503
US
I need help on how to correctly identify dates from a form in queries.

I have

MainForm: ShopOpNavigation (used the wizard)
Subform: frmReports where the user enters txtFromDate and txtToDate

That is fine.

Reports:
Main Report: Report1 which has only titles for the report, no recordsource
Sub Report: Rpt1 which has it's own recordsource
Sub Report: Rpt2 which has a different recordsource

On the FrmReports, I have a button that opens the selected report with
Code:
 StrWhere = "[dateadded] between #" & Me.txtfromdate & "# and #" & Me.txtToDate & "#"
 DoCmd.OpenReport "Report1", acViewPreview, , StrWhere

The filter StrWhere passes to the mainreport fine but not the subreports.
I have read many different ways suggested to pass to the subreport but can't seem to get it to work.
the last one I was going to try was Duane's suggestion on a post

Between Forms!frmX!txtFromDate and Forms!frmX!txtToDate But do not know how to reference it properly.

I Tried:
Between Me!ShopOpNavigation.Form!frmReports.Form!txtFromDate and Me!ShopOpNavigation.Form!frmReports.Form!txtFromDate
I don't get any errors but I also don't get any records

If I run the report by itself (leaving frmreports open), I get an error

The expression On Open you entered as the event property setting produced the following error: Object or class does not support the set of events
Thanks
Lhuffst
 
redid the reports for the umpteenth time :)

Form
frmReports, you fill in txtfromdate and txttodate.

Report1
Added 2 text boxes
TxtStart control source =[Forms]![frmReports]![txtfromdate]
txtEnd control source =[Forms]![frmReports]![txtToDate]

Recordsource is now: SELECT TblData.DateAdded, TblData.EnteredBy, TblData.SubmittedBy
FROM TblData;
Group is DateAdded

Rpt1 (first subreport)
uses a query that has
Between [Reports]![Report1]![TxtStart] And [Reports]![Report1]![TxtEnd] as criteria for the dateadded field

If I run this from frmreports, it runs fine.
When I run it from the navigation page (ShopOpNavigation), the subreport (rpt1) is blank.
What should I check?
 
Finally (after days) of trying different things, I got it to work.

changed the following.

On Report 1
txtStart Control Source is now =[Forms]![ShopOpNavigation]![NavigationSubform].[Form]![txtfromdate]
txtEnd control source is now =[Forms]![ShopOpNavigation]![NavigationSubform].[Form]![txttodate]

had to change subform to navigationsubform.

Whew
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top