Help - Printing a Recordset

  • Thread starter Thread starter Dick Penny
  • Start date Start date
D

Dick Penny

I have been trying for 2-3 weeks (with posts to forms and reports groups) to
print the results of filter by form. Nothing works as replied, something is
always wrong.

So, since I know my Me.recordset (Me=a form) is correct by looking in
immediate window I would like to "shovel" this recordset (which is a type=2
recordset, what is a type 2?) to a Report, or to a table (new & temp) which
would then be the recordsource for a Report. I am using DAO. The
Me.recordset contains only data, not the field names of underlying table.
(setting report source to Me.recordset.name does not work, you get the WHOLE
table underlying the form)

Suggested or "air" code appreciated.
Have a great '04.
 
When opening the report using the DoCmd.OpenReport command, send the form's
RecordSource as the report's RecordSource (as you say you can already do)
and pass the form's Filter value as the WHERE argument.

DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode,
OpenArgs)

Change the WhereCondition to Me.Filter. Don't forget to include the commas
as place holders before the last argument that you use.

Example:
DoCmd.OpenReport "NameOfReport", acViewPreview,, Me.Filter

No need for trailing commas.
 
Wayne,
This does not work. I have tried it and variations for 2+ weeks. It is
because Me.filter contains references (my term "references", not an Access
term) to a "lookup" from a secondary table. It works FINE as a form filter,
but bombs when you pass it to a report. This is why I WANT TO PASS THE
RECORDSET via some mechanism that I do not know/understand.

Next set of ideas?

Dick
Wayne Morgan said:
When opening the report using the DoCmd.OpenReport command, send the form's
RecordSource as the report's RecordSource (as you say you can already do)
and pass the form's Filter value as the WHERE argument.

DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode,
OpenArgs)

Change the WhereCondition to Me.Filter. Don't forget to include the commas
as place holders before the last argument that you use.

Example:
DoCmd.OpenReport "NameOfReport", acViewPreview,, Me.Filter

No need for trailing commas.

--
Wayne Morgan
Microsoft Access MVP


Dick Penny said:
I have been trying for 2-3 weeks (with posts to forms and reports
Click to expand...
groups)
Click to expand...
 
"Dick Penny" said:
Wayne,
This does not work. I have tried it and variations for 2+ weeks. It is
because Me.filter contains references (my term "references", not an Access
term) to a "lookup" from a secondary table. It works FINE as a form filter,
but bombs when you pass it to a report. This is why I WANT TO PASS THE
RECORDSET via some mechanism that I do not know/understand.

Next set of ideas?

Dick
Click to expand...

Dick

You can actually set the recordsource of a report to be a recordset - look at
the Access Web at http://www.mvps.org/access/reports/rpt0014.htm.
 
If Jon's idea doesn't work, paste a copy of the form's filter into a message
and let's see if we can modify it to work with the report.
 
Jon,

Have you tried the code you suggested? I think NOT.

It does not work for sub-sets of data produced by filter-by-from.

Any 2nd ideas??

Dick
 
Wayne,

Here goes, but I have posted in several groups for 2-3 weeks. All I get is
suggestions to use Replace(....) to modify the strings. Yes, I think this
can be done. But WHY is everyone so resistant to passing a recordset to a
report? If it cannot be done (a limit of A2K) sobeit, just tell me. Maybe I
should try to copy the recordset to a table, then feed the table to the
report, all in code behind button on form that has been filtered?

works, 1 example
============
?me.filter

((tblCandidates.FirstName Like "pr*") AND (tblCandidates.Avail=-1))



fails, 2 examples

===========

?me.Filter

((Lookup_cboPos1.Positions="A/P Manager"))

?me.Filter

((Lookup_cboPos1.Positions="A/P Manager")) OR
((Lookup_cboPos3.Positions="Accountant Sr"))



Thanks for your interest & efforts. If I sound frustrated, I AM.



Dick
 
I suspect that Lookup_cboPos1 is a combo box on the form that is calling the
report. The report won't know anything about this combo box. What you'll
need to replace Lookup_cboPos1 with the field name that contains the value
"A/P Manager" that you are trying to filter on, provided that this field is
available in the report's record source.


Also,
In Jon's code, what happens if you set the recordset to Me.RecordsetClone
instead of the CurrentDb.OpenRecordset?
 
dick penny said:
Wayne,

Here goes, but I have posted in several groups for 2-3 weeks. All I
get is suggestions to use Replace(....) to modify the strings. Yes, I
think this can be done. But WHY is everyone so resistant to passing
a recordset to a report? If it cannot be done (a limit of A2K)
sobeit, just tell me.
Click to expand...

I don't see anything in the help file that says so, but the error
message I get when I try to set the Recordset property of a report --
"Run-time error '2593': This feature is not available in an MDB." --
seems to state pretty clearly that you can't do this in an MDB file,
though it implies that you may be able to do it in an ADP. Further
research turns up this KB article:


http://support.microsoft.com/default.aspx?scid=kb;en-us;287437&Product=acc2002

So your answer is clear: you can't do it the way you want; at least
not if you're working in an MDB. Since you keep talking about ADO
recordsets, are you working in an ADP? Otherwise, the form's recordset
would normally be a DAO recordset.

[snip]
fails, 2 examples

===========

?me.Filter

((Lookup_cboPos1.Positions="A/P Manager"))

?me.Filter

((Lookup_cboPos1.Positions="A/P Manager")) OR
((Lookup_cboPos3.Positions="Accountant Sr"))
Click to expand...

