Form or Report?

  • Thread starter Thread starter S Jackson
  • Start date Start date
S

S Jackson

I have a continuous form that opens up after the user navigates a series of
dialog boxes selecting criteria. I want the user to be able to print the
resulting list. However, I cannot figure out the VB code to insert the
form's filtered record set into a report. So, one possible solution I came
up with was that it might be easier to write code behind the on-click event
of a command button that converted the continous form to datasheet view,
printed it, and the flipped back to Form View. It wouldn't result in a very
pretty report, but has anyone got a different solution? I welcome your
opinions!

TIA
Shelly
 
Hi Shelly,

Do it like this ---

You have a query (let's call it QryMyFormQuery) for your form that should
include CaseID along with all the other fields that are there for criteria
and/or to display in the form. The key here is to recognize that you have all
the CaseIDs you want in the report.

Create a query to base your report on and be sure to include CaseID. Put the
following expression in the criteria of CaseID:
In (Select CaseID From QryMyFormQuery)

Your report query will now return all the records where CaseID is in
QryMyFormQuery or in other words the same records as are in the form.
 
Hi Steve:

I get this message when attempting to open the report or run then report
query [qryQuickRpt] :

"At most one record can be returned by this subquery"

and then nothing happens. The report never opens and the query never
displays any results.

Here is the SQL view of the report query [qryQuickRpt]:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo
WHERE (((tblCaseInfo.CaseId)=(Select tblCaseInfo.CaseId From qryQuickFrm)))
ORDER BY tblCaseInfo.CaseName;

Here is the SQL from the qryQuickFrm:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo LEFT JOIN tblStatus ON tblCaseInfo.CaseId =
tblStatus.CaseId
WHERE (((tblStatus.ClosedDate) Is Null))
ORDER BY tblCaseInfo.CaseName;

Dunno what to do now . . .

Shelly
 
After further messing around I figured out that the sql in the qryQuickRpt
has to look like this:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo
WHERE (((tblCaseInfo.CaseId)=Any (select CaseId from qryQuickFrm)))
ORDER BY tblCaseInfo.CaseName;

I added the "Any"

But the form that opens from the qryQuickfrm, opens up after a dialog box
that has this code:

Dim stDocName As String
Dim stLinkCriteria As String
Dim varItem As Variant

stDocName = "frmQuickListOpen"

stLinkCriteria = "[tblCaseInfo]![Region] IN ("
For Each varItem In Me.RegionList.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.RegionList.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

If Me.RegionList.ItemsSelected.Count > 0 Then

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "fdlgRegQuickOpen"

Else
MsgBox "Please select one or more regions and try again."
End If

So my subquery in qryQuickRpt does not work because the form is using the
above to open (I am launching the report from the form). So, do I need to
add a SQL statement to the On Click event to the Print my report button on
my form?

Shelly


S. Jackson said:
Hi Steve:

I get this message when attempting to open the report or run then report
query [qryQuickRpt] :

"At most one record can be returned by this subquery"

and then nothing happens. The report never opens and the query never
displays any results.

Here is the SQL view of the report query [qryQuickRpt]:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo
WHERE (((tblCaseInfo.CaseId)=(Select tblCaseInfo.CaseId From qryQuickFrm)))
ORDER BY tblCaseInfo.CaseName;

Here is the SQL from the qryQuickFrm:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo LEFT JOIN tblStatus ON tblCaseInfo.CaseId =
tblStatus.CaseId
WHERE (((tblStatus.ClosedDate) Is Null))
ORDER BY tblCaseInfo.CaseName;

Dunno what to do now . . .

Shelly

PC Datasheet said:
Hi Shelly,

Do it like this ---

You have a query (let's call it QryMyFormQuery) for your form that should
include CaseID along with all the other fields that are there for criteria
and/or to display in the form. The key here is to recognize that you
Click to expand...
have
all
the CaseIDs you want in the report.

Create a query to base your report on and be sure to include CaseID.
Click to expand...
Put
the
following expression in the criteria of CaseID:
In (Select CaseID From QryMyFormQuery)

Your report query will now return all the records where CaseID is in
QryMyFormQuery or in other words the same records as are in the form.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Click to expand...
a
very
Click to expand...
Click to expand...
 
This may not be the most professional or efficient way to do it, but instead
of trying to mess with the queries - which I never got to work - or try to
write VBA code to get the report to use the Form's recordset, I did this:

The form is a continuous form and its purpose is for the user to get a quick
summary look at the data after he has navigated a series of dialog boxes
selecting criteria. The form also allows the user to do other things with
the data -like open up other forms that show more record detail, etc. I
formatted the form so that the background was white and the controls were
black - looks like a report. Put in a "Print my Quick List" button in the
form's footer. In its On click event I set all of the form's cmd buttons
Visible properties to False, set the Form's Footer Visible property to false
(that's where the print cmd button is), then Docmd.printout.

Not the most professional way, but it gets the job done!

I would still welcome any help or advice you have as I would have preferred
being able to set the report's filter to match the form's filter.

Thanks!
Shelly



S. Jackson said:
After further messing around I figured out that the sql in the qryQuickRpt
has to look like this:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo
WHERE (((tblCaseInfo.CaseId)=Any (select CaseId from qryQuickFrm)))
ORDER BY tblCaseInfo.CaseName;

I added the "Any"

But the form that opens from the qryQuickfrm, opens up after a dialog box
that has this code:

Dim stDocName As String
Dim stLinkCriteria As String
Dim varItem As Variant

stDocName = "frmQuickListOpen"

stLinkCriteria = "[tblCaseInfo]![Region] IN ("
For Each varItem In Me.RegionList.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.RegionList.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

If Me.RegionList.ItemsSelected.Count > 0 Then

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "fdlgRegQuickOpen"

Else
MsgBox "Please select one or more regions and try again."
End If

So my subquery in qryQuickRpt does not work because the form is using the
above to open (I am launching the report from the form). So, do I need to
add a SQL statement to the On Click event to the Print my report button on
my form?

Shelly


S. Jackson said:
Hi Steve:

I get this message when attempting to open the report or run then report
query [qryQuickRpt] :

"At most one record can be returned by this subquery"

and then nothing happens. The report never opens and the query never
displays any results.

Here is the SQL view of the report query [qryQuickRpt]:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo
WHERE (((tblCaseInfo.CaseId)=(Select tblCaseInfo.CaseId From qryQuickFrm)))
ORDER BY tblCaseInfo.CaseName;

Here is the SQL from the qryQuickFrm:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo LEFT JOIN tblStatus ON tblCaseInfo.CaseId =
tblStatus.CaseId
WHERE (((tblStatus.ClosedDate) Is Null))
ORDER BY tblCaseInfo.CaseName;

Dunno what to do now . . .

Shelly

PC Datasheet said:
Hi Shelly,

Do it like this ---

You have a query (let's call it QryMyFormQuery) for your form that should
include CaseID along with all the other fields that are there for criteria
and/or to display in the form. The key here is to recognize that you
Click to expand...
have
all
the CaseIDs you want in the report.

Create a query to base your report on and be sure to include CaseID.
Click to expand...
Put
the
following expression in the criteria of CaseID:
In (Select CaseID From QryMyFormQuery)

Your report query will now return all the records where CaseID is in
QryMyFormQuery or in other words the same records as are in the form.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com




I have a continuous form that opens up after the user navigates a series of
dialog boxes selecting criteria. I want the user to be able to
Click to expand...
print
the
resulting list. However, I cannot figure out the VB code to insert the
form's filtered record set into a report. So, one possible solution
Click to expand...
I
came
up with was that it might be easier to write code behind the
Click to expand...
on-click
event
of a command button that converted the continous form to datasheet view,
printed it, and the flipped back to Form View. It wouldn't result
Click to expand...
Click to expand...
in
a
Click to expand...
Click to expand...
 
Shelly,

While looking at your first post I noticed you did not use the "In" keyword with
your subquery. Try adding that and see if it works for you. Should be:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo
WHERE (((tblCaseInfo.CaseId)= In (Select tblCaseInfo.CaseId From qryQuickFrm)))
ORDER BY tblCaseInfo.CaseName;

Steve

S. Jackson said:
This may not be the most professional or efficient way to do it, but instead
of trying to mess with the queries - which I never got to work - or try to
write VBA code to get the report to use the Form's recordset, I did this:

The form is a continuous form and its purpose is for the user to get a quick
summary look at the data after he has navigated a series of dialog boxes
selecting criteria. The form also allows the user to do other things with
the data -like open up other forms that show more record detail, etc. I
formatted the form so that the background was white and the controls were
black - looks like a report. Put in a "Print my Quick List" button in the
form's footer. In its On click event I set all of the form's cmd buttons
Visible properties to False, set the Form's Footer Visible property to false
(that's where the print cmd button is), then Docmd.printout.

Not the most professional way, but it gets the job done!

I would still welcome any help or advice you have as I would have preferred
being able to set the report's filter to match the form's filter.

Thanks!
Shelly



S. Jackson said:
After further messing around I figured out that the sql in the qryQuickRpt
has to look like this:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo
WHERE (((tblCaseInfo.CaseId)=Any (select CaseId from qryQuickFrm)))
ORDER BY tblCaseInfo.CaseName;

I added the "Any"

But the form that opens from the qryQuickfrm, opens up after a dialog box
that has this code:

Dim stDocName As String
Dim stLinkCriteria As String
Dim varItem As Variant

stDocName = "frmQuickListOpen"

stLinkCriteria = "[tblCaseInfo]![Region] IN ("
For Each varItem In Me.RegionList.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.RegionList.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

If Me.RegionList.ItemsSelected.Count > 0 Then

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "fdlgRegQuickOpen"

Else
MsgBox "Please select one or more regions and try again."
End If

So my subquery in qryQuickRpt does not work because the form is using the
above to open (I am launching the report from the form). So, do I need to
add a SQL statement to the On Click event to the Print my report button on
my form?

Shelly


S. Jackson said:
Hi Steve:

I get this message when attempting to open the report or run then report
query [qryQuickRpt] :

"At most one record can be returned by this subquery"

and then nothing happens. The report never opens and the query never
displays any results.

Here is the SQL view of the report query [qryQuickRpt]:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo
WHERE (((tblCaseInfo.CaseId)=(Select tblCaseInfo.CaseId From qryQuickFrm)))
ORDER BY tblCaseInfo.CaseName;

Here is the SQL from the qryQuickFrm:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo LEFT JOIN tblStatus ON tblCaseInfo.CaseId =
tblStatus.CaseId
WHERE (((tblStatus.ClosedDate) Is Null))
ORDER BY tblCaseInfo.CaseName;

Dunno what to do now . . .

Shelly

Hi Shelly,

Do it like this ---

You have a query (let's call it QryMyFormQuery) for your form that should
include CaseID along with all the other fields that are there for criteria
and/or to display in the form. The key here is to recognize that you have
all
the CaseIDs you want in the report.

Create a query to base your report on and be sure to include CaseID. Put
the
following expression in the criteria of CaseID:
In (Select CaseID From QryMyFormQuery)

Your report query will now return all the records where CaseID is in
QryMyFormQuery or in other words the same records as are in the form.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com




I have a continuous form that opens up after the user navigates a
series of
dialog boxes selecting criteria. I want the user to be able to print
the
resulting list. However, I cannot figure out the VB code to insert the
form's filtered record set into a report. So, one possible solution I
came
up with was that it might be easier to write code behind the on-click
event
of a command button that converted the continous form to datasheet view,
printed it, and the flipped back to Form View. It wouldn't result
Click to expand...
in
a
very
pretty report, but has anyone got a different solution? I welcome your
opinions!

TIA
Shelly
Click to expand...
Click to expand...
Click to expand...
 
While looking at your first post I noticed you did not use the "In" keyword with
your subquery. Try adding that and see if it works for you. Should be:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo
WHERE (((tblCaseInfo.CaseId)= In (Select tblCaseInfo.CaseId From qryQuickFrm)))
ORDER BY tblCaseInfo.CaseName;
Click to expand...

Steve, think you had a typo there - you should use the = if the
subselect returns only a single record, or IN() if it returns multiple
CaseID's; you should not use both = and IN, just one or the other. I'm
guessing

WHERE (((tblCaseInfo.CaseID) IN (SELECT...
 
I put your suggestion behind a command button on the Form!frmQuickList to
open the report:
Dim stDocName As String
Dim strSQL As String

stDocName = "rptQuickListOpen"
strSQL = "SELECT tblCaseInfo.Region, tblCaseInfo.CaseId,
tblCaseInfo.DHSNo, " _
& "tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor " _
& "FROM tblCaseInfo WHERE (((tblCaseInfo.CaseId) In (Select CaseId from
qryQuickFrm)))" _
& "ORDER BY tblCaseInfo.CaseName;"

DoCmd.OpenReport stDocName, acViewPreview, , strSQL

I get this message on execute: You have written a subquery that can return
more than one field without using the EXISTS reserved word in the main
query's FROM clause. Review the SELECT statement of the subquery to request
one field.

FYI - the query for the report does not contain a subquery.

S. Jackson

PC Datasheet said:
Shelly,

While looking at your first post I noticed you did not use the "In" keyword with
your subquery. Try adding that and see if it works for you. Should be:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo
WHERE (((tblCaseInfo.CaseId)= In (Select tblCaseInfo.CaseId From qryQuickFrm)))
ORDER BY tblCaseInfo.CaseName;

Steve

S. Jackson said:
This may not be the most professional or efficient way to do it, but instead
of trying to mess with the queries - which I never got to work - or try to
write VBA code to get the report to use the Form's recordset, I did this:

The form is a continuous form and its purpose is for the user to get a quick
summary look at the data after he has navigated a series of dialog boxes
selecting criteria. The form also allows the user to do other things with
the data -like open up other forms that show more record detail, etc. I
formatted the form so that the background was white and the controls were
black - looks like a report. Put in a "Print my Quick List" button in the
form's footer. In its On click event I set all of the form's cmd buttons
Visible properties to False, set the Form's Footer Visible property to false
(that's where the print cmd button is), then Docmd.printout.

Not the most professional way, but it gets the job done!

I would still welcome any help or advice you have as I would have preferred
being able to set the report's filter to match the form's filter.

Thanks!
Shelly



S. Jackson said:
After further messing around I figured out that the sql in the qryQuickRpt
has to look like this:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo
WHERE (((tblCaseInfo.CaseId)=Any (select CaseId from qryQuickFrm)))
ORDER BY tblCaseInfo.CaseName;

I added the "Any"

But the form that opens from the qryQuickfrm, opens up after a dialog box
that has this code:

Dim stDocName As String
Dim stLinkCriteria As String
Dim varItem As Variant

stDocName = "frmQuickListOpen"

stLinkCriteria = "[tblCaseInfo]![Region] IN ("
For Each varItem In Me.RegionList.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.RegionList.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

If Me.RegionList.ItemsSelected.Count > 0 Then

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "fdlgRegQuickOpen"

Else
MsgBox "Please select one or more regions and try again."
End If

So my subquery in qryQuickRpt does not work because the form is using the
above to open (I am launching the report from the form). So, do I need to
add a SQL statement to the On Click event to the Print my report button on
my form?

Shelly


Hi Steve:

I get this message when attempting to open the report or run then report
query [qryQuickRpt] :

"At most one record can be returned by this subquery"

and then nothing happens. The report never opens and the query never
displays any results.

Here is the SQL view of the report query [qryQuickRpt]:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo
WHERE (((tblCaseInfo.CaseId)=(Select tblCaseInfo.CaseId From
qryQuickFrm)))
ORDER BY tblCaseInfo.CaseName;

Here is the SQL from the qryQuickFrm:

SELECT tblCaseInfo.Region, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblCaseInfo.Action, tblCaseInfo.Vendor
FROM tblCaseInfo LEFT JOIN tblStatus ON tblCaseInfo.CaseId =
tblStatus.CaseId
WHERE (((tblStatus.ClosedDate) Is Null))
ORDER BY tblCaseInfo.CaseName;

Dunno what to do now . . .

Shelly

Hi Shelly,

Do it like this ---

You have a query (let's call it QryMyFormQuery) for your form that
should
include CaseID along with all the other fields that are there for
criteria
and/or to display in the form. The key here is to recognize that you
have
all
the CaseIDs you want in the report.

Create a query to base your report on and be sure to include CaseID.
Put
the
following expression in the criteria of CaseID:
In (Select CaseID From QryMyFormQuery)

Your report query will now return all the records where CaseID is in
QryMyFormQuery or in other words the same records as are in the form.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com




I have a continuous form that opens up after the user navigates a
series of
dialog boxes selecting criteria. I want the user to be able to print
the
resulting list. However, I cannot figure out the VB code to insert
the
form's filtered record set into a report. So, one possible
Click to expand...
solution
I
came
up with was that it might be easier to write code behind the on-click
event
of a command button that converted the continous form to datasheet
view,
printed it, and the flipped back to Form View. It wouldn't
Click to expand...
result
in
a
very
pretty report, but has anyone got a different solution? I welcome
your
opinions!

TIA
Shelly
Click to expand...
Click to expand...
Click to expand...
 
Back
Top