Prompt form launching report with subreport

[PeterK]

I have rpt1 based on qry1.
rpt1 has sub-rpt1. sub-rpt1 is based on qry2.
qry1 and qry2 both have a filter. The two filters are different.

My question - how do I set up a prompt form to launch the report with the
filters applied? I understand the basics of coding a WHERE clause and
passing it to the query via OpenArgs for a single report. But with a
subreport added, it’s too complex for me.

I have Access 2003 and Windows xp.

 

[Duane Hookom]

I would start by opening a form with the controls for setting the criteria.
Your subreport Record Source would need something in it SQL like:
WHERE [SomeField] = Forms!frmCriteria!cboMyCombo
You can set the Where Condition of the DoCmd.OpenReport method to set the
filter for your main report.

 

[PeterK]

Thanks for your quick reply. I’m taking from your comments to set it up in
stages. So…

Step 1 is complete - I have a prompt form that opens the main report,
unfiltered.

Step 2 - the report is a history of student lessons, and I want to filter
for a selected student. These are the details:
frmPrompt_Lessons has a control cboPromptName. The bound field is
tblStudents.intStudentID.
The report is based on qryLessons, which has intStudentID as a field. When
I type a value in the criteria line I get an extra SQL clause that says:
HAVING (((tblStudents.intStudentID)=100))
(rather than a WHERE clause)

How do I translate all that into VB code for the prompt form?


--
PeterK

I would start by opening a form with the controls for setting the criteria.
Your subreport Record Source would need something in it SQL like:
WHERE [SomeField] = Forms!frmCriteria!cboMyCombo
You can set the Where Condition of the DoCmd.OpenReport method to set the
filter for your main report.

--
Duane Hookom
Microsoft Access MVP

I have rpt1 based on qry1.
rpt1 has sub-rpt1. sub-rpt1 is based on qry2.
qry1 and qry2 both have a filter. The two filters are different.

My question - how do I set up a prompt form to launch the report with the
filters applied? I understand the basics of coding a WHERE clause and
passing it to the query via OpenArgs for a single report. But with a
subreport added, it’s too complex for me.

I have Access 2003 and Windows xp.

 

[Duane Hookom]

There isn't a lot of difference between using "HAVING" vs "WHERE" in this
instance. If you want to filter the main report by intStudentID consider
using code like:

Dim strWhere As String
Dim stDocName as String
stDocName ="rptStudentLessons"
strWhere = "1=1 "
If Not IsNull(Me.cboPromptName) Then
strWhere = strWhere & " AND intStudentID = " & _
Me.cboPromptName
End If
DoCmd.OpenReport stDocName, acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP

Thanks for your quick reply. I’m taking from your comments to set it up in
stages. So…

Step 1 is complete - I have a prompt form that opens the main report,
unfiltered.

Step 2 - the report is a history of student lessons, and I want to filter
for a selected student. These are the details:
frmPrompt_Lessons has a control cboPromptName. The bound field is
tblStudents.intStudentID.
The report is based on qryLessons, which has intStudentID as a field. When
I type a value in the criteria line I get an extra SQL clause that says:
HAVING (((tblStudents.intStudentID)=100))
(rather than a WHERE clause)

How do I translate all that into VB code for the prompt form?


--
PeterK

I would start by opening a form with the controls for setting the criteria.
Your subreport Record Source would need something in it SQL like:
WHERE [SomeField] = Forms!frmCriteria!cboMyCombo
You can set the Where Condition of the DoCmd.OpenReport method to set the
filter for your main report.

--
Duane Hookom
Microsoft Access MVP

I have rpt1 based on qry1.
rpt1 has sub-rpt1. sub-rpt1 is based on qry2.
qry1 and qry2 both have a filter. The two filters are different.

My question - how do I set up a prompt form to launch the report with the
filters applied? I understand the basics of coding a WHERE clause and
passing it to the query via OpenArgs for a single report. But with a
subreport added, it’s too complex for me.

I have Access 2003 and Windows xp.

 

[PeterK]

Your coding suggestion worked fine - thanks. But I have a second (and last!)
filter, by date. The main report shows a lesson summary up to the selected
date, then the subreport shows individual lesson records from that point on.
When I tried to add the date filter it didn’t work. This is the coding I
tried:

Private Sub cmdOK_Enter()

'Opens the named report with selected filters.
'Closes the prompt form.

Dim strReportName As String 'The report to be opened
Dim strPromptForm As String 'The name of the prompt form.
Dim strWhere As String 'The filter string

strReportName = "rptLessons_CurrentMonth2"
strPromptForm = "frmPrompt_Lessons"

