can't access recordset from report code module?

[Randall Arnold]

I need to set a table status flag after a report has run. The field is
Printed with a boolean of True or False. I set a flag in the Detail_Print
sub (fPrinted = true) then expect the following code to do the table update:

----------------------------------------------------
Private Sub Report_Close()
Dim iPrintOK As Integer
If fPrinted Then
iPrintOK = MsgBox("Did all prints come out okay?", vbYesNo, "Print
Confirmation")
If iPrintOK = vbYes Then
Me.Recordset.MoveFirst
While Not Me.Recordset.EOF
Me.Recordset![Printed] = True
Me.Recordset.MoveNext
Wend
Me.Recordset.Update
Else
DoCmd.OpenForm "DT_check"
End If
End If
End Sub
---------------------------------------------------

When the VB interpreter hits the Me.Recordset.Movefirst code, I get an error
stating "this functionality is not available in an MDB". ??? I don't get
that. Does this mean there's no way to accomplish my goal? Or does anyone
else know of a way to do this?

TIA,

Randall Arnold

 

[Allen Browne]

No, the approach you suggest will not work properly in Access.

You will not be able to loop through the records like that. You could handle
one record at a time (in Detail_Print), but - as your MsgBox implies - you
do not know if all records came out okay at that stage. Neither can you just
read the RecordSource property of the report and update the Printed field,
since the report may have been filtered and there is a bug in Access where
it does not reliably report the FilterOn property for reports.

The best solution might be to keep a log of which records were printed when.
It is then very easy to get a list of those that have not been printed. The
interface is a small unbound form where the user can select the records to
print. You write the primary key value of these records to a table, along
with a batch number for this print run. The report's query then reads the
records that match this batch. At the end, if things did not print correctly
(or if the user wants to have another go at the batch), you can delete the
log for this batch, and let them have another go. Ultimately, you have a
record of not only *if* the record was supposed to have been printed
sometime, but *when* the record was printed, and that is generally very
useful.

Hope that heips.

 

[Randall Arnold]

Here's my problem: the user will usually have a batch of sheets to print
(the reports will rarely be one page). There are occasions when one or more
don't print properly due to errors outside of Access (network issues, toner,
sticky pages, etc). The user wants to see which individual sheets of a
report have been successfully printed and which ones may need to be rerun.
He/she will check off the successful prints in a special form (DT_check) and
resubmit the remainder. Note that Quickbooks offers a similar feature. In
addition, report sheets that are marked as printed will not show up in
subsequent report generations as there will be no need; in other words, each
report generation needs to be new and not contain ANY prior printed sheets.

I've tried different approaches, but tackling this from the report itself
makes the most sense. For the life of me I don't understand why the
recordset udnerlying the report cannot be accessed. Any idea?

Regardless, looks like it's back to the drawing board...

Randall Arnold

No, the approach you suggest will not work properly in Access.

You will not be able to loop through the records like that. You could
handle
one record at a time (in Detail_Print), but - as your MsgBox implies - you
do not know if all records came out okay at that stage. Neither can you
just
read the RecordSource property of the report and update the Printed field,
since the report may have been filtered and there is a bug in Access where
it does not reliably report the FilterOn property for reports.

The best solution might be to keep a log of which records were printed
when.
It is then very easy to get a list of those that have not been printed.
The
interface is a small unbound form where the user can select the records to
print. You write the primary key value of these records to a table, along
with a batch number for this print run. The report's query then reads the
records that match this batch. At the end, if things did not print
correctly
(or if the user wants to have another go at the batch), you can delete the
log for this batch, and let them have another go. Ultimately, you have a
record of not only *if* the record was supposed to have been printed
sometime, but *when* the record was printed, and that is generally very
useful.

Hope that heips.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to set a table status flag after a report has run. The field is
Printed with a boolean of True or False. I set a flag in the
Detail_Print
sub (fPrinted = true) then expect the following code to do the table update:

----------------------------------------------------
Private Sub Report_Close()
Dim iPrintOK As Integer
If fPrinted Then
iPrintOK = MsgBox("Did all prints come out okay?", vbYesNo, "Print
Confirmation")
If iPrintOK = vbYes Then
Me.Recordset.MoveFirst
While Not Me.Recordset.EOF
Me.Recordset![Printed] = True
Me.Recordset.MoveNext
Wend
Me.Recordset.Update
Else
DoCmd.OpenForm "DT_check"
End If
End If
End Sub
---------------------------------------------------

When the VB interpreter hits the Me.Recordset.Movefirst code, I get an error
stating "this functionality is not available in an MDB". ??? I don't
get
that. Does this mean there's no way to accomplish my goal? Or does anyone
else know of a way to do this?

TIA,

Randall Arnold

 

[Allen Browne]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here's my problem: the user will usually have a batch of sheets to print
(the reports will rarely be one page). There are occasions when one or more
don't print properly due to errors outside of Access (network issues, toner,
sticky pages, etc). The user wants to see which individual sheets of a
report have been successfully printed and which ones may need to be rerun.
He/she will check off the successful prints in a special form (DT_check) and
resubmit the remainder. Note that Quickbooks offers a similar feature. In
addition, report sheets that are marked as printed will not show up in
subsequent report generations as there will be no need; in other words, each
report generation needs to be new and not contain ANY prior printed sheets.

