Report Problems

[DissentChick]

Hi there, sorry to bug you again-
I'm trying to do the old "print the current record only" trick and it's not
working. Before anyone gets mad, I did search the posts and tried 5 or 6 of
the suggestions, but they all result in a box that comes up and asks for my
ID value before it will display the record I want. My report does contain a
subreport, so I'm assuming that whatever I do to the main report I will have
to do to the subreport as well. Is this a matter of using code, or writing a
macro to pull the current record in the form up automatically from the
command button? I've even tried looking for the code that does this from the
Northwind database. Please help!
Thanks a bunch-

 

[Ken Snell [MVP]]

Not knowing any details, let me give you some generic info.

When you open the report using the DoCmd.OpenReport action, the fourth
argument can be used to pass the filter string to the report:
"PrimaryKeyField=" & Me.IDFieldInForm.Value

In the report, be sure that the subreport is linked to the main report via
the subform control's LinkMasterFields and LinkChildFields properties; that
way, the subform will be "filtered" according to what the main form has.

If this isn't what you need to use, then you'll need to give us a lot more
details about what you're doing, your form's and report's and subreport's
setups, etc.

 

[DissentChick]

Hi there again, thank you for your help! I fiddled with it for a great while
and managed to get it to bring up the current record using the code below,
but I'm still getting the parameter box popping up when I press the command
button. I am also posting the SQL to the query "WOLog Filter" To see if
that's where my problem is. (BTW, it doesn't matter what -if anything- I type
in for the parameter- the current record still comes up) Any thoughts would
be very helpful, I'm just trying to get rid of that box!
Aaron
Private Sub PreviewWorkOrder_Click()
On Error GoTo Err_PreviewWorkOrder_Click

Dim stDocName As String

stDocName = "WorkOrderLog"
'View Work Order Log Report, using WOLog Filter to show correct record.
DoCmd.OpenReport stDocName, acPreview, "WOLog Filter"

Exit_PreviewWorkOrder_Click:
Exit Sub

Err_PreviewWorkOrder_Click:
MsgBox Err.DESCRIPTION
Resume Exit_PreviewWorkOrder_Click