strWhere = "1=1 "
If Not IsNull(Me.cboPromptName) Then
strWhere = strWhere & " AND intStudentID = " & Me.cboPromptName
End If

If Not IsNull(Me.txtPromptDate) Then
strWhere = strWhere & " AND dtTransactionDate < #" & Me.txtPromptDate &
"#"
End If

DoCmd.OpenReport strReportName, acViewPreview, , strWhere
DoCmd.Close acForm, strPromptForm

End Sub

So my questions are:
1. How do I correct this code?
2. What do I add to the record source of the subreport to select records
where dtTransactionDate is = or > txtPromptDate?
3. If it’s not too complex, what is the significance of the line of code
**strWhere = "1=1 "**?

Thanks.
--
PeterK

There isn't a lot of difference between using "HAVING" vs "WHERE" in this
instance. If you want to filter the main report by intStudentID consider
using code like:

Dim strWhere As String
Dim stDocName as String
stDocName ="rptStudentLessons"
strWhere = "1=1 "
If Not IsNull(Me.cboPromptName) Then
strWhere = strWhere & " AND intStudentID = " & _
Me.cboPromptName
End If
DoCmd.OpenReport stDocName, acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP

Thanks for your quick reply. I’m taking from your comments to set it up in
stages. So…

Step 1 is complete - I have a prompt form that opens the main report,
unfiltered.

Step 2 - the report is a history of student lessons, and I want to filter
for a selected student. These are the details:
frmPrompt_Lessons has a control cboPromptName. The bound field is
tblStudents.intStudentID.
The report is based on qryLessons, which has intStudentID as a field. When
I type a value in the criteria line I get an extra SQL clause that says:
HAVING (((tblStudents.intStudentID)=100))
(rather than a WHERE clause)

How do I translate all that into VB code for the prompt form?


--
PeterK

I would start by opening a form with the controls for setting the criteria.
Your subreport Record Source would need something in it SQL like:
WHERE [SomeField] = Forms!frmCriteria!cboMyCombo
You can set the Where Condition of the DoCmd.OpenReport method to set the
filter for your main report.

--
Duane Hookom
Microsoft Access MVP


:

I have rpt1 based on qry1.
rpt1 has sub-rpt1. sub-rpt1 is based on qry2.
qry1 and qry2 both have a filter. The two filters are different.

My question - how do I set up a prompt form to launch the report with the
filters applied? I understand the basics of coding a WHERE clause and
passing it to the query via OpenArgs for a single report. But with a
subreport added, it’s too complex for me.

I have Access 2003 and Windows xp.

 

[Lydia]

Duane,

I don't know how to post a new question specifically addressed to you so I
find a message by you and did a reply instead.

This is my question:

I have a text box with its control source set to =[Enter a MIS number] &"
MIS number" and this text box is on a chart report with several subreports on
it.

When the parent report has nothing in its record source(It is an unbound
report), when I open the report, I don't see prompt for [Enter a Mis
Number]. However if I put a table to the report's record source, the prompt
comes up all right. I want the report to stay as it is: an unbound report.

My current work around is to create a table with one record and set it to
the report's recordsource.

Do you have any suggestion why this happens and is there is a better way to
deal with it?

Thanks.


--
Access/VB Programmer

I would start by opening a form with the controls for setting the criteria.
Your subreport Record Source would need something in it SQL like:
WHERE [SomeField] = Forms!frmCriteria!cboMyCombo
You can set the Where Condition of the DoCmd.OpenReport method to set the
filter for your main report.

--
Duane Hookom
Microsoft Access MVP

I have rpt1 based on qry1.
rpt1 has sub-rpt1. sub-rpt1 is based on qry2.
qry1 and qry2 both have a filter. The two filters are different.

My question - how do I set up a prompt form to launch the report with the
filters applied? I understand the basics of coding a WHERE clause and
passing it to the query via OpenArgs for a single report. But with a
subreport added, it’s too complex for me.

I have Access 2003 and Windows xp.

 

[Duane Hookom]

You won't get prompted unless there are records (as you have found). You
should be using references to controls on forms rather than parameter prompts.
--
Duane Hookom
Microsoft Access MVP

Duane,

I don't know how to post a new question specifically addressed to you so I
find a message by you and did a reply instead.

This is my question:

I have a text box with its control source set to =[Enter a MIS number] &"
MIS number" and this text box is on a chart report with several subreports on
it.

When the parent report has nothing in its record source(It is an unbound
report), when I open the report, I don't see prompt for [Enter a Mis
Number]. However if I put a table to the report's record source, the prompt
comes up all right. I want the report to stay as it is: an unbound report.

My current work around is to create a table with one record and set it to
the report's recordsource.

