Solved

VBA code to write simple Access table to multi-record fixed-width text file with multiple layouts.

Posted on 2009-04-05
8
2,003 Views
Last Modified: 2013-11-27
I have a simple table in Access 2002/2003.  Using VBA, I need to read each record in the table and for each record in the table, write out 3 fixed width text records with defferent layouts to a text file.  So for example, if the table has 10 records, I will end up with a single text file with 30 records, but as I said, each of the 3 text records has a different fixed-width layout. One text record is a Header Record, one record is a Wage Record (empty except for a "W" in column 1) and one record is a Tax Record. Note that not all the input fields are needed and a few constant fields are required on the ouput text file (H, W, T, YN, etc).  The EIN field is NOT a constant.

Setting up the code to loop thru the recordset (Table) was a no-brainer.  I have attached that code.  I have also attached some files showing sample contents of the input recordset and the layouts of the 3 record types.  I created 3 Export File Specifications and tried to use DoCmd.TransferText acExport Fixed, for example:

txtExportName = "C:\Temp\PaymentExport.txt
Docmd.TransferText acExportFixed, RecordHExportSpec, tblFilteredInput, txtExportName
Docmd.TransferText acExportFixed, RecordWExportSpec, tblFilteredInput, txtExportName
Docmd.TransferText acExportFixed, RecordTExportSpec, tblFilteredInput, txtExportName

But using this results in a text file containing only T records (since each TransferText statement overwrites the prior one (and makes the use of the loop irrelevant, anyway)

So how do I make this loop act as follows:

1. Read a single recordset row
2. Append a properly formatted H record to the text file
3. Append a properly formatted W record to the same text file
4. Append a properly formatted T record to the same text file
5. Read the next row of the recordset and repeat steps 2 thru 4
6. Continue as above until EOF

One other small detail -- there is a single $ amount field in the T (Tax) record.  Even using the Export Specification File for the T-record, I can't get the text file to properly fill the amount field with leading zeros and right-justify, for example 000000340.97;  it wants to left-justify the amount and strip off the leading zeros, as: 340.97.
Option Compare Database

Option Explicit
 

Private Sub Main()
 

    Dim db As Database

    Set db = CurrentDb

    Dim recFilteredInput As DAO.Recordset
 

    Set recFilteredInput = db.OpenRecordset("tblFilteredInput", _

        dbOpenDynaset)
 

    Do While Not recFilteredInput.EOF

        'Place here commands to write out text records

                            

        recFilteredInput.MoveNext

    Loop
 

End Sub

Open in new window

tblFilteredInput.jpg
PaymentExport.txt
tblRecordHExportSpec.jpg
tblRecordHKey.txt
tblRecordTExportSpec.jpg
0
Comment
Question by:bbecker
  • 5
  • 3
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24073334
it is not clear, how the output would look like, but this will get you started
Option Compare Database

Option Explicit

 

Private Sub Main()

 

    Dim db As Database

    Set db = CurrentDb

'    Dim recFilteredInput As DAO.Recordset

    Dim rs As DAO.Recordset

    Set rs = db.OpenRecordset("tblFilteredInput", _

        dbOpenDynaset)

    open "c:\myTextFile.txt" for append as #1

    dim hLine as string,wLine as string, tLine as string
 

    Do While Not recFilteredInput.EOF

       'format the record here

       hLine="H" & rs("GenesisEIN") & string(26," ") & rs("PayrollID") & string(24," ") & rs("LiabilityDate"

        'write the hLine to the textr file

      Print #1,hLine

  

      ' do the same for the   other lines 

        

                            

        recFilteredInput.MoveNext

    Loop

 

End Sub

Open in new window

0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 24073344

Option Compare Database

Option Explicit

 

Private Sub Main()

 

    Dim db As Database

    Set db = CurrentDb

'    Dim recFilteredInput As DAO.Recordset

    Dim rs As DAO.Recordset

    Set rs = db.OpenRecordset("tblFilteredInput", _

        dbOpenDynaset)

    open "c:\myTextFile.txt" for append as #1

    dim hLine as string,wLine as string, tLine as string

 

    Do While Not recFilteredInput.EOF

       'format the record here

       hLine="H" & rs("GenesisEIN") & string(26," ") & rs("PayrollID") & string(24," ") & rs("LiabilityDate"

        'write the hLine to the textr file

      Print #1,hLine

  

      ' do the same for the   other lines 

      wLine=..  

      Print #1,wLine   
 

      tLine=..  

      Print #1,tLine                   

        rs.MoveNext

    Loop

Close #1

End Sub

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24073485

you can also use instead of this line
open "c:\myTextFile.txt" for append as #1

'the line below
open "c:\myTextFile.txt" for output as #1
0
 

Author Comment

by:bbecker
ID: 24073491
Hi capricorn,

Thanks very VERY much.  I see where this is going and I believe it will work.  Do I dare press you for a solution to my additional little problem -- how do I get (for instance) 340.97 to come out on the text file as 000000340.97, or at least properly right-justify the number (with or without the leading zeroes)?  Will I have to parse the field somehow and count the digits then add the appropriate number of zeroes to the left using a variable, or is there a way to right-justify the number?   Thanks again.  You've earned your points already.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:bbecker
ID: 24073508
so opening the file as output will clear out any existing data in the file, as opposed to append.  And that will save me the trouble of testing whether the file already exists and KILL it. Right?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24073509
use format

format(340.97,"000000000.00")
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24073539
so opening the file as output will clear out any existing data in the file, as opposed to append.  And that will save me the trouble of testing whether the file already exists and KILL it. Right?


Yes
0
 

Author Closing Comment

by:bbecker
ID: 31566834
capricorn1 you are the BEST!!!!!  I owe you my sanity.  Your solution works like a charm.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now