End Sub
And the SQL:
SELECT [Work Order Log].*, [Work Order Log].[WorkOrder#]
FROM [Work Order Log]
WHERE ((([Work Order Log].[WorkOrder#])=[Forms]![WOLog].[WorkOrder#]));

 

[Ken Snell [MVP]]

You're not using the WhereCondition argument, you're using the FilterName
argument. Change your DoCmd.OpenReport statement to this (assumes that
WorkOrder# is a numeric field):

DoCmd.OpenReport stDocName, acPreview, , "[WorkOrder#]=" & Me.[WorkOrder#]

If it's a text field:

DoCmd.OpenReport stDocName, acPreview, , "[WorkOrder#]='" & Me.[WorkOrder#]
& "'"

 

[DissentChick]

Ken,
I did as you suggested and it is working- I tried it on another (identical)
report I had and it works perfectly- no boxes, just the correct record coming
up in the report. On the one I was originally working on, however, I am still
getting the parameter box. I have made sure that all of the settings are
exactly the same as the other, even deleted and remade the query the report
is based from-and still the box. I'm not sure if I should try to remake the
report just yet, but if you have any suggestions that's awesome. I'm sure
this topic is getting exhausting to you, so respond at your leisure, but
thank you very much for all your help.
Aaron

 

[Ken Snell [MVP]]

The problem is in the query that you're using for the report, most likely. I
noted in the one that you posted before that you have the WorkOrder# field
in the query twice:

SELECT [Work Order Log].*, [Work Order Log].[WorkOrder#]
FROM [Work Order Log]
WHERE ((([Work Order Log].[WorkOrder#])=[Forms]![WOLog].[WorkOrder#]));

It comes in with the * syntax, and then you also repeat the field
separately.

Post the SQL of the report's query.

 

[DissentChick]

Ken, here is the SQL for the query I'm using for it currently- I had to make
some changes to get some fields I needed on the report. I also removed the
additional WorkOrder# field and am still getting the box, but it still is
bringing up the correct record- Here's the SQL:

SELECT [Work Order Log].*, CombEqList1.Key, CombEqList1.NewID
FROM CombEqList1 INNER JOIN [Work Order Log] ON CombEqList1.Key = [Work
Order Log].NewID;

Thanks again-
Aaron

 

[Ken Snell [MVP]]

What are the actual field names in the Work Order Log table?

 

[DissentChick]

Ken,
They are:
WorkOrder#
Status
NewID
Equip#
Location
ProblemDescription
StartDate
DateCompleted
Delays/Holds
ReportedBy
MaterialsUsed
Notes
Hope this helps. Also, I'm aware that a better name scheme could be used,
I'm working on that. I know that in the query the NewID field is pulled from
a different table. This is for a different reason than what's going on with
this query.Thanks again.

 

[Ken Snell [MVP]]

Do you have a control on the report that is bound to the WorkOrder# field?

Post the exact VBA step that you're now using to open the report, and post
exactly what the parameter box that is popping up says.

 

[DissentChick]

Yes, I do have a control on the form bound to the Work Order# field. It works
fine on both the report and the form.The parameter box says only Work Order
Log. That's exactly like it is, no quotation marks or anything. Here is the
VBA:

Private Sub PreviewWorkOrder_Click()
On Error GoTo Err_PreviewWorkOrder_Click

Dim stDocName As String

stDocName = "WorkOrderLog"
'View Work Order Log Report, using WOLog Filter to show correct record.
DoCmd.OpenReport stDocName, acPreview, , "[WorkOrder#]=" & Me.[WorkOrder#]

Exit_PreviewWorkOrder_Click:
Exit Sub

Err_PreviewWorkOrder_Click:
MsgBox Err.DESCRIPTION
Resume Exit_PreviewWorkOrder_Click

End Sub

So it's not just me that thinks this is really weird?

"

 

[Ken Snell [MVP]]

Ahhhhh..... open the report in design view, click View | Sorting and
Grouping. Do you see Work Order Log as a field in the list there? Delete it
if yes.

 

[DissentChick]

Ok, i did that and now it says "You must define a sort field or expression
for the group header or footer in the report you tried to preview or print."
Wow, this is just getting silly-you're such a trooper, thanks again.

 

[Ken Snell [MVP]]

Sounds as if you deleted the name "Work Order Log" from the cell in the
Sorting & Grouping window. Instead, select the entire row and delete the
row. You need to delete the group header and / or footer sections from the
report that were originally being used for the Work Order Log field.

 

[DissentChick]

Ken, I deleted the entire row this time, and I'm still getting the box. I
made a new report, based on a new query, using the information from the Work
Order Log table, and its doing the same thing. (And it has no sorting or
grouping levels) I wonder if it's a property in the WorkOrder# field? If so,
what could it be? I think we've covered everything else. If this doesn't
work, I'll just succumb to the stupid box and stop pestering you. Thanks
again.

 

[Ken Snell [MVP]]

One last place to look.

Open the query itself in design view. Click Query | Parameters. Delete any
entries there by deleting the row.

 

[Ken Snell [MVP]]

Oh, one other place to look in the report:

Check all textboxes that have calculation expressions as the Control Source
string. See if Work Order Log is in any of those expressions as a "field"
name.

 

[DissentChick]

*sigh* There was nothing under Parameters, and nothing under any of the
fields' Control Sources. I checked all of them. I appreciate your help so
much. I'll let you know if I ever figure it out, if I last that long after
dealing with that stupid box. Have a great day- feel free to post me back if
you have any more thoughts.
Gratefully, Aaron

 

[Ken Snell [MVP]]

Is there a subreport in the report? If yes, check that subreport control
(the control that holds the subreport object) and see if Work Order Log is
listed in the LinkMasterFields or the LinkChildFields property.

Also, check the subreport's Sorting and Grouping, textbox control sources,
etc. just as you did for the main form.

 

[DissentChick]

Ken, sorry for the delay in reply- The master field in the subreport was our
culprit- Thanks so much for all of your help! Keep up the good (patient) work!
Aaron Regular