Do you have any suggestion why this happens and is there is a better way to
deal with it?

Thanks.


--
Access/VB Programmer

I would start by opening a form with the controls for setting the criteria.
Your subreport Record Source would need something in it SQL like:
WHERE [SomeField] = Forms!frmCriteria!cboMyCombo
You can set the Where Condition of the DoCmd.OpenReport method to set the
filter for your main report.

--
Duane Hookom
Microsoft Access MVP

I have rpt1 based on qry1.
rpt1 has sub-rpt1. sub-rpt1 is based on qry2.
qry1 and qry2 both have a filter. The two filters are different.

My question - how do I set up a prompt form to launch the report with the
filters applied? I understand the basics of coding a WHERE clause and
passing it to the query via OpenArgs for a single report. But with a
subreport added, it’s too complex for me.

I have Access 2003 and Windows xp.

 

[Lydia]

Duane,

Thanks. It works.

Although it is the same amount of work in creating a data entry form and a
table, I prefer the form to keep the report clean.

My only puzzle is why Access stops the prompt when there is no record in the
report.

Lydia


--
Access/VB Programmer

You won't get prompted unless there are records (as you have found). You
should be using references to controls on forms rather than parameter prompts.
--
Duane Hookom
Microsoft Access MVP

Duane,

I don't know how to post a new question specifically addressed to you so I
find a message by you and did a reply instead.

This is my question:

I have a text box with its control source set to =[Enter a MIS number] &"
MIS number" and this text box is on a chart report with several subreports on
it.

When the parent report has nothing in its record source(It is an unbound
report), when I open the report, I don't see prompt for [Enter a Mis
Number]. However if I put a table to the report's record source, the prompt
comes up all right. I want the report to stay as it is: an unbound report.

My current work around is to create a table with one record and set it to
the report's recordsource.

Do you have any suggestion why this happens and is there is a better way to
deal with it?

Thanks.


--
Access/VB Programmer

I would start by opening a form with the controls for setting the criteria.
Your subreport Record Source would need something in it SQL like:
WHERE [SomeField] = Forms!frmCriteria!cboMyCombo
You can set the Where Condition of the DoCmd.OpenReport method to set the
filter for your main report.

--
Duane Hookom
Microsoft Access MVP


:

I have rpt1 based on qry1.
rpt1 has sub-rpt1. sub-rpt1 is based on qry2.
qry1 and qry2 both have a filter. The two filters are different.

My question - how do I set up a prompt form to launch the report with the
filters applied? I understand the basics of coding a WHERE clause and
passing it to the query via OpenArgs for a single report. But with a
subreport added, it’s too complex for me.

I have Access 2003 and Windows xp.

 

[Duane Hookom]

I can't tell you exactly "why" but I expect running the report with records
requires the resolution of all parameters for every record. If there are no
records to print, the parameter ([Enter a MIS number]) doesn't need to be
resolved.

--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks. It works.

Although it is the same amount of work in creating a data entry form and a
table, I prefer the form to keep the report clean.

My only puzzle is why Access stops the prompt when there is no record in the
report.

Lydia


--
Access/VB Programmer

You won't get prompted unless there are records (as you have found). You
should be using references to controls on forms rather than parameter prompts.
--
Duane Hookom
Microsoft Access MVP

Duane,

I don't know how to post a new question specifically addressed to you so I
find a message by you and did a reply instead.

This is my question:

I have a text box with its control source set to =[Enter a MIS number] &"
MIS number" and this text box is on a chart report with several subreports on
it.

When the parent report has nothing in its record source(It is an unbound
report), when I open the report, I don't see prompt for [Enter a Mis
Number]. However if I put a table to the report's record source, the prompt
comes up all right. I want the report to stay as it is: an unbound report.

My current work around is to create a table with one record and set it to
the report's recordsource.

Do you have any suggestion why this happens and is there is a better way to
deal with it?

Thanks.


--
Access/VB Programmer



:

I would start by opening a form with the controls for setting the criteria.
Your subreport Record Source would need something in it SQL like:
WHERE [SomeField] = Forms!frmCriteria!cboMyCombo
You can set the Where Condition of the DoCmd.OpenReport method to set the
filter for your main report.

--
Duane Hookom
Microsoft Access MVP


:

I have rpt1 based on qry1.
rpt1 has sub-rpt1. sub-rpt1 is based on qry2.
qry1 and qry2 both have a filter. The two filters are different.

My question - how do I set up a prompt form to launch the report with the
filters applied? I understand the basics of coding a WHERE clause and
passing it to the query via OpenArgs for a single report. But with a
subreport added, it’s too complex for me.

I have Access 2003 and Windows xp.