Confused by parameter error dialog

[akirekab]

I am running a report in Access. When I click to run report, a dialog comes up for missing parameter, showing just the name of a table in the box.

Now if I just click ok without typing anything in, the report dispays perfectly.

But user wants it gone, and I am at a loss. Here is click Event code:

 Dim stDocName As String
 
    Dim strFilter As String
 
    stDocName = "rptPatientProfilebyInterviewID"
 
    DoCmd.OpenReport stDocName, acPreview
 

and here is SQL generated by qry builder:

SELECT tblPatientProfile.*, tblInterviewRecord.*
FROM tblPatientProfile INNER JOIN tblInterviewRecord ON tblPatientProfile.PatientProfileID = tblInterviewRecord.PatientProfileID
WHERE (((tblInterviewRecord.InterviewID)=[Forms]![frmPatientProfile]![frmInterviewRecord subform]![InterviewID]));
 

parameter dialog, comes up and shows tblPatientProfile

I can't figure out why this is popping up
Ken

[NeoPa]

There's nothing in here I can see which would cause that Ken.

Can you copy the exact message from the error message box. If this can't throw any light we may have to look at using the Access Documentor function and searching through the results.

[akirekab]

The dialog, box, has nothing in it except the table name
'tblPatientProfile'

[NeoPa]

So the title is Missing Parameter and the contents, pasted in exactly as requested, is 'tblPatientProfile' (including the quotes)?

This is leading to uncharted territory then, and we will need to use the Access Documenter feature to discover what is referring to this.

[akirekab]

Actually it is titled
Enter Parameter Value

Then above the input of dialog box it says tblPatientProfile
with no quotes

it has the normal ok button, and when I click it, without entering anything, the report prints or previews normally and correctly.

Thanks
Ken

[NeoPa]

Ah. That's starting to make some sense.

Please try to run the SQL you posted, natively (as a query rather than as a data source for the report) and describe what you get.

[akirekab]

Ok I ran the query,

SELECT tblPatientProfile.*, tblInterviewRecord.*
FROM tblPatientProfile INNER JOIN tblInterviewRecord ON tblPatientProfile.PatientProfileID = tblInterviewRecord.PatientProfileID
WHERE (((tblInterviewRecord.InterviewID[b])=[Forms]![frmPatientProfile]![frmInterviewRecord subform]![InterviewID]));

Got dialog asking for the

[Forms]![frmPatientProfile]![frmInterviewRecord subform]![InterviewID]));

which I entered and got the correct reply.
A combination of fields from the two tables.

It normally gets that parameter from the form which originates the report request.

[akirekab]

Sorry had typo in the SQL in frame above

SELECT tblPatientProfile.*, tblInterviewRecord.*
FROM tblPatientProfile INNER JOIN tblInterviewRecord ON tblPatientProfile.PatientProfileID = tblInterviewRecord.PatientProfileID
WHERE (((tblInterviewRecord.InterviewID)=[Forms]![frmPatientProfile]![frmInterviewRecord subform]![InterviewID]));
 

[NeoPa]

Ken, that's clearly expressed and I now know that the problem is not in the SQL itself.

That makes life harder of course, so we now need to look at the Documenter facility. It's a neat trick anyway so may be of more general interest.

From your main database window select Tools / Analyse / Documenter.
Makes sure at this stage to include everything you can. Kitchen sink optional.

When you have created the document save it and then open it in Word (convert from RTF). Now search through the document for instances of the string (tblPatientProfile). This should find some less visible references which would take a deal of searching for (and digging around) in the main Access application.

You may find more items than you need and will need to ignore those elements which would not cause your problem.

Good luck, and let us know how you get on.

[akirekab]

NeoPa
I have looked over documenter, now should I be just checking the table, report and query I am worrying about or really check everything??

Ken

[NeoPa]

For thoroughness, I would advise doing the whole lot. Also, it's hard to be more precise without the details in front of me.

Alternatively, you could try just the report itself and any related items (queries etc) first with a view to doing the whole lot later if nothing is uncovered. It's up to you.

When you have the (possibly enormous) document open in MS Word, simply do a search (Ctrl-F) for (tblPatientProfile) and examine all the occurrences found. You should find something that sets off alarm bells.

Let me know how you get on.