I've tried different approaches, but tackling this from the report itself
makes the most sense. For the life of me I don't understand why the
recordset udnerlying the report cannot be accessed. Any idea?

Regardless, looks like it's back to the drawing board...

Randall Arnold

No, the approach you suggest will not work properly in Access.

You will not be able to loop through the records like that. You could
handle
one record at a time (in Detail_Print), but - as your MsgBox implies - you
do not know if all records came out okay at that stage. Neither can you
just
read the RecordSource property of the report and update the Printed field,
since the report may have been filtered and there is a bug in Access where
it does not reliably report the FilterOn property for reports.

The best solution might be to keep a log of which records were printed
when.
It is then very easy to get a list of those that have not been printed.
The
interface is a small unbound form where the user can select the records to
print. You write the primary key value of these records to a table, along
with a batch number for this print run. The report's query then reads the
records that match this batch. At the end, if things did not print
correctly
(or if the user wants to have another go at the batch), you can delete the
log for this batch, and let them have another go. Ultimately, you have a
record of not only *if* the record was supposed to have been printed
sometime, but *when* the record was printed, and that is generally very
useful.

Hope that heips.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to set a table status flag after a report has run. The field is
Printed with a boolean of True or False. I set a flag in the
Detail_Print
sub (fPrinted = true) then expect the following code to do the table update:

----------------------------------------------------
Private Sub Report_Close()
Dim iPrintOK As Integer
If fPrinted Then
iPrintOK = MsgBox("Did all prints come out okay?", vbYesNo, "Print
Confirmation")
If iPrintOK = vbYes Then
Me.Recordset.MoveFirst
While Not Me.Recordset.EOF
Me.Recordset![Printed] = True
Me.Recordset.MoveNext
Wend
Me.Recordset.Update
Else
DoCmd.OpenForm "DT_check"
End If
End If
End Sub
---------------------------------------------------

When the VB interpreter hits the Me.Recordset.Movefirst code, I get an error
stating "this functionality is not available in an MDB". ??? I don't
get
that. Does this mean there's no way to accomplish my goal? Or does anyone
else know of a way to do this?

TIA,

Randall Arnold

 

[Allen Browne]

If you record the primary key value of the records the did print, it's easy
enough to show the user that and allow them to delete the ones that didn't,
should you wish to give them the granularity of an individual record. If you
*really* want to use a yes/no IsPrinted field in your table, you could then
use an Update query to set IsPrinted to True for the records in the temp
table when Report_Close fires.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here's my problem: the user will usually have a batch of sheets to print
(the reports will rarely be one page). There are occasions when one or more
don't print properly due to errors outside of Access (network issues, toner,
sticky pages, etc). The user wants to see which individual sheets of a
report have been successfully printed and which ones may need to be rerun.
He/she will check off the successful prints in a special form (DT_check) and
resubmit the remainder. Note that Quickbooks offers a similar feature. In
addition, report sheets that are marked as printed will not show up in
subsequent report generations as there will be no need; in other words, each
report generation needs to be new and not contain ANY prior printed sheets.

I've tried different approaches, but tackling this from the report itself
makes the most sense. For the life of me I don't understand why the
recordset udnerlying the report cannot be accessed. Any idea?

Regardless, looks like it's back to the drawing board...

Randall Arnold

No, the approach you suggest will not work properly in Access.

You will not be able to loop through the records like that. You could
handle
one record at a time (in Detail_Print), but - as your MsgBox implies - you
do not know if all records came out okay at that stage. Neither can you
just
read the RecordSource property of the report and update the Printed field,
since the report may have been filtered and there is a bug in Access where
it does not reliably report the FilterOn property for reports.

The best solution might be to keep a log of which records were printed
when.
It is then very easy to get a list of those that have not been printed.
The
interface is a small unbound form where the user can select the records to
print. You write the primary key value of these records to a table, along
with a batch number for this print run. The report's query then reads the
records that match this batch. At the end, if things did not print
correctly
(or if the user wants to have another go at the batch), you can delete the
log for this batch, and let them have another go. Ultimately, you have a
record of not only *if* the record was supposed to have been printed
sometime, but *when* the record was printed, and that is generally very
useful.