I can see what's going on; those "Lookup_..." names are a dead
giveaway. Your form is being filtered on the displayed column of a
combo box, when that column is not the bound column. Therefore, the
values being filtered on don't exist in the form's recordsource at all.
What Access does in these cases is create an internal lookup query and
join it to the form's recordsource, so as to provide the values for
filtering. This internal query is given the prefix "Lookup_", which is
tacked onto the name of the control being filtered.

Hmm, what to do about it. I can think of two possible solutions, both
complicated. The first is to create a recordsource query for your
report that defines the same helper lookup queries as the includes the
same. That way, the form's filter string will be just as applicable to
the report because the report's recordsource will also contain fields
such as Lookup_cboPos1.Positions and Lookup_cboPos3.Positions. I've
just tested this out with a test form and report and it works in
principle.

The second approach involves you preprocessing the filter string in code
to identify the lookup fields; then do your own lookup and replace
those criteria in the filter string with the field names and values that
*will* appear in the report's recordsource. For example, you would
start with this string:

((Lookup_cboPos1.Positions="A/P Manager"))

parse out the Lookup_cboPos1.Positions specification, identify the table
and field being looked up, figure out that (maybe) this lookup
corresponds to the Pos1 field being equal to 123, rewrite the filter
string to:

((Pos1=123))

and pass that to the report. All this parsing and interpreting could be
difficult, but I think it could be made to work.
 
To all who have responded - THANK YOU. (and yes, that is YELLING)

I did not want to change the underlying DB, nor the "main form" used to find
and filter data records, nor the 2nd tables populating combo-boxes, nor
created a multi-source query to feed the form, nor muck with character
replacements (len, instr,mid, right, left,etc.) in the filter string. (I'm
only a finance guy doing maintainence on somebody else's DB - but I am
tenacious.)

1) I duplicated the underlying data table's structure into a permanent table
TEMP.
Then in code behing the button on search form:
2) I executed a SQL to empty table TEMP.
3) I opened a recordset on/of/for table TEMP.
4) I copied the Me.recordset (the filtered results) into recordset of TEMP.
(by iterating thru records and fields within records)
5) I "Docmd opened" a report based on table TEMP.

Dick Penny
 
dick penny said:
To all who have responded - THANK YOU. (and yes, that is YELLING)

I did not want to change the underlying DB, nor the "main form" used
to find and filter data records, nor the 2nd tables populating
combo-boxes, nor created a multi-source query to feed the form, nor
muck with character replacements (len, instr,mid, right, left,etc.)
in the filter string. (I'm only a finance guy doing maintainence on
somebody else's DB - but I am tenacious.)

1) I duplicated the underlying data table's structure into a
permanent table TEMP.
Then in code behing the button on search form:
2) I executed a SQL to empty table TEMP.
3) I opened a recordset on/of/for table TEMP.
4) I copied the Me.recordset (the filtered results) into recordset of
TEMP. (by iterating thru records and fields within records)
5) I "Docmd opened" a report based on table TEMP.

Dick Penny
Click to expand...

Congratulations on finding a workable solution to your problem. Very
ingenious for a "finance guy"! Do note that the repeated
loading/emptying of your TEMP table is likely to make your database grow
in size, so you'll probably want to compact it regularly.
 
from Access 2K3 VB help ,

|"Recordset Property
|See AlsoApplies ToExampleSpecificsReturns or sets the ADO Recordset or
DAO Recordset |object representing the record source for the specified
form, report, list box control, or combo |box control. Read/write.
|
|expression.Recordset
|expression Required. An expression that returns one of the objects
in the Applies To list.
|
|Remarks
|You cannot use this property with ODBCDirect recordset types in DAO.
|
|The Recordset property returns the recordset object that provides the
data being browsed in a |form, report, list box control, or combo box
control. If a form is based on a query, for example, |referring to the
Recordset property is the equivalent of cloning a Recordset object by
using the |same query. However, unlike using the RecordsetClone
property, changing which record is |current in the recordset returned
by the form's Recordset property also sets the current record |of the
form.
|
|This property is available only by using Visual Basic.
|
|The read/write behavior of the Recordset property is determined by the
type of recordset (ADO |or DAO) and the type of data (Jet or SQL)
contained in the recordset identified by the property.
|
|Recordset type Based on SQL data Based on Jet data
|ADO Read/Write Read/Write
|DAO N/A Read/Write


seems to indicate that it is intended that the recordset property of a
report be writeable for ADO recordsets from SQL Server. However, if in
the open event of a report I open an ADO recordset (verified in
immediate as being open, containing data and ADO) and then try to set
me.recordset to the ado recordset, the "Run-time error '2593': This
feature is not available in an MDB." message pops up.

Jon's suggestion above of Access Web's example is for DAO - ADO
recordsets do not have a name property. DAO recordsets are indicated
by HELP to not work for SQL data, but I guess I'll try that next in
hopes that Access's VB Help is bassackwards on this.

The reason I wish to do it this way is that changing a saved
passthrough querydef is seen by Access as a code change, thereby
removing the code signing signature unless the user has the correct
certificate, which they will not have. Microsoft Knowledge Base article
828412 acknowledges that "Programmatically modifying an action query in
an Access database invalidates the digital signature of the Access
database", but they appear to not know that it is more widespread and
affects all passthrough querydefs as well. It's possible that all
passthrough queries are defined as action queries, but the prescribed
workaround of "Don't do that" is totally inadequate.

I'm losing hair. If I change a querydef in code, the signature is lost
and the users get macro warnings - if I try to change the recordset
property to an open recordset, it tells me that I can't do that in an
mdb, if I try to change the recordsource property of the report, it is
assumed to be Access sql and no longer a passthough, even though the
saved recordsource is passthrough.

Am I missing something obvious? All I want to do is open a report with
a runtime generated passthough sql statement without losing the code
siging digital signature.
 
Back
Top