Access Subreport from Concatenated Query

J

Joyce12345

I have created a query where I concatenate (based on the basConcatenate
module sample created by Duane Hookem) comments from evaluation forms. The
problem I am having is that I need to filter the query or the results on the
subreport by date.

For the main report and other subreports I am using a Date Filter Dialog
form (similar to the example on Northwind) but when I try to do the same for
this subreport or even the concatenated query I receive an error which
highligts the lines on the module
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Now I know this module works as I have used it previously but it doesn't
work with the filter.

My experience with VBA is limited though I do try to understand any samples
I use.

Can anyone explain where I am going wrong and hopefully point me in the
right direction.

Thanks in advance.
 
D

Duane Hookom

You should provide more information so we don't have to open Northwind to
find out what you are talking about. How exactly is your report filtered? Do
you have a form with controls that stays open?

What is the syntax of your query with the Concatenate() function?

I expect you need to leave a form open. Another solution would be to use a
small, 1 record table with 2 date fields to supply your criteria.
 
J

Joyce12345

Sorry Duane,

The main report and the other subforms are based on queries which all
contain a "SessionDate". The criteria on this field of the query is
Between [Forms]![DateFilterDialog]![StartDate] And
[Forms]![DateFilterDialog]![EndDate].

DateFilterDialog being the form that opens automatically when the Report is
opened and the DoCmd.OpenForm runs. Full code is
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "DateFilterDialog", , , , , acDialog, Me.Name
If Not IsLoaded("DateFilterDialog") Then
Cancel = True
End If
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub
Private Sub Report_Close()
DoCmd.Close acForm, "DateFilterDialog"
End Sub

The problem query has a VenueID field and also
contains concatenated fields (thanks for being such a good
teacher/provider), e.g.
GNCombined: Concatenate("SELECT GoodNews FROM QREvalQuery WHERE (VenueID) ="
& [VenueID]).

