report parameter

[Guest]

I've scanned previous postings, and found a related issue "Bugs" from Oct 04.
I have a report that keeps prompting for "orders" parameter when viewing. I
do not have any field / control on the report called "Orders", it is not in
the sorting / grouping list, Filters are not on in the Report Properties. I
have turned off name auto correct, and compacted the database - it's still
asking for the parameter?!?! any other ideas please? Running Access 2003 on
XP Pro. Thank you

 

[Allen Browne]

Hi Sue

These can be hard to track down. Suggestions:

1. If the report's recordsource is a query, try opening the query directly
to see if it asks for the parameter.

2. If the report is being opened through code, could the Orders be named in
the WhereCondition of OpenReport?

3. Open the report in design view. Open the Properties of the report. Delete
anything in the OrderBy property (as well as Filter).

4. On the report design toolbar, there is a drop-down list of control names.
See if you can find Orders there.

5. Even if there is no control with that name, it could still be in the
ControlSource of another.

You already checked the Sorting'n'Grouping dialog.

If you are still stuck, run the code below to find the critter. Make sure
the report is open in design view and no others are open. Then in the
Immediate Window (Ctrl+G):
? FindText("orders")

------------------------code starts----------------
Function FindText(strFind As String)
Dim rpt As Report
Dim ctl As Control

Set rpt = Reports(0)
For Each ctl In rpt.Controls
If ctl.Name Like "*" & strFind & "*" Then
Debug.Print ctl.Name & " is a " &
ControlTypeName(ctl.ControlType)
End If
If HasProperty(ctl, "ControlSource") Then
If ctl.ControlSource Like "*" & strFind & "*" Then
Debug.Print ctl.Name & " is bound to " & ctl.ControlSource
End If
End If
Next
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
Function ControlTypeName(n As Long) As String
'Purpose: Return the name of the ControlType.
'Note: The ControlType returns a Byte, but the constants are Long.
Dim strReturn As String

Select Case n
Case acBoundObjectFrame: strReturn = "Bound Object Frame"
Case acCheckBox: strReturn = "Check Box"
Case acComboBox: strReturn = "Combo Box"
Case acCommandButton: strReturn = "Command Button"
Case acCustomControl: strReturn = "Custom Control"
Case acImage: strReturn = "Image"
Case acLabel: strReturn = "Label"
Case acLine: strReturn = "Line"
Case acListBox: strReturn = "List Box"
Case acObjectFrame: strReturn = "Object Frame"
Case acOptionButton: strReturn = "Object Button"
Case acOptionGroup: strReturn = "Option Group"
Case acPage: strReturn = "Page (of Tab)"
Case acPageBreak: strReturn = "Page Break"
Case acRectangle: strReturn = "Rectangle"
Case acSubform: strReturn = "Subform/Subrport"
Case acTabCtl: strReturn = "Tab Control"
Case acTextBox: strReturn = "Text Box"
Case acToggleButton: strReturn = "Toggle Button"
Case Else: strReturn = "Unknown: type" & n
End Select

ControlTypeName = strReturn
End Function
------------------------code ends----------------

 

[Guest]

Hi Allen,

I was hoping you'd be on line! I've narrowed it down to a sub-report (when
I delete the sub-report, I don't get the parameter prompt, when it's in the
main report, I do). I've checked the link master & child fiels, and they're
OK.
I've run each of the queries that are used in the report & sub-reports and
they're fine.
I'll run the code that you sent on both the parent & sub-reports? and let
you know. Thanks for your time.

 

[Guest]

Hi Allen,

Forgive me, I do very little (read nothing!) with VB. I copied the text
below into the immediate window from the Report in Design view. Hit Enter?
If I do, nothing seems to happen, or else I get an error like:
Compile Error: Sub or Function not defined

Do I need to format the text below in some fashion (delete spaces, lines
etc.?)

Thanks for your patience with dummies like me!

 

[Allen Browne]

1. Choose the Modules tab of the database window.

2. Click New. Access opens a new module.

3. Paste the code in there.

4. Check that Access understands it by choosing Compile from the Debug menu.
There may be some word wrap issues, e.g. this is one line:
Debug.Print ctl.Name & " is a " &
ControlTypeName(ctl.ControlType)

Then enter the one line into the Immediate Window.

Yes. If there are subreports, you will need to check the sorting'n'grouping,
filter, orderby, etc of each one, and you can open each in turn (one at a
time) and repeat the code from the immediate window.

 

[Guest]

Hi Allen,

Thanks again. No luck though. Probably should have mentioned that Orders
is the name of a table which is used in the query for the main report. The
only controls found with your code were all valid Orders.fields (eg.
Orders.FullName). These were in the main report. There are two subreports -
the Master link field for each subreport is the same, the Child fields are
different (as the underlying queries for each of the subreports are
different).

When I run your code for the subreports, I get nothing, I'm assuming this
means that there are no Orders references in these reports?

So, in summary: there are no control sources or controls typed in as
"Orders", none of the report properties have Filter or Group by on, none of
the reports Sorting & Grouping lists includes anything named "Orders", none
of the queries underlying the reports request this parameter when they are
run directly, I have no other VBA code for these reports (or even the entire
..mdb).
The parameter dialog only arises when I link one or other or both of the
sub-reports in to the main report (if I drag the report in, and don't set the
Link Child & Link Master Data Properties for the sub-report, I don't get the
parameter dialog box, but of course, neither do I get the right data).
Driving me nuts, of course it will be something stupid! Thanks again for your
time.

 

[Allen Browne]

Okay, that's good trouble-shooting. You have pinned it down the
LinkMasterFields/LinkChildFields.

