Creating a report from a form based on a query....

[BakerUSMC]

Hello,

I have a strange question... I have a form (frmhoursworked) whose recordsouce is a query (qryhoursworked). When I open the form, the query will ask you for beginning dates and ending dates. I have a command button on this form to preview a report (rpthoursworked). As of right now, the report is also based on the same query as the above form.
How do I preview a report based on the results returned on the active form(frmhoursworked)?

Thanks for all your help!!!

 

[dawnd3]

Are you asking how to show a report for just the current record on the form? Please explain a little more.

Thanks,

Dawn

http://www.calliopeconsulting.net

 

[BakerUSMC]

Dawn,

Yes... The query outputs a users hours worked for a specific date period. After they view it on the form, I want them to be able to print it out in a report format. So what ever data shows needs to be put on the report.

Hope I explained it enough...

Thanks

 

[dpye]

BakerUSMC,

If I undertand correctly you have a form where the criteria is being entered and a button the will run preview the report based on the data entered.

I have a similar setup with my own database and the way I did it was through using ME statement in the criteria fields on the query and some extra code on the button the launches the report.
----------------------------------------------------------
For example, you include the date field in the query being used by the report and then put the following criteria on it.

Between [Forms]![yourformname].[BeginDate] And [Forms]![yourformname].[EndDate]

The names BeginDate and EndDate are the names of data input fields on the form.

The button that launches the report will have the the following code on the click event to make sure they fill in a date:

If IsNull(Me![BeginDate].Value) Or IsNull(Me![EndDate].Value) Then
MsgBox "You must enter a begin date and end date before continuing.", vbOKOnly
Exit Sub
End If

DoCmd.OpenReport "Nameofreport", acPreview

I hope this helps.

 

[BakerUSMC]

DPYE,

Thanks for your response and in a way it does help. But to try to clarify a little further my situation and to make sure your method does help...I have outlined the sequense of events that lead up to this report in question:

1. frmHoursWorked - users enter the start/end times and then click an ADD/SAVE button to save to tblHoursWorked
2. Click on button on frmHoursWorked to show frmAllHoursWorked - this form is based on a query from tblHoursWorked and ask's you to enter beginning and ending dates
3. frmAllHoursWorked displays by date (date that was given by the user) all days worked with their start/end times (that was originally given in frmHoursWorked)
4. I want a command button on the frmAllHoursWorked that will pull that exact data (dates given by user to show frmAllHoursWorked) to a report

I really hope I explained this clear enough...

Thanks again!!!!

 

[dawnd3]

Basically, when you run the report you need to tell Access the criteria to base the report on. You can do this by putting parameters in the underlying query, or you can do it in VBA code in the docmd.openreport. One of the ways to tell it the criteria is to reference fields on the form. Here is an example from one of my databases. I am telling it to match the Job ID in the reports data source to the cmdJob field on the form which happens to be a combo box. And to also match the Plan ID to the plan that was chosen on the form.

DoCmd.OpenReport stDocName, acPreview, "", "[lngJobID]=[Forms]![frmPrintReports]![cboJob] And [lngPlanID]=[Forms]![frmPrintReports]![cboPlan]"

I hope in someway this leads you in the right direction.

Dawn

http://www.calliopeconsulting.net