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.

 

[Lydia]

Durane,

All right. Thanks.

I have another problem I need your help.

My database imports data from excel files. Because the size of the files,
the importing could run for 10 minutes. The process brings records one at a
time into the database table. (Because the way excel decides the data type
based on first several rows, I don't use transferspreadsheet method to avoid
trouble of losing data or messages such as Numeric Field Overflow. I also
don't like using transfrespreadsheet method because Access can't close Excel
after running transferspreadsheet. To be able to close the excel, I have to
save formatted excel file, close it and then do transferspreadsheet. The
problem with it is this will change the orignial excel file. In a word,
communication with excel could be so much headache. If you have suggestions
to work around all these troubles, I would love to hear them.)

Anyway, this importing is once a month thing, so my boss doesn't quite mind
about it. And my way pulls in most exact data.

My problem is everytime when I am testing the importing, if an end user
happens to work on the database at the same time, even if he is working on a
form that is based on an unrelated table, he sees the message like "Currently
locked, unable to modify." I wonder why this happens and how to solve the
issue.

This is a split-into-font-and-back-end database.

Thanks.

Lydia




--
Access/VB Programmer

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.

 

[Duane Hookom]

You might want to start a new thread since this is a completely different
question and has nothing to do with reports.

My generally solution for importing from Excel is to import into a temporary
table and then processing as needed.

--
Duane Hookom
Microsoft Access MVP

Durane,

All right. Thanks.

I have another problem I need your help.

My database imports data from excel files. Because the size of the files,
the importing could run for 10 minutes. The process brings records one at a
time into the database table. (Because the way excel decides the data type
based on first several rows, I don't use transferspreadsheet method to avoid
trouble of losing data or messages such as Numeric Field Overflow. I also
don't like using transfrespreadsheet method because Access can't close Excel
after running transferspreadsheet. To be able to close the excel, I have to
save formatted excel file, close it and then do transferspreadsheet. The
problem with it is this will change the orignial excel file. In a word,
communication with excel could be so much headache. If you have suggestions
to work around all these troubles, I would love to hear them.)

Anyway, this importing is once a month thing, so my boss doesn't quite mind
about it. And my way pulls in most exact data.

My problem is everytime when I am testing the importing, if an end user
happens to work on the database at the same time, even if he is working on a
form that is based on an unrelated table, he sees the message like "Currently
locked, unable to modify." I wonder why this happens and how to solve the
issue.

This is a split-into-font-and-back-end database.

Thanks.

Lydia




--
Access/VB Programmer

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.

 

[PeterK]

Duane,

Just a note that the thread we started that Lydia has hooked onto hasn't
been resolved yet - unless you posted a reply that I can't find. I had
assumed you were away or busy on other projects, but if you can take a few
minutes to guide me with some suggestions that would be great.

 

[Duane Hookom]

Opening a main report with a Where Condition doesn't have any affect on the
filtering of a subreport. I use one of the following methods for setting the
filter of a subreport:
- set the Link Master/Child
- code references to controls on forms in the Record Source of the subreport
- use code to change the SQL property of a saved query that is part of the
subreport's Record Source.

--
Duane Hookom
Microsoft Access MVP

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.

 

[PeterK]

Thanks Duane.
If I understand you correctly, you're saying I need to open the main report
filtering with a Where Condition using OpenArgs; and filter the subreport
using one of the suggestions you've given.

If so, I now have 2 problems.

First problem is opening the main report. The code you gave me worked with
one parameter (intStudentID) but I couldn't add the second parameter
correctly (dtTransactionDate).

Second problem is I'm not sure I have the skills to set the extra coding for
the subreport filter. What info do you need to guide me with the specifics?
--
PeterK

Opening a main report with a Where Condition doesn't have any affect on the
filtering of a subreport. I use one of the following methods for setting the
filter of a subreport:
- set the Link Master/Child
- code references to controls on forms in the Record Source of the subreport
- use code to change the SQL property of a saved query that is part of the
subreport's Record Source.

--
Duane Hookom
Microsoft Access MVP

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.