Report based on Query?

[Michelle]

I have a query that has a from so the user can us the Date Picker to pick the
Start and End date for the query.
It works, however I also have a report that is based of the query.
On my Switchboard I have to to open the form they enter the days and then it
runs the query however if I have it open the report it just gives me two
parmater boxes for the start and end date.
Here is my code for the OK box on the form (the code that runs the query)
Private Sub cmdOK_Click()
If IsNull(sDate) Or IsNull(eDate) Then
MsgBox "You must enter a Sart and End Date." _
& vbCrLf & "Please try again.", vbExclamation, _
"More information required."
Exit Sub
End If
DoCmd.OpenQuery "qurTimeCardDates", acViewNormal, acEdit
DoCmd.close acForm, "frmDates"
End Sub

How would I get this to open the from then produce the report...can it even
be done.

 

[John W. Vinson]

I have a query that has a from so the user can us the Date Picker to pick the
Start and End date for the query.
It works, however I also have a report that is based of the query.
On my Switchboard I have to to open the form they enter the days and then it
runs the query however if I have it open the report it just gives me two
parmater boxes for the start and end date.
Here is my code for the OK box on the form (the code that runs the query)
Private Sub cmdOK_Click()
If IsNull(sDate) Or IsNull(eDate) Then
MsgBox "You must enter a Sart and End Date." _
& vbCrLf & "Please try again.", vbExclamation, _
"More information required."
Exit Sub
End If
DoCmd.OpenQuery "qurTimeCardDates", acViewNormal, acEdit
DoCmd.close acForm, "frmDates"
End Sub

How would I get this to open the from then produce the report...can it even
be done.

You don't need to open the Query to run the report; in fact it's very rare
that you would want to display a query datasheet to users. Base either a Form
or a Report (or both) on the query, and open that form or report directly.

You can open frmDates in the Report's Open event, with

DoCmd.OpenForm "frmDates", WindowMode:=acDialog

Reference the form controls directly in the query's criteria - if you want to
make either or both date optional you can use

= NZ([Forms]![frmDates]![sDate], #1/1/100#) AND < DateAdd("d", 1, NZ(Forms!frmDates!eDate, #12/30/9999#)))

Put a Run button on frmDates that sets its Visible property to False:

Private Sub cmdRun_Click()
Me.Visible = False
End Sub

to resume the code and print the report.

Put a command to close frmDates in the Report's Close event.

 

[Michelle]

That worked perfectly...thanks

I have a query that has a from so the user can us the Date Picker to pick the
Start and End date for the query.
It works, however I also have a report that is based of the query.
On my Switchboard I have to to open the form they enter the days and then it
runs the query however if I have it open the report it just gives me two
parmater boxes for the start and end date.
Here is my code for the OK box on the form (the code that runs the query)
Private Sub cmdOK_Click()
If IsNull(sDate) Or IsNull(eDate) Then
MsgBox "You must enter a Sart and End Date." _
& vbCrLf & "Please try again.", vbExclamation, _
"More information required."
Exit Sub
End If
DoCmd.OpenQuery "qurTimeCardDates", acViewNormal, acEdit
DoCmd.close acForm, "frmDates"
End Sub

How would I get this to open the from then produce the report...can it even
be done.

You don't need to open the Query to run the report; in fact it's very rare
that you would want to display a query datasheet to users. Base either a Form
or a Report (or both) on the query, and open that form or report directly.

You can open frmDates in the Report's Open event, with

DoCmd.OpenForm "frmDates", WindowMode:=acDialog

Reference the form controls directly in the query's criteria - if you want to
make either or both date optional you can use

= NZ([Forms]![frmDates]![sDate], #1/1/100#) AND < DateAdd("d", 1, NZ(Forms!frmDates!eDate, #12/30/9999#)))

Put a Run button on frmDates that sets its Visible property to False:

Private Sub cmdRun_Click()
Me.Visible = False
End Sub

to resume the code and print the report.

Put a command to close frmDates in the Report's Close event.