G
Guest
Forgive you..???? For what, helping!! I am grateful for your help. I changed
the code, and I copied the name from the query and pasted it into the code to
make sure I got it exactly correct. Here is what that line says:
Set rst = CurrentDb.OpenRecordset("[AP Payment Export]", _
dbOpenSnapshot)
When I ran it, I got this message: 3078 The Microsoft Jet database engine
cannot find the input table or qury '[AP Payment Export]'. Make sure it
exists and that its name is spelled correctly.
the code, and I copied the name from the query and pasted it into the code to
make sure I got it exactly correct. Here is what that line says:
Set rst = CurrentDb.OpenRecordset("[AP Payment Export]", _
dbOpenSnapshot)
When I ran it, I got this message: 3078 The Microsoft Jet database engine
cannot find the input table or qury '[AP Payment Export]'. Make sure it
exists and that its name is spelled correctly.
Gary Walter said:I never use spaces in query/table names,
so forgive me if I forget to bracket..
change following code line
(put brackets around query name)
Set rst = CurrentDb.OpenRecordset("AP Payment Export", _
dbOpenSnapshot)
to
Set rst = CurrentDb.OpenRecordset("[AP Payment Export]", _
dbOpenSnapshot)
that is...if query name is for sure "AP Payment Export"
save your code and compile again
if you put a question mark in front of the
function when you type in Immediate
Window, you will get "True" if it completes
properly, i.e., in Immediate Window, type
?fExpAP_Pmt
hopefully, you will get True w/change
Donna said:I deleted the original modTest and created a new one. When I ran it, this
is
the message I received:
3265 Item not found in this collection.
Gary Walter said:did you cut the attempt earlier
to run the function in the wrong
window?
I typed in fExpAP_Pmt and hit enter and I got this error:
Compile error:
Ambiguous name detected: fExpAP_Pmt
:
this is all untested code...
plus, quick-and-dirty....
1) you can change "C:\xxx.txt" to a
for-sure path and filename...
be sure and save/compile again
if it wasn't q&d, one would input
this path/filename when call function,
then, code would test if it exists,
and if it does, ask user if wanted
to overwrite it or cancel....
but I don't even know if what is
there works, or if it works, does
what you want.
2) in the window where you entered
code for module, there usually is a
"bottom window" with a title bar of
"Immediate"
if it is not there, then hit
<CTRL> G keys simultaneously
that's what they call the "Immediate Window"
3) if it works and does what you want
(all off top of my head, so probably
something wrong somewhere)
you could create a macro to run it,
or (recommended),
put a command button on a form
that runs it when clicked.
:
Thanks for the code!!!! I know you are busy, so if you can't help
me,
please
let me know. I absolutely do not want to hinder you.
I have a couple of questions:
1. "C:\xxx.txt" - is this where I input the location of where I
want
the
txt tile to be exported?
2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the
page
after I compiled, hit enter and nothing happened.
3. Can I set up a Macro to automatically run this module?
:
start a new module
copy and paste this function
into the new module
save module as "modTest"
in top menu, click on Compile..
from "Debug" drop-down
if all above went well,
test it by:
in Immediate Window at bottom
of window, type
fExpAP_Pmt
and hit <ENTER> key
'***start code****
Option Explicit
Public Function fExpAP_Pmt() As Boolean
On Error GoTo Err_fExpAP_Pmt
Dim strPath As String
Dim rst As DAO.Recordset
Dim hFile As Long
Dim strTextLine As String
Dim varCurFld As Variant
strPath = "C:\xxx.txt"
Set rst = CurrentDb.OpenRecordset("AP Payment Export", _
dbOpenSnapshot)
With rst
.MoveFirst
'First get a new file handle
hFile = FreeFile
'Open the strPath to write the text
Open strPath For Output As hFile
Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but your one field
'ssn-name 1 25
varCurFld = Left(rst![ssn-name] & Space(25), 25)
strTextLine = strTextLine & varCurFld
'date 26 6
varCurFld = Left(rst![Date] & Space(6), 6)
strTextLine = strTextLine & varCurFld
'FILLER5 32 5
varCurFld = Left(rst!FILLERS & Space(5), 5)
strTextLine = strTextLine & varCurFld
'v# 37 6
varCurFld = Left(rst![v#] & Space(6), 6)
strTextLine = strTextLine & varCurFld
'FILLER3 43 3
varCurFld = Left(rst!FILLER3 & Space(3), 3)
strTextLine = strTextLine & varCurFld
'amt 46 9 <---RIGHT ALIGN
varCurFld = Right(Space(9) & rst!amt, 9)
strTextLine = strTextLine & varCurFld
'FILLER1 55 1
varCurFld = Left(rst!FILLER1 & Space(1), 1)
strTextLine = strTextLine & varCurFld
'case# 56 25
varCurFld = Left(rst![case#] & Space(25), 25)
strTextLine = strTextLine & varCurFld
'charge 81 4
varCurFld = Left(rst!charge & Space(4), 4)
strTextLine = strTextLine & varCurFld
'FILLER5A 85 5
varCurFld = Left(rst!FILLER5A & Space(5), 5)
strTextLine = strTextLine & varCurFld
'FREQ 90 2
varCurFld = Left(rst!FREQ & Space(2), 2)
strTextLine = strTextLine & varCurFld
'FILLER10 92 10
varCurFld = Left(rst!FILLER10 & Space(10), 10)
strTextLine = strTextLine & varCurFld
'print the collected string to textfile
Print #hFile, strTextLine
'get next record
.MoveNext
Loop
End With
Close hFile ' Close file.
rst.Close
fExpAP_Pmt = True
Exit_fExpAP_Pmt:
Set rst = Nothing
Reset
Exit Function
Err_fExpAP_Pmt:
MsgBox Err.Number & " " & Err.Description
Resume Exit_fExpAP_Pmt
End Function
'***end code***
Gary - The DataType is text for all fields. The field that needs
to
be
right-aligned is the "amt" field. I know this is asking a lot,
but I
really,
really appreciate your help.
:
missing 1 requested field please
DataType FieldName Start Width
this will not involve a schema.ini file....
this will read in field values for a record,
then create *line* in code for that record,
then write that line to a text file....
if that's too complicated for you,
tell me now so I don't waste my
time please...
if not, it might also help me
if you could copy here a few lines
of the "badly aligned text file"
thanks
:
Gary - here is the specs for the export specification.
Everything I
know
about Access I taught myself, so all that 'language' you
posted
may
as
well
be Greek. haha I appreciate your help.
If I understand correctly, you can create a schema.ini file
which I
can
save
in the folder where the export is to be placed and everythime
I
export,
the
amount field will right align, right??
The name of the Query I am exporting is AP Payment Export if
this
matters.
Field Name Start Width
ssn-name 1 25
date 26 6
FILLER5 32 5
v# 37 6
FILLER3 43 3