macro - repeat - gotorecord - next

  • Thread starter Thread starter carolm
  • Start date Start date
C

carolm

By the way Steve Schapel, all your posts so far have gotten me at least this
far!! thank you! But...

I've created two macros -- one opens a form containing data for a report
based on a table of data that will be a different number of records each day.
This macro then runs the macro that prints output to a snapshot file one per
record with a unique file name based on on a patient number. I get all the
ouptuted records without a problem but when I get to the end of the list it
advances to the null record and returns an error - "you may be at the end of
the dataset." and halts the process.

I've tried:
* hard code 12 for the repeat - get the error
* place a unbound text box on the form and do a setvalue and then decrement
it by 1 each time through, then used a repeat expression checking to see if
the value is 0 - get the error.
* used a repeat expression to evaluate if the field is now null - i get the
error.

The problem seems to be that the form is advancing to the next record before
I can evaluate if I have cycled through all the records so it always advances
past the last record and causing the popup.

I'm using MSAccess2000. I plan to make this whole process called from a
task on the windows scheduler so it will run unattended on a dialy basis.
So close and yet so far! HELP please!
 
Carol,

* place a unbound text box on the form and do a setvalue and then
decrement
it by 1 each time through, then used a repeat expression checking to see
if
the value is 0 - get the error.
Click to expand...

I would expect this one to work. Can you give details of exactly the
expressions and actions you used, and I'll see if I can spot why it's not
working.
 
the repeat expression in the first macro:
[Forms]![frm_PINN_PTSWCharting]![Count]>0

the set value items in the second macro. I have this after GoToRecord/Next:
item: [Forms]![frm_PINN_PTSWCharting]![Count]
expression: [Forms]![frm_PINN_PTSWCharting]![Count]-1

On the form I do see the count decrementing down to 12.

As I said I get the eror even if I hard code 12 in the repeat count. The
form is reading directly from a table.

Thanks for the help.
 
I'm sorry, Carol, but solving this problem is an "attention to detail"
reequirement, and so far I haven't got enough detail.

I need you to give me a full and exact listing of all the actions in the
macro, with all relevant arguments. Otherwise I'm just second guessing.

By the way, what's the significance of 12?

Thanks.
 
Okay understood: 12 was the inital set of records. I had used it as a repeat
factor instead of the Repeat Expression and I still got the error.

here is more detail:
Form: frm_PINN_PTSWCharting
§ Record Source: tbl_PINN_PTS_AntibioticReport
§ Default View: Continuous Forms
§ Unbound field with default value:
=DCount("*","tbl_PINN_PTS_AntibioticReport")

Macro A: Mcr_PrintpatientCall

OpenForm:
§ frm_PINN_PTSWCharting
o view: From
o DataMode: Edit
o Window Mode: Normal
RunMacro:
§ mcr_PrintPTAntibioticReport
o Repeat Expression: [Forms]![frm_PINN_PTSWCharting]![Count]>0

Macro B: mcr_PrintPTAntibioticReport

SetWarnings – No
§ OutPutTo:
o Object type: Report
o Object Name: PINN_AntibioticCharting by PT#
o Output Format: Snapshot Format
o Output File: ="C:\Documents and Settings\cmuirhea\My Documents\" &
[Forms]![frm_PINN_PTSWCharting]![PAT_NUM] & ".SNP"
o Auto Start: No
§ GotToRecord:
o Object Type: Form
o Object Name: frm_PINN_PTSWCharting
o Record: Next
§ SetValue:
o Item: [Forms]![frm_PINN_PTSWCharting]![Count]
o Expression: [Forms]![frm_PINN_PTSWCharting]![Count]-1
 
Carol,

Ok, thanks for the explanation, and I can now see what you are doing.

Thus by the time the value of the [Count] textbox gets to 1, we are at
record 12, and then, as per the macro, there is the Output of the report,
and the the GoToRecord attempting to move to the 13th record, which doesn't
exist.

The only solution I can quickly think of off-hand is to put a copy of the
OutputTo action into the MacroA, before the RunMacro action.

Then, in MacroB, reverse the order of the GoToRecord and OutputTo actions.

And change the Repeat Expression to >1 instead of >0

So the first record will be printed. Then the MacroB will take over to
print the 11 records 2-12.

Make sense?
 
That was the ticket to success. Many thanks! If I ever get to NZ I owe you
a beer.

Steve Schapel said:
Carol,

Ok, thanks for the explanation, and I can now see what you are doing.

Thus by the time the value of the [Count] textbox gets to 1, we are at
record 12, and then, as per the macro, there is the Output of the report,
and the the GoToRecord attempting to move to the 13th record, which doesn't
exist.

The only solution I can quickly think of off-hand is to put a copy of the
OutputTo action into the MacroA, before the RunMacro action.

Then, in MacroB, reverse the order of the GoToRecord and OutputTo actions.

And change the Repeat Expression to >1 instead of >0

So the first record will be printed. Then the MacroB will take over to
print the 11 records 2-12.

Make sense?

--

Steve Schapel, Microsoft Access MVP


carolm said:
Okay understood: 12 was the inital set of records. I had used it as a
repeat
factor instead of the Repeat Expression and I still got the error.

here is more detail:
Form: frm_PINN_PTSWCharting
§ Record Source: tbl_PINN_PTS_AntibioticReport
§ Default View: Continuous Forms
§ Unbound field with default value:
=DCount("*","tbl_PINN_PTS_AntibioticReport")

Macro A: Mcr_PrintpatientCall

OpenForm:
§ frm_PINN_PTSWCharting
o view: From
o DataMode: Edit
o Window Mode: Normal
RunMacro:
§ mcr_PrintPTAntibioticReport
o Repeat Expression: [Forms]![frm_PINN_PTSWCharting]![Count]>0

Macro B: mcr_PrintPTAntibioticReport

SetWarnings – No
§ OutPutTo:
o Object type: Report
o Object Name: PINN_AntibioticCharting by PT#
o Output Format: Snapshot Format
o Output File: ="C:\Documents and Settings\cmuirhea\My Documents\" &
[Forms]![frm_PINN_PTSWCharting]![PAT_NUM] & ".SNP"
o Auto Start: No
§ GotToRecord:
o Object Type: Form
o Object Name: frm_PINN_PTSWCharting
o Record: Next
§ SetValue:
o Item: [Forms]![frm_PINN_PTSWCharting]![Count]
o Expression: [Forms]![frm_PINN_PTSWCharting]![Count]-1
Click to expand...
Click to expand...
 
Back
Top