Using ad hoc table to select which records to print

[Marla]

Hi all. I have a situation I could use some help with. I have a
database used for a high-volume electronics repair business, and I'm
trying to streamline pre-shipment paperwork functions for customer
service. Depending on the customer and order type, up to 6 different
reports might be printed for each order.

I've set up a table populated on an ad hoc basis by customer service
through a continuous form with a text box for the order identification
number and a check box for each report to select which to print. Each
of these reports might also be printed from elsewhere in the database
(while this table contains no records, as it's emptied when this form
is closed), so setting the RecordSource for the report to a query
linking this table to the current underlying RecordSource doesn't seem
a viable option.

I've tried a For ... Next loop to build criteria for each report, but
the results are inconsistent (selected reports may not open or may not
have results sets matching the user's request). It seems like a more
efficient way to pull exactly the right results would be to use an SQL
statement to pull the order numbers from this table where the option
for this report = True and pass it through the where condition of the
OpenReport command, but I'm drawing a blank on just how to do this.

A sample SQL statement for selecting one type of report for printing:
SELECT tblWorkOrderPrint.lngWorkOrderNum,
tblWorkOrderPrint.rptPackingSlip
FROM tblWorkOrderPrint
WHERE (((tblWorkOrderPrint.rptPackingSlip)=True));

Any and all suggestions are welcome. Thanks for your time.

 

[Albert D. Kallal]

The approch is simply to pass the "where" clause to the report to restrit
what you want in the reprot.

Often, I build a un-bound form (a un-bound form is a form that is NOT
attached to a table - these forms are typicaly desiged for user interface
face stuff like promtps, print buttions etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for informaton
to run reports

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above shold give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

 

[Marla]

Thank you, Albert. I may be being thickheaded at the moment, but I'm
still having a bit of difficulty seeing how to apply your solution to
my problem.

To clarify, I have a form that has 7 fields (one text field for order
identification number -- lngWorkOrderNum as given in the example
above), and 6 check boxes for various reports that might be printed,
and is in continuous forms view (if the user didn't need to print
paperwork for more than one order, s/he would not open this form). So
a populated form might look something like this:

[Work Order Number] [Work Order] [Picking
Ticket] [Packing Slip] [Commercial Invoice] [Box
Labels] [Ship Labels]
249654
X X
249655
X X X
249656
X
X
X X
249657
X X
X X

(Please forgive the formatting limitations.)

In this case, we'd need a Work Order and Packing Slip for order
249564, both of those as well as a Picking Ticket for order 249655,
and so on. I'd like to figure out a way to use a where condition on
the OpenReport method so that rptWorkOrder would open with records for
249654, 249655, 249656, and 249657, but Ship Labels would only open
for 249656, based on the user's entering this data in the ad hoc
table.

I appreciate your taking the time to look at this for me.

 

[Marla]

Well, that formatting was miserable. Maybe this briefer version will
be better, depending on font of choice.


[Order Number] [WO] [Pick] [Pack] [Comm] [Box] [Ship]
249654 X X
249655 X X X
249656 X X X
X
249657 X X X X

 

[Albert D. Kallal]

Well, that formatting was miserable. Maybe this briefer version will
be better, depending on font of choice.


[Order Number] [WO] [Pick] [Pack] [Comm] [Box] [Ship]
249654 X X
249655 X X X
249656 X X X
X
249657 X X X X

It is not clear how, or where the data in the continues form data comes
from. However, you can most certainly use the data in this continues
form as a criteria to "feed" the report
system as criteria for records in the report.

For example, to print only the two above selected ship labels, your button
to print ship labels
would look like:

dim strWhere as string

strWhere = "[Order number] in " & _
"(select [order number] from tblContiouesForm where Ship = True)"

--- note that while you have a "x" for ship, I assume it is a Boolean field
(a true/false field) that you can "click" on to set?

docmd.OpenReprot "shiplables",acViewPreview,,strWhere

Of course, you eventually remove the "acviewPreivew" so it prints without a
preview.

note in the above, I called your continues form table tblCointiuesForm. You
would of course replace this with the name of your actual table (data
source) used for the continues form. (it not clear where/how that continues
form is loaded, but it don't change the above suggestion in any way).

It also assumed that order number in this continues form is the SAME data
type as the actual order number in the shipping labels report...

You could of course make a one button to print everything, but it better to
get one part working at a time, and then when all of the separate reports
work the way you want, you *then* can combine them into one buttion click
that send out the shipping reprot, and other reports with one button click..

 

[Marla]

Thanks again, Albert!

Well, that formatting was miserable. Maybe this briefer version will
be better, depending on font of choice.

[Order Number] [WO] [Pick] [Pack] [Comm] [Box] [Ship]
249654 X X
249655 X X X
249656 X X X
X
249657 X X X X

It is not clear how, or where the data in the continues form data comes
from. However, you can most certainly use the data in this continues
form as a criteria to "feed" the report
system as criteria for records in the report.

I guess I inadequately explained that the data in this form is in a
table populated by the user as needed and deleted when the form
closes. Its only purpose is to tell the database which reports need
printing for which orders.

For example, to print only the two above selected ship labels, your button
to print ship labels
would look like:

dim strWhere as string

strWhere = "[Order number] in " & _
"(select [order number] from tblContiouesForm where Ship = True)"

--- note that while you have a "x" for ship, I assume it is a Boolean field
(a true/false field) that you can "click" on to set?

Yes, as I mentioned, the form has a check box control for each report
type, and each corresponds to an underlying field in the table. I
didn't know how else to represent it visually here. The in/select
combination was just the ticket, though, and I thank you very much for
your help. It's working beautifully now.

docmd.OpenReprot "shiplables",acViewPreview,,strWhere

Of course, you eventually remove the "acviewPreivew" so it prints without a
preview.

Actually, I have one button for preview and one for print, to leave
the option to the user.

note in the above, I called your continues form table tblCointiuesForm. You
would of course replace this with the name of your actual table (data
source) used for the continues form. (it not clear where/how that continues
form is loaded, but it don't change the above suggestion in any way).

It also assumed that order number in this continues form is the SAME data
type as the actual order number in the shipping labels report...

Yes, I learned years ago that it's painful to store a value referring
to a foreign key in the wrong format. All my order numbers everywhere
are long integers.

You could of course make a one button to print everything, but it better to
get one part working at a time, and then when all of the separate reports
work the way you want, you *then* can combine them into one buttion click
that send out the shipping reprot, and other reports with one button click..

Thanks a lot, Albert. I really appreciate your help, and so will my
users.