Output fields to report only if conditions met

[RJB]

This is sort of related to a previous post of mine (in case it seems
familiar).

I've got a database for a printing company and I'm making the workorders
digital instead of hand written. The workorder itself is a form with a
subform in it. In the main form is a field called "LogID". In the subform
there is a field called "Log Letter" which separates each individual print
run for a particular LogID. Also in the subform is a field for "Order Type"
which has 5 options, but for example we'll just say the options are "Black"
and "Color". Any given LogID may have a mix of Order Types. 1 Black, 1
Color: 1 Black and 2 Color, vice versa...etc, all depending on what a
client is ordering with that particular job. At the bottom of the main form
are 2 buttons: "Create Black Workorder" and "Create Color Workorder".
These buttons open a report that has all the important information entered
in the form/subform.

What I need to do, is separate the jobs by Order Type. If they click on
"Create Black Workorder", then that report needs to only display/print the
'Log Letter's that have the "Order Type" of Black. If they click "Create
Color Workorder", then the opposite, only display/print those where the
"Order Type" is Color.

Here's an example of what I've got on the form and what I need in the
report - I added some other variables the the examples to make things a
little more clear:
___________________________
FORM:

LogID: 3005
client: JohnDoe Sales

*Subform*
Log Letter: A OrderType: Black

Log Letter: B OrderType: Black

Log Letter: C OrderType: Color
_________________________________

REPORT: Black Workorder

LogID: 3005 Page: 1 of 2
JohnDoe Sales

A Black
B Black
_________________________________

REPORT: Color Workorder

LogID: 3005 Page: 2 of 2
JohnDoe Sales

C Color
_____________________________________

Any ideas on how I can make this happen, or is there a easy / better way
that I'm not thinking of?

Thanks in advance,
Rick
(e-mail address removed)

 

[Guest]

You can use the same report and report recordsource for all your options.
Put code behind the buttons that will create a Where criteria string you can
use for the Where argument of the OpenReport method to filter the report's
record source based on the selections.

 

[RJB]

Dave,

Thanks for the response. It sounds easy enough, but I'm missing something
I guess. When you say "Put code behind your buttons that will create a
Where criteria string..." I got lost. I know about the Where argument in
the Open Report Method (I have a macro set up to do that) but I'm not sure
about the code needed or what you mean by "put it behind your button". Do I
enter the code somewhere in the 'Event' tab in the 'Properties' for the
button?

Also, when the button is clicked to open the report, it is currently running
a procedure that will only open the report for that log number. Will more
code effect that?

Sorry, I'm new to this. I'm picking up where the previous person left off.

 

[Guest]

It would be the Click event of the button.

 

[RJB]

Dave,
I've tried for a week to figure this out and can't come up with anything
that even remotely works (I'm not familiar enough with coding). I do know
how to setup a macro for OpenReport and that there is a "Where" field that
would be where I enter the criteria.

The part I don' t know is how to set up the criteria - especially since I
already have code on the "On Click" event. The code currently is as follows
(to print/preview only the record currently being edited/created).
-----------------------------
Option Compare Database

Private Sub Cmd_Workorder_Click()

Dim strReportName As String
Dim strCriteria As String

If NewRecord Then
MsgBox "This record contains no data. Please select a record to
print or Save this record." _
, vbInformation, "Invalid Action"
Exit Sub
Else
strReportName = "Rpt_BlackWorkorder"
strCriteria = "[LogID]= " & Me![LogID]

DoCmd.OpenReport strReportName, acViewPreview, Filter_BWOrder,
strCriteria

End If
End Sub

 

[Guest]

I don't know what Filter_BWOrder is, but I doubt you need it. Notice I put
the Me. keyword in front of NewRecord. It is always best to qualify
properties to ensure Access doesn't get confused.

Private Sub Cmd_Workorder_Click()
Dim strReportName As String
Dim strCriteria As String

If Me.NewRecord Then
MsgBox "This record contains no data. Please select a record to
print or Save this record." _
, vbInformation, "Invalid Action"
Exit Sub
Else
strReportName = "Rpt_BlackWorkorder"
strCriteria = "[LogID]= " & Me![LogID]

DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End If
End Sub

--
Dave Hargis, Microsoft Access MVP

Dave,
I've tried for a week to figure this out and can't come up with anything
that even remotely works (I'm not familiar enough with coding). I do know
how to setup a macro for OpenReport and that there is a "Where" field that
would be where I enter the criteria.

The part I don' t know is how to set up the criteria - especially since I
already have code on the "On Click" event. The code currently is as follows
(to print/preview only the record currently being edited/created).
-----------------------------
Option Compare Database

Private Sub Cmd_Workorder_Click()

Dim strReportName As String
Dim strCriteria As String

If NewRecord Then
MsgBox "This record contains no data. Please select a record to
print or Save this record." _
, vbInformation, "Invalid Action"
Exit Sub
Else
strReportName = "Rpt_BlackWorkorder"
strCriteria = "[LogID]= " & Me![LogID]

DoCmd.OpenReport strReportName, acViewPreview, Filter_BWOrder,
strCriteria

End If
End Sub
-----------------------------

More ideas?
Thanks,
Rick

 

[RJB]

Sorry, there's some confusion here - I had posted the actual problem a week
or so ago. The code I sent this morning (that you corrected - thank you)
was to get the report to print only the current record (instead of all
28,000).

That works, but the "filter_BWOrder" was my attempt to accomplish what I
originally posted for last week that you answered, but I didn't quite
understand how to accomplish. The filter has been removed now - but I still
need to know how to accomplish the following:

I've got a database for a printing company and I'm making the workorders
digital instead of hand written. The workorder itself is a form with a
subform in it. In the main form is a field called "LogID". In the subform
there is a field called "Log Letter" which separates each individual print
run for a particular LogID. Also in the subform is a field for "Order Type"
which has 5 options, but for example we'll just say the options are "Black"
and "Color". Any given LogID may have a mix of Order Types. 1 Black, 1
Color: 1 Black and 2 Color, vice versa...etc, all depending on what a
client is ordering with that particular job. At the bottom of the main form
is 1 button: "Create Workorder". This button opens a report that has all
the important information entered in the form/subform.

What I need to do, is separate the jobs by Order Type. When they click on
"Create Workorder", it should preview the report with one page having only
'Log Letter's that have the "Order Type" of 'Black'. Page 2 then should
have any 'Color' "Order Type"s. And if there are only 'Black' or only
'Color', then that should just preview a single page report.

Here's an example of what I've got on the form and what I need in the
report - I added some other variables the the examples to make things a
little more clear:
___________________________
FORM:

LogID: 3005
client: JohnDoe Sales

*Subform*
Log Letter: A OrderType: Black

Log Letter: B OrderType: Black

Log Letter: C OrderType: Color
_________________________________

REPORT: Black Workorder

LogID: 3005 Page: 1 of 2
JohnDoe Sales

A Black
B Black
_________________________________

REPORT: Color Workorder

LogID: 3005 Page: 2 of 2
JohnDoe Sales

C Color
_____________________________________

How can I append the code already in the "On Click" event that I posted
earlier, to include a way to separate the "black" and "color" orders?

Thanks again,
Rick

 

[Guest]

This part is not that straight forward. Depending on a lot of things, I may
use a different technique. One approach to explore would be to use Sorting
and Grouping in the report and group by Order Type. If you need different
pages, you could force a new page with the Order Type group.

In addition, if you want to print only one order type for a customer, even
if he has multiple order types, you could add that to the filtering I posted
previously.