Access Issue with Saving Invoices

[vman92]

I have no trouble printing a stack of customer invoices at month end. I
can then sort them out and mail them. I am having problems getting my
head around how I would go about automatically saving each customer's
invoice out individually in a Snapshot format. The Customer Invoice is
1 report run on a query that loops through the Business table. I can
run the same report and instead save it to Snapshot. The problem is
that it is only 1 file with all customers.

 

[Guest]

You could loop through a recordset of all relevant customers and amend the
RecordSource of the report so that it prints just the current customer,
creating the snapshot file in each iteration of the loop. The code for a
procedure to do this would go something like this to save each file with a
name incorporating the current month and the customer's ID number, e.g.
Invoice200608_42.snp (42 being the customerID) in a folder F:\Monthy
Invoices. This code is simplified as it loops through all customers and all
invoices, so you'll need to amend it to establish a recordset of just those
customers who have invoices this month and to assign a string expression to
the strRS variable so that it sets the RecordSource of the report to just the
invoices for the current month.

On Error GoTo Err_Handler

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strFile As String
Dim strRS As String
Dim lngID As Long

strSQL = "SELECT CustomerID FROM Customers"

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strSQL, _
CursorType:=adOpenKeyset, _
Options:=adCmdText

Application.Echo False

With rst
Do While Not .EOF
strFile = "F:\Monthy Invoices\"
lngID = .Fields("CustomerID")
strFile = strFile & "Invoice" & Format(Date, "yyyymm") & "_" &
lngID & ".snp"
strRS = "SELECT * FROM Invoices WHERE CustomerID = " & lngID
DoCmd.OpenReport "rptInvoices", acViewDesign
Reports("rptInvoices").RecordSource = strRS
DoCmd.Close acReport, "rptInvoices", acSaveYes
DoCmd.OutputTo acOutputReport, "rptInvoices", acFormatSNP, strFile
.MoveNext
Loop
End With

Exit_Here:
Application.Echo True
Set rst = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Here

Ken Sheridan
Stafford, England

 

[vman92]

Thanks Ken,
It looks like it might work. I do however have a little more complex
SQL.
I copied this right from the SQL behind the report. I don't really
think it is formatted
properly for VBA.

strRS =

"SELECT Business.BusinessName, ImportAddresses.Name,
ImportAddresses.Add1, ImportAddresses.City, ImportAddresses.State,
ImportAddresses.Zip, Zips.Type, Zips.CustomerNumber" & _

"FROM (Business INNER JOIN Zips ON Business.CustomerNumber =
Zips.CustomerNumber)" & _

"INNER JOIN ImportAddresses ON Zips.ZipCode = ImportAddresses.Zip" & _

"GROUP BY Business.BusinessName, ImportAddresses.Name,
ImportAddresses.Add1, ImportAddresses.City, ImportAddresses.State,
ImportAddresses.Zip, Zips.Type, Zips.CustomerNumber,
ImportAddresses.Type" & _

"HAVING (((Zips.CustomerNumber)=" & lngID & ";"") AND
((ImportAddresses.Type)=[Zips].[Type]))" & _

"ORDER BY Business.BusinessName, ImportAddresses.Name;"

 

[Tony Toews]

DoCmd.OpenReport "rptInvoices", acViewDesign
Reports("rptInvoices").RecordSource = strRS
DoCmd.Close acReport, "rptInvoices", acSaveYes
DoCmd.OutputTo acOutputReport, "rptInvoices", acFormatSNP, strFile

This code presumes that you are using an MDB. Which works for many
folks. But if it's an MDE then you will need to change the record
source in the reports OnOpen event.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[vman92]

MDE?

This code presumes that you are using an MDB. Which works for many
folks. But if it's an MDE then you will need to change the record
source in the reports OnOpen event.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

Your query shouldn't pose a problem, but one slight amendment you can make is
to use a WHERE clause rather than a HAVING clause. The latter will work, but
is not really appropriate in this case; it results from using a 'group by'
the column in query design view to apply a criterion rather than applying the
criterion in a separate 'where' column in the query design grid. A HAVING
clause operates on the data after its grouped whereas a WHERE clause operates
before its grouped. The former is usually used to restrict a query on the
result of an aggregation of values. You also seem to have a join criterion
(ImportAddresses.Type =[Zips].[Type]) in the HAVING clause, which looks to me
as though it should be in the JOIN clause. Also don't use an ORDER BY clause
in a query which is used as a report's RecordSource. Instead sort the report
by means of its internal sorting and grouping mechanism (from the
View|Sorting and Grouping menu item in report design view). With these
amendments the string expression for the report's RowSource can be built like
so:

strRS = "SELECT Business.BusinessName, ImportAddresses.Name, "
strRS = strRS & "ImportAddresses.Add1, ImportAddresses.City,
ImportAddresses.State, "
strRS = strRS & "ImportAddresses.Zip, Zips.Type, Zips.CustomerNumber "
strRS = strRS & "FROM (Business INNER JOIN Zips "
strRS = strRS & "ON Business.CustomerNumber = Zips.CustomerNumber) "
strRS = strRS & "INNER JOIN ImportAddresses "
strRS = strRS & "ON Zips.ZipCode = ImportAddresses.Zip "
strRS = strRS & "AND ImportAddresses.Type =Zips.Type "
strRS = strRS & "WHERE Zips.CustomerNumber=" & lngID & " "
strRS = strRS & "GROUP BY Business.BusinessName, ImportAddresses.Name, "
strRS = strRS & "ImportAddresses.Add1, ImportAddresses.City,
ImportAddresses.State, "
strRS = strRS & "ImportAddresses.Zip, Zips.Type, Zips.CustomerNumber, "
strRS = strRS & "ImportAddresses.Type"

This assumes CustomerNumber is a long integer number data type, not a text
string.

Regarding Tony's point a.MDE file is a version specific form of the original
source .MDB file in which only compiled VBA code is present. The database is
smaller and to some extent secure from 'meddlers'. In a .MDE file object
definitions cannot be amended. What I'd probably do in a case like that is
make the RecordSource of the report a query which references as a parameter
on the CustomerNumber column a hidden control on a dialogue form from which
the snapshots are generated. You then simply need to assign the
CustomerNumber to the control in each iteration through the recordset of
customers before each call of the OututTo method. No amendment of the report
definition would be required.

Ken Sheridan
Stafford, England

Thanks Ken,
It looks like it might work. I do however have a little more complex
SQL.
I copied this right from the SQL behind the report. I don't really
think it is formatted
properly for VBA.

strRS =

"SELECT Business.BusinessName, ImportAddresses.Name,
ImportAddresses.Add1, ImportAddresses.City, ImportAddresses.State,
ImportAddresses.Zip, Zips.Type, Zips.CustomerNumber" & _

"FROM (Business INNER JOIN Zips ON Business.CustomerNumber =
Zips.CustomerNumber)" & _

"INNER JOIN ImportAddresses ON Zips.ZipCode = ImportAddresses.Zip" & _

"GROUP BY Business.BusinessName, ImportAddresses.Name,
ImportAddresses.Add1, ImportAddresses.City, ImportAddresses.State,
ImportAddresses.Zip, Zips.Type, Zips.CustomerNumber,
ImportAddresses.Type" & _

"HAVING (((Zips.CustomerNumber)=" & lngID & ";"") AND
((ImportAddresses.Type)=[Zips].[Type]))" & _

"ORDER BY Business.BusinessName, ImportAddresses.Name;"

 

[Tony Toews]

MDE?

MDE means you have created a version of the MDE which strips out the
source code and you can't update the forms, reports or code. Users
will find it a bit more difficult to "improve" things.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm