You really don't know if the print was successful, they canceled, or 200
other possibility things. (paper jammed, ink ran out...but it kept
printing.... etc . etc etc).
The best approach here is to build a prompt form that prompts the user for
the start end (have a start + end box on the form).
Thus, you have a print button to print the report that will use the above
start/end range for the records.
And, also add a print successfully button that take the start/end boxes, and
does an update to the datePrinted table....
(the other reason for this is maybe a day later you need to print those
records again, and you DO NOT want to update the printed date (and, in some
cases...you may very want to print those records...and you DO want to update
the date. That is why having a separate button to run/set the printed date
field is better. Furthermore, they might mean to type 97-99, and type in
97-999 and bump a extra "9". when they print, they realize they made a HUGE
mistake as piles and piles of paper comes out..but you NOT set the printed
date.... (and, what would happen if they enter no numbers....and print
everything...it is VERY easy to do that with he printer dialog box).
Also, you speak of using the PK...keep in mind *if* your PK is an
autonumber, you
NEVER EVER allow users to see/use that number. There is no order, there will
be gaps, and if you export the data to another table, those numbers may
change.
(in a production database, you can't allow users to rely on autonumbers).
As for building a reprot prompt form:
The normal approach is to 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.
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