Does either the main report's or the subreport's query return two fields
with the same name? For example, if the query outputs Order.OrderID and also
OrderDetail.OrderID, and OrderID is named in the LinkMasterFields, then
Access has to try to figure out which one is meant and adds the "Order." to
distinguish them. Avoiding this scenario might help.

You did say that the Name AutoCorrect options were off, because it is also
very likely to cause this kind of issue.

Focus on tracing the fields referred to in the
LinkMasterFields/LinkChildFields.

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

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

[snip]

 

[Guest]

Hi Allen,

Thanks again for your time. Yes, I have two fields in my "master" report
query, both the source field names are mxSearchKey, one table is
Opportunities, the other is Orders. Unfortunately, I'm a little hamstrung
with the naming, as the tables are "extracted" and sync'd from Outlook, so I
can't change the names. I'll play around with changing the report layout,
perhaps change some of my group headers to sub-reports, with the underlying
queries also changed so that I'm not combining fields from the Opportunities
and Orders tables in the same query.

Okay, that's good trouble-shooting. You have pinned it down the
LinkMasterFields/LinkChildFields.

Does either the main report's or the subreport's query return two fields
with the same name? For example, if the query outputs Order.OrderID and also
OrderDetail.OrderID, and OrderID is named in the LinkMasterFields, then
Access has to try to figure out which one is meant and adds the "Order." to
distinguish them. Avoiding this scenario might help.

You did say that the Name AutoCorrect options were off, because it is also
very likely to cause this kind of issue.

Focus on tracing the fields referred to in the
LinkMasterFields/LinkChildFields.

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

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

[snip]

The parameter dialog only arises when I link one or other or both of the
sub-reports in to the main report (if I drag the report in, and don't set
the
Link Child & Link Master Data Properties for the sub-report, I don't get
the
parameter dialog box, but of course, neither do I get the right data).
Driving me nuts, of course it will be something stupid! Thanks again for
your
time.

 

[Allen Browne]

Can you alias one of the fields in the query, e.g.:
SELECT Opportunities.mxSearchKey AS OppMxSearchKey,
Opportunities.Orders, ...

You might work around the problem by specifying table.field in the Link
Fields properties, but the alias would be my preference.

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

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

Hi Allen,

Thanks again for your time. Yes, I have two fields in my "master" report
query, both the source field names are mxSearchKey, one table is
Opportunities, the other is Orders. Unfortunately, I'm a little hamstrung
with the naming, as the tables are "extracted" and sync'd from Outlook, so
I
can't change the names. I'll play around with changing the report layout,
perhaps change some of my group headers to sub-reports, with the
underlying
queries also changed so that I'm not combining fields from the
Opportunities
and Orders tables in the same query.

Okay, that's good trouble-shooting. You have pinned it down the
LinkMasterFields/LinkChildFields.

Does either the main report's or the subreport's query return two fields
with the same name? For example, if the query outputs Order.OrderID and
also
OrderDetail.OrderID, and OrderID is named in the LinkMasterFields, then
Access has to try to figure out which one is meant and adds the "Order."
to
distinguish them. Avoiding this scenario might help.

You did say that the Name AutoCorrect options were off, because it is
also
very likely to cause this kind of issue.

Focus on tracing the fields referred to in the
LinkMasterFields/LinkChildFields.

[snip]

The parameter dialog only arises when I link one or other or both of
the
sub-reports in to the main report (if I drag the report in, and don't
set
the
Link Child & Link Master Data Properties for the sub-report, I don't
get
the
parameter dialog box, but of course, neither do I get the right data).
Driving me nuts, of course it will be something stupid! Thanks again
for
your
time.

 

[Guest]

Hi Allen,

Thanks for your input - I had actually changed my queries & reports so that
Opportunities is in the main report, Orders information is now in another
sub-report, and the original 2 sub-reports are nested in the Orders
sub-report. It works, but I'll certainly take note of your suggestion for
the future. Thank you again for putting up with weekend warriors like me.

Can you alias one of the fields in the query, e.g.:
SELECT Opportunities.mxSearchKey AS OppMxSearchKey,
Opportunities.Orders, ...

You might work around the problem by specifying table.field in the Link
Fields properties, but the alias would be my preference.

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

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

Hi Allen,

Thanks again for your time. Yes, I have two fields in my "master" report
query, both the source field names are mxSearchKey, one table is
Opportunities, the other is Orders. Unfortunately, I'm a little hamstrung
with the naming, as the tables are "extracted" and sync'd from Outlook, so
I
can't change the names. I'll play around with changing the report layout,
perhaps change some of my group headers to sub-reports, with the
underlying
queries also changed so that I'm not combining fields from the
Opportunities
and Orders tables in the same query.

Okay, that's good trouble-shooting. You have pinned it down the
LinkMasterFields/LinkChildFields.

Does either the main report's or the subreport's query return two fields
with the same name? For example, if the query outputs Order.OrderID and
also
OrderDetail.OrderID, and OrderID is named in the LinkMasterFields, then
Access has to try to figure out which one is meant and adds the "Order."
to
distinguish them. Avoiding this scenario might help.

You did say that the Name AutoCorrect options were off, because it is
also
very likely to cause this kind of issue.

Focus on tracing the fields referred to in the
LinkMasterFields/LinkChildFields.

[snip]
The parameter dialog only arises when I link one or other or both of
the
sub-reports in to the main report (if I drag the report in, and don't
set
the
Link Child & Link Master Data Properties for the sub-report, I don't
get
the
parameter dialog box, but of course, neither do I get the right data).
Driving me nuts, of course it will be something stupid! Thanks again
for
your
time.