why am i getting these parameter prompts 1

[uscitizen]

i have a form with a 'Print' command button which launches a print job from the 'On click' event property.

the code to be found for that [Event Procedure] is:

Private Sub Command68_Click()
On Error GoTo Err_Command68_Click

Dim stDocName As String

stDocName = "Treatment and Toxicity"

Me.Refresh

DoCmd.OpenReport stDocName, acNormal, "", "[Patient Number] = " & Me![Patient Number] & " And [Current Cycle Number] = " & Me![Current Cycle Number]

Exit_Command68_Click:
Exit Sub

Err_Command68_Click:
MsgBox Err.Description
Resume Exit_Command68_Click

End Sub

now, when i press the Print command button, i am shown three enter parameter prompts in this order:

1) "Forms!Trtmnts_Admnstrd!Patient Number"
2) "Forms!Trtmnts_Admnstrd!CycleNum"
3) "Forms!Trtmnts_Admnstrd!Medication"

this doesn't make sense. anybody out there know how to make sense out of it willing to share?

 

[CajunCenturion]

It would seem that the report needs three values which it is expecting to get from the Trtmnts_Admnstrd form.

First question is if the Trtmnts_Admnstrd form is loaded when you run the report, and if the three controls referenced exist on that form.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[uscitizen]

hi,

a) there is a Trtmnts_Admnstrd form but it isn't related to
the form (called "Treatment and Toxicity&quot having the Print command button which actuates the report

and

b)yes the tree parameters that are being requested do exist on that "Trtmnts_Admnstrd" form, but that form exists solely to acquire information to determine which combination of identifying parameters need to get passed to a report which is launched from a Print command button on it. the "Trtmnts_Admnstrd" Print command button launches a macro which has just an 'Open Report' action on it, the report being called: "Treatment Administered"

this is kind of **&$#!!!-ed up.

 

[dhookom]

uscitizen,
Do you get these prompts if you attempt to open the datasheet view of the report's record source? Also, you can remove the "" in your DoCmd.OpenReport code line.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

hi,

well, i removed the twin quotations and have identified the source of the report as a table having the same name as the report ("Treatment and Toxicity&quot so i guess that rules out viewing it in datasheet view (but apologies for any unintentional naivete since i'm a newbie). as for removing the quotations, that only causes more repeated prompting for the parameters.

 

[CajunCenturion]

I understand that the form Trtmnts_Admnstrd is not related to the form which contains the Print button, but I suspect that the Report that you're trying to generate is related to that Trtmnts_Admnstrd form.

What is the RecordSource property of the Report that you're trying to generate?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dhookom]

uscitizen,
Having objects with the same names causes confusion. Since you are a admitted newbie, you should consider finding and using a naming convention ASAP. Check Tony Toews page

http://www.granite.ab.ca/access/tablefieldnaming.htm

for some help. Unlike Tony, I prefix all my table names with "tbl" or similar.

Can we assume now that since the report has a table as its record source, the prompts must be generated from the report. What happens if you open the report from the database window?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

cajun centurion: the Record Source property of the report is 'Treatment and Toxicity'.

dhookum: in reponse to the question wrt what happens when i open the report from the database window...i get the same parameter prompts.

 

[uscitizen]

i did some snooping around and wanted to add the following facts to the 'case':

there is an SQL statement used as the Record Source for another report called "Treatment Administered" that reads:

SELECT [Treatment and Toxicity].[Patient Number], [Treatment and Toxicity].[Current Cycle Number], [Treatment and Toxicity].[Modifications to cycle], [Treatment and Toxicity].[Last date protocol Tx given], [Treatment and Toxicity].[Reason Tx ended], [Treatment and Toxicity].Comments
FROM [Treatment and Toxicity]
WHERE ((([Treatment and Toxicity].[Patient Number])=[Forms]![Trtmnts_Admnstrd]![Patient Number]) AND (([Treatment and Toxicity].[Current Cycle Number])=[Forms]![Trtmnts_Admnstrd]![CycleNum]));

i note that it is using "Treatment and Toxicity" (the same table which the currently troublesome report is using) and that the criteria mirror the parameter prompts. this same report having the SELECT statement is the one which is launched when the user presses a Print command button appearing of the "Trtmnts_Admnstrd" form.

i suspect this is probably quite relevant.....

 

[dhookom]

Is the report "Treatment Administered" used as a subreport on your "Treatment and Toxicity" report? What happens if you have the form "Trtmnts_Admnstrd" open when you attempt to open "Treatment and Toxicity"?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

hi dhookum:

the report "Treatment Administered" isn't used as a sub-report anywhere.

on the other hand, we may be getting somewhere in terms of your second question....i.e. when "Trtmnts_Admnstrd" is open (and i minimized it just to make it easier to see what i was doing on the database window) and i open the form "Treatment and Toxicity" and click the Print command button on this form (which launches the "Treatment and Toxicity" report), the "Treatment and Toxicity" report runs....however, it prints a report for all records irrespective of the fact it's supposed to print a report for JUST the record that the form is pointing to when the button got clicked.

 

[CajunCenturion]

The other report called "Treatment Administered" I don't think is an issue, for among other reasons, that SQL statement wouldn't generate the prompt for 3) "Forms!Trtmnts_Admnstrd!Medication", but it would for the other two prompts as the where clause references that form.

