Print button criteria

[AHopper]

On a form "UniqueLabelReportDialogBox" I have unbound
combo boxes that I want to use for criteria when printing
a report. The user creates the range by selecting from the
combo box list in two combo boxes "OneStartUnique"
and "EndUniqueLabel".
The problem I have is with printing a range between
[OneStartUnique] and [EndUniqueLabel]. The data table only
has a field called "UniqueLabel".

I have tried the following code in the On Click event of a
commmand button on the "UniqueLabelReportDialogBox" form

Dim stDocName As String
stDocName = "OnePackagedUniqueLabelReport"
DoCmd.OpenReport stDocName, acNormal, , "[UsedWithJob]=
[Forms]![UniqueLabelReportDialogBox]![JobNumber]And
[UniqueLabel]>=[Forms]![UniqueLabelReportDialogBox]!
[OneStartUnique]And [UniqueLabel]<=[Forms]!
[UniqueLabelReportDialogBox]![EndUniqueLabel]"

[UsedWithJob]and [UniqueLabel] are both in the source for
the report as seen below:
SELECT DISTINCTROW OneGPackData.JobNumber,
Jobs.ItemNumber, Jobs.ItemName, OneGPackData.UsedWithJob,
OneGPackData.UniqueLabel, Sum(OneGPackData.GoodOnePack) AS
[Sum Of GoodOnePack], OneGPackData.Surplus,
OneGPackData.PartialOnePack
FROM Jobs INNER JOIN OneGPackData ON Jobs.JobNumber =
OneGPackData.JobNumber
GROUP BY OneGPackData.JobNumber, Jobs.ItemNumber,
Jobs.ItemName, OneGPackData.UsedWithJob,
OneGPackData.UniqueLabel, OneGPackData.Surplus,
OneGPackData.PartialOnePack;

Thank you for your help

Allan

 

[Rod Scoullar]

Allan,

Try

DoCmd.OpenReport stDocName, acNormal, , "[UsedWithJob]=" & [JobNumber] & "
And [UniqueLabel] Between " & [OneStartUnique] & " And " & [EndUniqueLabel]

Rod Scoullar

 

[AHopper]

Rod, I get following message:
"Object doesn't support this property or method."

Thank you for your help.

Allan

 

[Rod Scoullar]

Allan,

Put a breakpoint in at the line DoCmd ... , copy the form criteria to the
immediate pane of the debug window and print the value of the criteria.
This will let you know if the filter clause is sending the correct values.

You should see something like when you print the criteria

[UsedWithJob]=1234 And [UniqueLabel] Between 500 And 600.

If UniqueLabel is a text data type you will have to use single quotation
marks around the between criteria, ie '500' And '600'.

If the criteria statement is OK then step through the code within the
report.

I can't see any object on the DoCmd line that would give that error.

Rod

 

[AHopper]

Rob
This is a new challenge for me. I have placed the break
and copied the following code to the immediate window.

DoCmd.OpenReport stDocName, acNormal, , "[UsedWithJob]=" &
[JobNumber] & "And [UniqueLabel] Between " &
[OneStartUnique] & "And " & [OneEndUnique]

I do not know how to "print the value of the criteria". I
am not certain it will have a value when I have the form
in design view.

I receive the following message when I press enter in the
immediate window.
"External name not defined"

When I close out of design view and try to print from the
form. I receive the message "Object doesn't support this
property or method".

When I place the criteria directly in the source for the
report, the report prints as I would like it to.

Thanks
Allan

 

[Rod Scoullar]

Allan,

Glad you worked it out.

Printing values in the immediate pane is really useful.

In this example you would halt the program at the breakpoint as you did.
The halted line shows in yellow.

Copy the code whose values you want to print, in this case the
"[UsedWithJob]=" & [JobNumber] & " And [UniqueLabel] Between " &
[OneStartUnique] & " And " & [EndUniqueLabel]

Move to the immediate pane.

Enter a ? (this is the abbreviation for Print) and paste the copied code; ie

?"[UsedWithJob]=" & [JobNumber] & " And [UniqueLabel] Between " &
[OneStartUnique] & " And " & [EndUniqueLabel]

Press Enter

This will show the values as passed to the form. It is really good for
debugging SQL statements.


Rod.