Full SQL:
SELECT Venue.VenueID, Concatenate("SELECT GoodNews FROM QREvalQuery WHERE
(VenueID) =" & [VenueID]) AS GNCombined
FROM Venue;

The QREvalQuery contains fields for VenueID, SessionDate and GoodNews with
the DateFilter on the SessionID Criteria.
Full SQL:
SELECT Venue.VenueID, PlaySessions.SessionDate, EvaluationForm.GoodNews FROM
Venue INNER JOIN (PlaySessions INNER JOIN EvaluationForm ON
PlaySessions.SessionID = EvaluationForm.SessionID) ON Venue.VenueID =
PlaySessions.ClubName
WHERE (((PlaySessions.SessionDate) Between
[Forms]![DateFilterDialog]![StartDate] And
[Forms]![DateFilterDialog]![EndDate]));

Hope this is clearer for you, thanks for looking.
 
D

Duane Hookom

I don't think I have ever used the solution that uses code in the report open
to open the dialog form. I almost always open a report with the "criteria"
form allready open with values previously entered into the controls.

You could try comment out the closing of the form in favor of hiding it.
Private Sub Report_Close()
'DoCmd.Close acForm, "DateFilterDialog"
Forms!DateFilterDialog.Visible = False
End Sub

If that doesn't work, I would try the 1 record table solution.
--
Duane Hookom
Microsoft Access MVP


Joyce12345 said:
Sorry Duane,

The main report and the other subforms are based on queries which all
contain a "SessionDate". The criteria on this field of the query is
Between [Forms]![DateFilterDialog]![StartDate] And
[Forms]![DateFilterDialog]![EndDate].

DateFilterDialog being the form that opens automatically when the Report is
opened and the DoCmd.OpenForm runs. Full code is
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "DateFilterDialog", , , , , acDialog, Me.Name
If Not IsLoaded("DateFilterDialog") Then
Cancel = True
End If
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub
Private Sub Report_Close()
DoCmd.Close acForm, "DateFilterDialog"
End Sub

The problem query has a VenueID field and also
contains concatenated fields (thanks for being such a good
teacher/provider), e.g.
GNCombined: Concatenate("SELECT GoodNews FROM QREvalQuery WHERE (VenueID) ="
& [VenueID]).

Full SQL:
SELECT Venue.VenueID, Concatenate("SELECT GoodNews FROM QREvalQuery WHERE
(VenueID) =" & [VenueID]) AS GNCombined
FROM Venue;

The QREvalQuery contains fields for VenueID, SessionDate and GoodNews with
the DateFilter on the SessionID Criteria.
Full SQL:
SELECT Venue.VenueID, PlaySessions.SessionDate, EvaluationForm.GoodNews FROM
Venue INNER JOIN (PlaySessions INNER JOIN EvaluationForm ON
PlaySessions.SessionID = EvaluationForm.SessionID) ON Venue.VenueID =
PlaySessions.ClubName
WHERE (((PlaySessions.SessionDate) Between
[Forms]![DateFilterDialog]![StartDate] And
[Forms]![DateFilterDialog]![EndDate]));

Hope this is clearer for you, thanks for looking.



Duane Hookom said:
You should provide more information so we don't have to open Northwind to
find out what you are talking about. How exactly is your report filtered? Do
you have a form with controls that stays open?

What is the syntax of your query with the Concatenate() function?

I expect you need to leave a form open. Another solution would be to use a
small, 1 record table with 2 date fields to supply your criteria.
Click to expand...
Click to expand...
 
J

Joyce12345

Thanks for your help Duane - it didn't work so I will try your suggestion of
the 1 record table.

Joyce

Duane Hookom said:
I don't think I have ever used the solution that uses code in the report open
to open the dialog form. I almost always open a report with the "criteria"
form allready open with values previously entered into the controls.

You could try comment out the closing of the form in favor of hiding it.
Private Sub Report_Close()
'DoCmd.Close acForm, "DateFilterDialog"
Forms!DateFilterDialog.Visible = False
End Sub

If that doesn't work, I would try the 1 record table solution.
--
Duane Hookom
Microsoft Access MVP


Joyce12345 said:
Sorry Duane,

The main report and the other subforms are based on queries which all
contain a "SessionDate". The criteria on this field of the query is
Between [Forms]![DateFilterDialog]![StartDate] And
[Forms]![DateFilterDialog]![EndDate].

DateFilterDialog being the form that opens automatically when the Report is
opened and the DoCmd.OpenForm runs. Full code is
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "DateFilterDialog", , , , , acDialog, Me.Name
If Not IsLoaded("DateFilterDialog") Then
Cancel = True
End If
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub
Private Sub Report_Close()
DoCmd.Close acForm, "DateFilterDialog"
End Sub

The problem query has a VenueID field and also
contains concatenated fields (thanks for being such a good
teacher/provider), e.g.
GNCombined: Concatenate("SELECT GoodNews FROM QREvalQuery WHERE (VenueID) ="
& [VenueID]).

Full SQL:
SELECT Venue.VenueID, Concatenate("SELECT GoodNews FROM QREvalQuery WHERE
(VenueID) =" & [VenueID]) AS GNCombined
FROM Venue;

The QREvalQuery contains fields for VenueID, SessionDate and GoodNews with
the DateFilter on the SessionID Criteria.
Full SQL:
SELECT Venue.VenueID, PlaySessions.SessionDate, EvaluationForm.GoodNews FROM
Venue INNER JOIN (PlaySessions INNER JOIN EvaluationForm ON
PlaySessions.SessionID = EvaluationForm.SessionID) ON Venue.VenueID =
PlaySessions.ClubName
WHERE (((PlaySessions.SessionDate) Between
[Forms]![DateFilterDialog]![StartDate] And
[Forms]![DateFilterDialog]![EndDate]));

Hope this is clearer for you, thanks for looking.



Duane Hookom said:
You should provide more information so we don't have to open Northwind to
find out what you are talking about. How exactly is your report filtered? Do
you have a form with controls that stays open?

What is the syntax of your query with the Concatenate() function?

I expect you need to leave a form open. Another solution would be to use a
small, 1 record table with 2 date fields to supply your criteria.

--
Duane Hookom
Microsoft Access MVP


:

I have created a query where I concatenate (based on the basConcatenate
module sample created by Duane Hookem) comments from evaluation forms. The
problem I am having is that I need to filter the query or the results on the
subreport by date.

For the main report and other subreports I am using a Date Filter Dialog
form (similar to the example on Northwind) but when I try to do the same for
this subreport or even the concatenated query I receive an error which
highligts the lines on the module
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Now I know this module works as I have used it previously but it doesn't
work with the filter.

My experience with VBA is limited though I do try to understand any samples
I use.

Can anyone explain where I am going wrong and hopefully point me in the
right direction.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter subreport 3
Subreport visability based on query 1
Query within a query 4
Subreport behind report data 2
Access Subreport Can Grow Problem 2
Report/Subreport Problems with Sorting/Grouping, Access 2003 2
Subreport Problem 10
concatenate child records 20

Top