The next issue will be the 98-liine limit per header/footer enclosure. Here
is the same code, but it inserts CustomerName as a header before every group
of 98, 98 as footer after every group of 98, and, if the last group has fewer
than 98 records, inserts the actual number of records in the last group as
the footer.
There will still be the issue of getting the data into fixed-width format.. I
use a public function to do this so I can just pass off a string & integer
(width) as the arguments and have the function pad the entry with spaces or
zeros as required.
Here is the 98-line-specific code. This is all air code (I did not create
the query to test), and I could have mistyped something. If you like, or if
something does not work, I can put it into a working MDB and e-mail it to
you.
Private Sub ButtonInvoiceExport_Click()
Dim FileName As String
FileName = "C:\MyFile.txt"
Dim FileNumber As Long
Dim qryCurrent As QueryDef
Dim rsCustomer As Recordset
Dim rsInvoice As Recordset
Dim CustIDas Long
Dim CustNameas String
Dim InvID as Long
Dim InvAmt as Integer 'pretending all are integers
Dim InvLine as String
'open new file
FileNumber = FreeFile
Open FileName For Output As FileNumber
'loop through invoice records nested inside customer record loop
Set rsCustomer = CurrentDb.OpenRecordset("Customer", dbOpenSnapshot)
rsCustomer.MoveFirst
Do While Not rsCustomer.EOF 'write each customer header
CustID = rsCustomer.Fields("CustomerID").Value
CustName = rsCustomer.Fields("CustomerName").Value
Set qryCurrent = CurrentDb.QueryDefs("CustomerInvoice")
qryCurrent.Parameters("paramCustIDCurrent") = CustIDCurrent
Set rsInvoice = CurrentDb.OpenRecordset("CustomerInvoice", dbOpenSnapshot)
Dim InvCount as Integer
Dim InvCurrent as Integer
Dim InvGroupCount as Integer
Dim InvGroupCurrent as Integer
Dim InvLeftOver as Integer
rsInvoice.MoveLast 'force accurate record count
InvCount = rsInvoice.CountRecords 'how many detail lines
InvGroupCount = Int(InvCount/98) 'how many complete sets of 98?
InvLeftOver = InvCount Mod 98 'how many in the last group?
rsInvoice.MoveFirst 'start at the beginning
Do While Not rsInvoice.EOF 'now write all invoice lines for this customer
InvCurrent = InvCurrent + 1
InvGroup = InvGroup + 1
If InvCurrent Mod 98 = 1 Then 'insert header record
Print #FileNumber, CustName 'writes customer name to file
End If
InvID = rsInvoice.Fields("InvoiceID").Value
InvAmt = rsInvoice.Fields("InvoiceAmount").Value
InvLine = InvID & "/" & InvAmt
Print #FileNumber, InvLine 'writes Invoice data to file
If InvCurrent Mod 98 = 0 Then 'insert footer record every 98th record
Print #FileNumber, 98 'section line count
Else 'insert footer using leftover count on last group
If InvGroup = InvGroupCount Then 'last group
Print #FileNumber, InvLeftOver last section line count
End If
End If
Loop 'goes to next invoice for this customer
Close rsInvoice 'when this customer is done
Loop 'goes to next Customer
Close rsCustomer 'when all customers are done
Close #FileNumber 'close the text file
End Sub
Brad said:
Are you familiar with using recordsets to loop through records? If not, here
is an example of how to interleave it.
Let's say you have a Customer table with just CustomerID & CustomerName,
then an Invoice table with InvoiceID, CustomerID (joined to
Customer.CustomerID) & InvoiceAmount. You need to export the CustomerName &
InvoiceAmount to a text file, with header for Customer, and detail ofInvoice
data. So you make a query called CustomerInvoice that joins the tables on
CustomerID & selects CustomerID, InvoiceID, & InvoiceAmount. It has one
parameter, [paramCustIDCurrent] (used in the loop later).
Private Sub ButtonInvoiceExport_Click()
Dim FileName As String
FileName = "C:\MyFile.txt"
Dim FileNumber As Long
Dim qryCurrent As QueryDef
Dim rsCustomer As Recordset
Dim rsInvoice As Recordset
Dim CustIDas Long
Dim CustNameas String
Dim InvID as Long
Dim InvAmt as Integer 'pretending all are integers
Dim InvLine as String
'open new file
FileNumber = FreeFile
Open FileName For Output As FileNumber
'loop through invoice records nested inside customer record loop
Set rsCustomer = CurrentDb.OpenRecordset("Customer", dbOpenSnapshot)
rsCustomer.MoveFirst
Do While Not rsCustomer.EOF 'write each customer header
CustID = rsCustomer.Fields("CustomerID").Value
CustName = rsCustomer.Fields("CustomerName").Value
Print #FileNumber, CustName 'writes customer name to file
Set qryCurrent = CurrentDb.QueryDefs("CustomerInvoice")
qryCurrent.Parameters("paramCustIDCurrent") = CustIDCurrent 'limit query
to this customer
Set rsInvoice = CurrentDb.OpenRecordset("CustomerInvoice", dbOpenSnapshot)
rsInvoice .MoveFirst
Do While Not rsInvoice.EOF 'now write all invoice lines for this customer
InvID = rsInvoice.Fields("InvoiceID").Value
InvAmt = rsInvoice.Fields("InvoiceAmount").Value
InvLine = InvID & "/" & InvAmt
Print #FileNumber, InvLine 'writes Invoice data to file
Loop 'goes to next invoice for this customer
Close rsInvoice 'when this customer is done
Loop 'goes to next Customer
Close rsCustomer 'when all customers are done
Close #FileNumber 'close the text file
End Sub
This will generate a file like this:
John Smith
17 / 1400
13765 / 120
Jane Doe
9438 / 84850
89 / 2345
that is
CustomerName #1
His first invoice ID / amount
His second invoice ID / amount
etc. etc.
CustomerName #2
Her first invoice ID / amount
Her second invoice ID / amount
etc. etc.
:
I have never used a report for data exports - only for formatted visual
output. For exports to other databases, I have always written data straight
to a text file.
Perhaps I am missing the meaning of your term "upload data to another
system". I inferring that this was to be imported into some otherdatabase
format as records. Fixed-width is usually associated with mainframe systems
such as the AS400, and most such systems should not care whether they get
more than 100 records in single import batch, so a little more specifics
would help.
If you can post a little more detail on what type of system that is and the
method used to import it into that system, we can focus more on which method
would work best and how to accomplish it.
:
I don't think I would use an Access Report for this purpose, though I
suppose you might be able to do so. What you describe sounds to me to be a
recordset, a standard old DOS/Windows Text File.
Open, Print #, Close are commands used in VBA for writing sequential text
files. Documentation on the subject seems to have gone downhill -- it used
to be quite good, and may only be a little difficult to locate, now.
Larry Linson
Microsoft Office Access MVP
I am trying to determine the best way to create a report in fixed
width text file format but am having a problem determining how to add
a header and footer field. This report will be used to upload data to
another system. Here are my specs I need to put a headerline then up
to 98 records then a footer field that contains among otherthings a
count of rhe reords on the page. This then repeats untilall records
are exported. I am looking for suggestion on the best way to do
this. By the way, the data is SQL format so any solutioncan be
either MS SQL or Access.
Any suggests will be greatly appreciated.
Thanks,- Hide quoted text -
- Show quoted text -
Thanks for both responses but, I am still not sure how to get this
accomplished. I have created a report that has the header and footer
rows with 98 rows be page that I can export as a text file but,the
last page includes blank rows if not a full 98 rows. -
Brian if I use your method how do I get the intervening footer and
headers between each set of 98 records. This really seems asthough
it should be simpler than I am making it out to be but, I have a total
block on how to make this work.- Hide quoted text -
- Show quoted text -
Your right, I am writing this to be uploaded to an AS400 type system
and I think your method of writing the text file is a much better way
to to it. The format that is required is as follows:
Header Row
detail..
detail..
detail..
etc (98 times)
Footer Row (contains count of rows between header and footer)
Header Row
detail..
detail..
detail.. (again up to 98 rows between header and footer)
Footer Row
I guess what I am having trouble figuring out is how to make the
header and footer rows work since they have to appear between every98
rows and the footer row has to include the number of records between
the header and footer. (the number is only less than 98 on the last
...
read more »- Hide quoted text -
- Show quoted text -