Hope that heips.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to set a table status flag after a report has run. The field is
Printed with a boolean of True or False. I set a flag in the
Detail_Print
sub (fPrinted = true) then expect the following code to do the table update:

----------------------------------------------------
Private Sub Report_Close()
Dim iPrintOK As Integer
If fPrinted Then
iPrintOK = MsgBox("Did all prints come out okay?", vbYesNo, "Print
Confirmation")
If iPrintOK = vbYes Then
Me.Recordset.MoveFirst
While Not Me.Recordset.EOF
Me.Recordset![Printed] = True
Me.Recordset.MoveNext
Wend
Me.Recordset.Update
Else
DoCmd.OpenForm "DT_check"
End If
End If
End Sub
---------------------------------------------------

When the VB interpreter hits the Me.Recordset.Movefirst code, I get an error
stating "this functionality is not available in an MDB". ??? I don't
get
that. Does this mean there's no way to accomplish my goal? Or does anyone
else know of a way to do this?

TIA,

Randall Arnold

 

[Randall Arnold]

I guess I'm not explaining my dilemma very well.

The user won't know until after visually inspecting the print batch which
ones printed successfully. I can't seem to run *any* code from Report_Close
that touches records. When Me.Recordset didn't work, I tried to DIM a
recordset, going into the table itself, and got the exact same error. So I
can't see how to update my printed field at all from a report if Access
won't allow such code to execute (which appears to be the case, and I still
don't understand why not).

Unless, of course, I'm just so thickheaded that I can't grasp your solution.
The customer is beating me up on this and I'd be glad to implement ANYthing
what would work-- even if it meant getting away from the Printed field
approach!



Randall Arnold

If you record the primary key value of the records the did print, it's
easy
enough to show the user that and allow them to delete the ones that
didn't,
should you wish to give them the granularity of an individual record. If
you
*really* want to use a yes/no IsPrinted field in your table, you could
then
use an Update query to set IsPrinted to True for the records in the temp
table when Report_Close fires.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here's my problem: the user will usually have a batch of sheets to print
(the reports will rarely be one page). There are occasions when one or more
don't print properly due to errors outside of Access (network issues, toner,
sticky pages, etc). The user wants to see which individual sheets of a
report have been successfully printed and which ones may need to be
rerun.
He/she will check off the successful prints in a special form (DT_check) and
resubmit the remainder. Note that Quickbooks offers a similar feature. In
addition, report sheets that are marked as printed will not show up in
subsequent report generations as there will be no need; in other words, each
report generation needs to be new and not contain ANY prior printed sheets.

I've tried different approaches, but tackling this from the report itself
makes the most sense. For the life of me I don't understand why the
recordset udnerlying the report cannot be accessed. Any idea?

Regardless, looks like it's back to the drawing board...

Randall Arnold

No, the approach you suggest will not work properly in Access.

You will not be able to loop through the records like that. You could
handle
one record at a time (in Detail_Print), but - as your MsgBox implies - you
do not know if all records came out okay at that stage. Neither can you
just
read the RecordSource property of the report and update the Printed field,
since the report may have been filtered and there is a bug in Access where
it does not reliably report the FilterOn property for reports.

The best solution might be to keep a log of which records were printed
when.
It is then very easy to get a list of those that have not been printed.
The
interface is a small unbound form where the user can select the records to
print. You write the primary key value of these records to a table, along
with a batch number for this print run. The report's query then reads the
records that match this batch. At the end, if things did not print
correctly
(or if the user wants to have another go at the batch), you can delete the
log for this batch, and let them have another go. Ultimately, you have
a
record of not only *if* the record was supposed to have been printed
sometime, but *when* the record was printed, and that is generally very
useful.

Hope that heips.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to set a table status flag after a report has run. The field
is
Printed with a boolean of True or False. I set a flag in the
Detail_Print
sub (fPrinted = true) then expect the following code to do the table
update:

----------------------------------------------------
Private Sub Report_Close()
Dim iPrintOK As Integer
If fPrinted Then
iPrintOK = MsgBox("Did all prints come out okay?", vbYesNo, "Print
Confirmation")
If iPrintOK = vbYes Then
Me.Recordset.MoveFirst
While Not Me.Recordset.EOF
Me.Recordset![Printed] = True
Me.Recordset.MoveNext
Wend
Me.Recordset.Update
Else
DoCmd.OpenForm "DT_check"
End If
End If
End Sub
---------------------------------------------------

When the VB interpreter hits the Me.Recordset.Movefirst code, I get an
error
stating "this functionality is not available in an MDB". ??? I don't
get
that. Does this mean there's no way to accomplish my goal? Or does
anyone
else know of a way to do this?

TIA,

Randall Arnold

 

[Allen Browne]

Okay. Simplify the idea. This is almost as much work as keeping the full
log, but may be easier to grasp.

Assuming your report has a unique numeric field such as ClientID, create a
table with just one field field: ClientID, type Number (size Long Integer).

Change the RecordSource of your report to a query that includes the temp
table, joined on ClientID. The report is now restricted to only the clients
in the temp table.

In the interface that will open your report (e.g. click of a command
button), execute two query statements before the OpenReport:
- a Delete (to clear the temp table), and
- an Append, to add the ClientID value for the desired records.

In the Close event of the report, open a continuous form that shows the
records that were in the report. Let the user delete any that did not print
right (from the temp table). The use an Okay button to execute an Update
query statement to update the Printed field in the real table for the ones
that printed okay.

Exeuting an action query in code looks like this:
strSQL = "DELETE FROM MyTable;"
dbEngine(0)(0).Execute strSQL, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I guess I'm not explaining my dilemma very well.

The user won't know until after visually inspecting the print batch which
ones printed successfully. I can't seem to run *any* code from Report_Close
that touches records. When Me.Recordset didn't work, I tried to DIM a
recordset, going into the table itself, and got the exact same error. So I
can't see how to update my printed field at all from a report if Access
won't allow such code to execute (which appears to be the case, and I still
don't understand why not).

Unless, of course, I'm just so thickheaded that I can't grasp your solution.
The customer is beating me up on this and I'd be glad to implement ANYthing
what would work-- even if it meant getting away from the Printed field
approach!



Randall Arnold

If you record the primary key value of the records the did print, it's
easy
enough to show the user that and allow them to delete the ones that
didn't,
should you wish to give them the granularity of an individual record. If
you
*really* want to use a yes/no IsPrinted field in your table, you could
then
use an Update query to set IsPrinted to True for the records in the temp
table when Report_Close fires.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here's my problem: the user will usually have a batch of sheets to print
(the reports will rarely be one page). There are occasions when one or more
don't print properly due to errors outside of Access (network issues, toner,
sticky pages, etc). The user wants to see which individual sheets of a
report have been successfully printed and which ones may need to be
rerun.
He/she will check off the successful prints in a special form

(DT_check)
and

resubmit the remainder. Note that Quickbooks offers a similar feature. In
addition, report sheets that are marked as printed will not show up in
subsequent report generations as there will be no need; in other words, each
report generation needs to be new and not contain ANY prior printed sheets.

I've tried different approaches, but tackling this from the report itself
makes the most sense. For the life of me I don't understand why the
recordset udnerlying the report cannot be accessed. Any idea?

Regardless, looks like it's back to the drawing board...

Randall Arnold

No, the approach you suggest will not work properly in Access.

You will not be able to loop through the records like that. You could
handle
one record at a time (in Detail_Print), but - as your MsgBox

implies -
you

do not know if all records came out okay at that stage. Neither can you
just
read the RecordSource property of the report and update the Printed field,
since the report may have been filtered and there is a bug in Access where
it does not reliably report the FilterOn property for reports.

The best solution might be to keep a log of which records were printed
when.
It is then very easy to get a list of those that have not been printed.
The
interface is a small unbound form where the user can select the

records
to

print. You write the primary key value of these records to a table, along
with a batch number for this print run. The report's query then reads the
records that match this batch. At the end, if things did not print
correctly
(or if the user wants to have another go at the batch), you can

delete
the

log for this batch, and let them have another go. Ultimately, you have
a
record of not only *if* the record was supposed to have been printed
sometime, but *when* the record was printed, and that is generally very
useful.

Hope that heips.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to set a table status flag after a report has run. The field
is
Printed with a boolean of True or False. I set a flag in the
Detail_Print
sub (fPrinted = true) then expect the following code to do the table
update:

----------------------------------------------------
Private Sub Report_Close()
Dim iPrintOK As Integer
If fPrinted Then
iPrintOK = MsgBox("Did all prints come out okay?", vbYesNo, "Print
Confirmation")
If iPrintOK = vbYes Then
Me.Recordset.MoveFirst
While Not Me.Recordset.EOF
Me.Recordset![Printed] = True
Me.Recordset.MoveNext
Wend
Me.Recordset.Update
Else
DoCmd.OpenForm "DT_check"
End If
End If
End Sub
---------------------------------------------------

When the VB interpreter hits the Me.Recordset.Movefirst code, I get an
error
stating "this functionality is not available in an MDB". ??? I don't
get
that. Does this mean there's no way to accomplish my goal? Or does
anyone
else know of a way to do this?

TIA,

Randall Arnold