Do you also have a Query call "Treatment and Toxicity".
Also, check the RecordSource for all of the controls on the report and see if any of them reference the form, or if they use a query/macro which references the form. Also, check your sorting and grouping and see if you have form references in there.

Something in the report "Treatment and Toxicity" is referencing, either directly or indirectly, the Trtmnts_Admnstrd form.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[uscitizen]

brilliant! but how do we get ourselves out of the loop.....
i.e., there are two sub-reports on the affected one. one of them is called "Protocol Medications" and i think we've found our "achilees' heel", its RecordSource is:

SELECT [Protocol Medications].[Patient Number], [Protocol Medications].Cycle, [Protocol Medications].Medication, [Protocol Medications].Route, [Protocol Medications].[Dose Unit], [Protocol Medications].Amount, [Protocol Medications].Dose, [Protocol Medications].[Doses Given], [Protocol Medications].[Date begun], [Protocol Medications].[Date ended], [Protocol Medications].[Doses Required], [Protocol Medications].[Dosage Frequency], [Protocol Medications].[Doses Missed]
FROM [Protocol Medications]
WHERE ((([Protocol Medications].[Patient Number])=[Forms]![Trtmnts_Admnstrd]![Patient Number]) AND (([Protocol Medications].Cycle)=[Forms]![Trtmnts_Admnstrd]![CycleNum]) AND (([Protocol Medications].Medication)=[Forms]![Trtmnts_Admnstrd]![Medication]));

which as we see is being used by Trtmnts_Admnstrd. question's, now, 'seems to be, how do we get around that?

 

[CajunCenturion]

I think you have three choices.

1 - Remove the Sub-report form the report.
2 - Load the Trtmnts_Admnstrd form and populate the controls with the proper values.
3 - Change the Where clause to get those values from some other source. But first, what other reports will be affected by any changes made to the "Protocol Medications" sub-report? In other words, is this subreport being included in any other report, and if so, whatever changes made in the Where clause must also work in those other reports. One possibility here is to add three controls to your launching form, load the proper values into those controls, hide them, and change the Where clause to reference the launching from. But again, if this subreport is used elsewhere, this may not solve the problem, simply move the problem to another form.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[uscitizen]

....well, hey now! we may have reassembled humpty dumpty again.

i made a copy of sub-report "Protocol Medications" naming it "Protocol Medications_A" and substituted this "A" version for the first one in the "Treatment and Toxicity" report.

when i opened the "Treatment and Toxicity" form, clicked on ye olde "Print" command button, the effect was anticlimatic: it had the unwanted outcome of printing the report for all records not just the current record.

i replaced those deleted quotation marks from the "Event_Procedure" On Click code behind the Print command, and "voila", she's a working again.

 

[uscitizen]

i don't believe i got around to saying a big THANKS for the interest/help/support/.....