Headers, Fillers, Trailers...Oh My!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Headers, Fillers, Trailers...Oh My!

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-05-04 : 12:38:23

New to VB, I'm having to create a text file from an Access Form. (I bought VB & VBA in a Nutshell, but there are no examples).

The text file should contain the record that is generated from filling out the OPA form and the header record--which should be written first

and the trailer record that follows.

Here's my code...

Private Sub Command45_Click()
Me.RecordSource = ""
strDate = Format(Date, "yyyymmdd")
strTime = Format(Time, "hhmmss")
strHdr = "1" & strDate & strTime & " "
strTrl = "9" & "000001"
DoCmd.TransferText acExportFixed, "OPA", "OPA", "c:\HIP_PA_OutPat_METH_" & strDate & strTime & ".txt"
Me.RecordSource = "OPA"
End Sub

I set up the strings to create the header and trailer records, but how can I fit them into the Transfertext command?

Thanks for any help!

Semper fi,

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-05-05 : 14:09:56

Try this....

Dim db As Database
Dim i As Integer
Dim rst As Recordset
Dim fso As Object
Dim Ftype As String

Me.RecordSource = ""
Set rst = CurrentDb.OpenRecordset(strTab)
Set fso = CreateObject("Scripting.FileSystemObject")
strDate = Format(Date, "yyyymmdd")
strTime = Format(Time, "hhmmss")
strHdr = "1" & strDate & strTime & " "
If strTab = "TEMPOPA" Then
Ftype = "Out"
ElseIf strTab = "IPA" Then
Ftype = "In"
End If

Set Textfile = fso.CreateTextFile("c:\HIP_PA_" & Ftype & "Pat_METH_" & strDate & strTime & ".txt", True)
Textfile.WriteLine (strHdr)
Do Until rst.EOF = True
Textfile.WriteLine (rst![Rec Type] & rst![Function] & rst![RID] & rst![Process Dt] & rst![Ref IHCP Num] & rst![Prov IHCP Num] & rst![Admit Dt] & rst![Thru Dt] & rst![Diag Cd 1] & rst![Diag Cd 2] & rst![Total Units] & rst![Tot Appvd Units] & rst![Tot Denied Units] & rst![Status Reason] & rst![Patient Status] & rst!POS & rst![Proc Code] & rst![PA Auth Num] & rst![Line Num] & rst![Ref NPI] & rst![Prov NPI])
rst.MoveNext
i = i + 1
Loop

strTlr = "9" & Right("0000000000" & CInt(i), 6)
Textfile.WriteLine (strTlr)
Textfile.Close
Me.RecordSource = "TEMPOPA"
End Function

It's amazing how you can answer your OWN question! Xerx, you're a genius!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources