Access Query Right Align

sorry, change it back (remove brackets),
then make the following change:
(FILLER5 instead of FILLERS)

'FILLER5 32 5
varCurFld = Left(rst!FILLERS & Space(5), 5)
strTextLine = strTextLine & varCurFld

should be

'FILLER5 32 5
varCurFld = Left(rst!FILLER5 & Space(5), 5)
strTextLine = strTextLine & varCurFld

hopefully that will do it....8-)

"Donna" wrote:
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" wrote:

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" wrote:
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" wrote:

did you cut the attempt earlier
to run the function in the wrong
window?


"Donna" wrote in message
...
I typed in fExpAP_Pmt and hit enter and I got this error:

Compile error:
Ambiguous name detected: fExpAP_Pmt


"Gary Walter" wrote:

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.

"Donna" wrote:
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?

"Gary Walter" wrote:

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***


"Donna" wrote in message
...
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.


"Gary Walter" wrote:

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

"Donna" wrote:
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

It said "True"....... woohoo!!!! Now, I just need to come up with a way to
export it to my specific drive, and replace the current file, if any, right?
How can I look at the results?

URFANTASTIC!!!! :0)

"Gary Walter" wrote:

sorry, change it back (remove brackets),
then make the following change:
(FILLER5 instead of FILLERS)

'FILLER5 32 5
varCurFld = Left(rst!FILLERS & Space(5), 5)
strTextLine = strTextLine & varCurFld

should be

'FILLER5 32 5
varCurFld = Left(rst!FILLER5 & Space(5), 5)
strTextLine = strTextLine & varCurFld

hopefully that will do it....8-)

"Donna" wrote:
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" wrote:

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" wrote:
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" wrote:

did you cut the attempt earlier
to run the function in the wrong
window?


"Donna" wrote in message
...
I typed in fExpAP_Pmt and hit enter and I got this error:

Compile error:
Ambiguous name detected: fExpAP_Pmt


"Gary Walter" wrote:

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.

"Donna" wrote:
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?

"Gary Walter" wrote:

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***


"Donna" wrote in message
...
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.


"Gary Walter" wrote:

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"

Sorry, It put it in the correct file and it looks GREAT!!!!!! I can't tell
you how much you have helped me. If you ever need anything (tax related,
definitely NOT VB) contact me.

Donna D. Driver
Tax Manager
ph: 615-231-2822
fax: 615-231-2505
Captain D's, LLC
1717 Elm Hill Pike, Ste A-1
Nashville, TN 37210


"Gary Walter" wrote:

sorry, change it back (remove brackets),
then make the following change:
(FILLER5 instead of FILLERS)

'FILLER5 32 5
varCurFld = Left(rst!FILLERS & Space(5), 5)
strTextLine = strTextLine & varCurFld

should be

'FILLER5 32 5
varCurFld = Left(rst!FILLER5 & Space(5), 5)
strTextLine = strTextLine & varCurFld

hopefully that will do it....8-)

"Donna" wrote:
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" wrote:

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" wrote:
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" wrote:

did you cut the attempt earlier
to run the function in the wrong
window?


"Donna" wrote in message
...
I typed in fExpAP_Pmt and hit enter and I got this error:

Compile error:
Ambiguous name detected: fExpAP_Pmt


"Gary Walter" wrote:

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.

"Donna" wrote:
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?

"Gary Walter" wrote:

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***


"Donna" wrote in message
...
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.


"Gary Walter" wrote:

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"

great! want to try something else?

do you remember what you named your
original export specification (it would
be in tblSaveIMEX.SpecName)?

you created 2 tables earlier using
following queries:

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,
I.FileType,
I.SpecID,
I.SpecName,
I.SpecType,
I.StartRow,
I.TextDelim,
I.TimeDelim
INTO
tblSaveIMEX
FROM
MSysImexSpecs AS I;

Add a field to "tblSaveIMEXColumns"
'"RightAlign" Yes/No, default 0

then change RightAlign to -1
for column(s) wanted right-aligned

then, paste following code into
your module:

'***start code***
Public Function fExportSpec(pPath As String, _
pQryName As String, _
pSpec As String) As Boolean
On Error GoTo Err_fExportSpec
Dim strPath As String
Dim rstQry As DAO.Recordset
Dim rstSpec As DAO.Recordset
Dim strSQL As String
Dim hFile As Long
Dim strTextLine As String
Dim strFldName As String
Dim lngWidth As Long
Dim varCurFld As Variant

'test example:
'to save "qryTestExport"
'to file "C:\newxx.txt"
'using Export Specification "testspec"
'
'in Immediate Window, type
'?fexportspec("C:\newxxx.txt","qryTestExport","tes tspec")

'NOTE: Code below does not test for valid parameters!
' Probably good idea to add that.
'
strPath = pPath

Set rstQry = CurrentDb.OpenRecordset(pQryName, dbOpenSnapshot)

strSQL = "SELECT C.FieldName, C.Start, C.Width, RightAlign " _
& "FROM tblSaveIMEX AS I " _
& "INNER JOIN tblSaveIMEXColumns AS C " _
& "ON I.SpecID = C.SpecID " _
& "WHERE i.SpecName = '" & pSpec & "' " _
& "ORDER BY C.Start"
Set rstSpec = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'First get a new file handle
hFile = FreeFile

'Open the strPath to write the text
Open strPath For Output As hFile

With rstQry
.MoveFirst

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but field(s)
'where wanted RightAlign
With rstSpec
.MoveFirst
Do While Not .EOF
strFldName = !FieldName
lngWidth = !Width
varCurFld = rstQry(strFldName)
If !rightalign = 0 Then
varCurFld = Left(varCurFld _
& Space(lngWidth), lngWidth)
Else
varCurFld = Right(Space(lngWidth) _
& varCurFld, lngWidth)
End If
strTextLine = strTextLine & varCurFld
.MoveNext
Loop

End With


'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rstQry.Close
rstSpec.Close

fExportSpec = True

Exit_fExportSpec:
Set rstQry = Nothing
Set rstSpec = Nothing
Reset
Exit Function

Err_fExportSpec:
MsgBox Err.Number & " " & Err.Description
Resume Exit_fExportSpec
End Function
'***end code***

save and compile.

Create a new form
(click on "Create form in Design View")

Add a command button to close the
form using wizard...
(select command button icon in toolbar,
double-click on form,
in wizard, choose
Form Operations/Close Form,
click Next,
choose Text radio button,
click Next,
type in "cmdClose" for meaningful name,
then click Finish)

While your new command button
is selected, in upper menu

Edit/Duplicate

Right-mouse click on new button
and choose "Properties"

Under "All" tab, change Name and Caption

Name ....cmdExportAP
Caption..Export AP to Text File

further down in Properties,
double-click in white space
beside "On Click"

you should now have "[Event Procedure]"
in white space, with little box to the right
of the white space.

click on that box (with 3 dots)

You now should be in VBA window
with cursor blinking in middle of


Private Sub cmdExportAP_Click()

End Sub

"in the middle," paste following code

'***start code***
On Error GoTo Err_cmdExportAP_Click
Dim varResponse As Variant
Dim strMsg As String
Dim strPath As String
Dim strQryName As String
Dim strSpec As String

strMsg = "Do you wish to export AP Pmts to text file?"
varResponse = MsgBox(strMsg, vbOKCancel)
If varResponse = vbCancel Then
' User chose to Cancel
MsgBox "You chose to cancel export."
GoTo Exit_cmdExportAP_Click
Else
'do it
strMsg = "Please enter full export path " _
& "and filename (C:\xxx.txt)"
strPath = InputBox(strMsg, "Export", strPath)

strQryName = "AP Payment Export"
strSpec = "??????"

'run export
If fExportSpec(strPath, strQryName, strSpec) = True Then
MsgBox "Successfully exported to " & strPath
Else
MsgBox "Export Failed!"
End If

End If

Exit_cmdExportAP_Click:
Exit Sub

Err_cmdExportAP_Click:
MsgBox Err.Description
Resume Exit_cmdExportAP_Click
'***end code***

replace "??????" with actual name of spec in line

strSpec = "??????"

Save and compile.

Save form and open, click on button.

Now you know how to add this button
to any form (I assume you don't really
need this separate form).

First of all, you have been so kind and patient, I just hate to keep
bothering you; I know you are very busy.

Ok, I know I am sounding pretty dumb, but I didn't create any files before,
I just copied the export specification info, which only listed the info I
gave you. I don't know what "tblSaveIMEXColumns" is (sorry). I do need to
come up with a way to actually run the process, which it appears you have
some code her to do that, but I don't know what to do with it.

"Gary Walter" wrote:

great! want to try something else?

do you remember what you named your
original export specification (it would
be in tblSaveIMEX.SpecName)?

you created 2 tables earlier using
following queries:

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,
I.FileType,
I.SpecID,
I.SpecName,
I.SpecType,
I.StartRow,
I.TextDelim,
I.TimeDelim
INTO
tblSaveIMEX
FROM
MSysImexSpecs AS I;

Add a field to "tblSaveIMEXColumns"
'"RightAlign" Yes/No, default 0

then change RightAlign to -1
for column(s) wanted right-aligned

then, paste following code into
your module:

'***start code***
Public Function fExportSpec(pPath As String, _
pQryName As String, _
pSpec As String) As Boolean
On Error GoTo Err_fExportSpec
Dim strPath As String
Dim rstQry As DAO.Recordset
Dim rstSpec As DAO.Recordset
Dim strSQL As String
Dim hFile As Long
Dim strTextLine As String
Dim strFldName As String
Dim lngWidth As Long
Dim varCurFld As Variant

'test example:
'to save "qryTestExport"
'to file "C:\newxx.txt"
'using Export Specification "testspec"
'
'in Immediate Window, type
'?fexportspec("C:\newxxx.txt","qryTestExport","tes tspec")

'NOTE: Code below does not test for valid parameters!
' Probably good idea to add that.
'
strPath = pPath

Set rstQry = CurrentDb.OpenRecordset(pQryName, dbOpenSnapshot)

strSQL = "SELECT C.FieldName, C.Start, C.Width, RightAlign " _
& "FROM tblSaveIMEX AS I " _
& "INNER JOIN tblSaveIMEXColumns AS C " _
& "ON I.SpecID = C.SpecID " _
& "WHERE i.SpecName = '" & pSpec & "' " _
& "ORDER BY C.Start"
Set rstSpec = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'First get a new file handle
hFile = FreeFile

'Open the strPath to write the text
Open strPath For Output As hFile

With rstQry
.MoveFirst

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but field(s)
'where wanted RightAlign
With rstSpec
.MoveFirst
Do While Not .EOF
strFldName = !FieldName
lngWidth = !Width
varCurFld = rstQry(strFldName)
If !rightalign = 0 Then
varCurFld = Left(varCurFld _
& Space(lngWidth), lngWidth)
Else
varCurFld = Right(Space(lngWidth) _
& varCurFld, lngWidth)
End If
strTextLine = strTextLine & varCurFld
.MoveNext
Loop

End With


'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rstQry.Close
rstSpec.Close

fExportSpec = True

Exit_fExportSpec:
Set rstQry = Nothing
Set rstSpec = Nothing
Reset
Exit Function

Err_fExportSpec:
MsgBox Err.Number & " " & Err.Description
Resume Exit_fExportSpec
End Function
'***end code***

save and compile.

Create a new form
(click on "Create form in Design View")

Add a command button to close the
form using wizard...
(select command button icon in toolbar,
double-click on form,
in wizard, choose
Form Operations/Close Form,
click Next,
choose Text radio button,
click Next,
type in "cmdClose" for meaningful name,
then click Finish)

While your new command button
is selected, in upper menu

Edit/Duplicate

Right-mouse click on new button
and choose "Properties"

Under "All" tab, change Name and Caption

Name ....cmdExportAP
Caption..Export AP to Text File

further down in Properties,
double-click in white space
beside "On Click"

you should now have "[Event Procedure]"
in white space, with little box to the right
of the white space.

click on that box (with 3 dots)

You now should be in VBA window
with cursor blinking in middle of


Private Sub cmdExportAP_Click()

End Sub

"in the middle," paste following code

'***start code***
On Error GoTo Err_cmdExportAP_Click
Dim varResponse As Variant
Dim strMsg As String
Dim strPath As String
Dim strQryName As String
Dim strSpec As String

strMsg = "Do you wish to export AP Pmts to text file?"
varResponse = MsgBox(strMsg, vbOKCancel)
If varResponse = vbCancel Then
' User chose to Cancel
MsgBox "You chose to cancel export."
GoTo Exit_cmdExportAP_Click
Else
'do it
strMsg = "Please enter full export path " _
& "and filename (C:\xxx.txt)"
strPath = InputBox(strMsg, "Export", strPath)

strQryName = "AP Payment Export"
strSpec = "??????"

'run export
If fExportSpec(strPath, strQryName, strSpec) = True Then
MsgBox "Successfully exported to " & strPath
Else
MsgBox "Export Failed!"
End If

End If

Exit_cmdExportAP_Click:
Exit Sub

Err_cmdExportAP_Click:
MsgBox Err.Description
Resume Exit_cmdExportAP_Click
'***end code***

replace "??????" with actual name of spec in line

strSpec = "??????"

Save and compile.

Save form and open, click on button.

Now you know how to add this button
to any form (I assume you don't really
need this separate form).

in Database Window
(the one with "Objects" down
the left side),
click on "Queries" in Objects list,
then click on
"Create query in Design view"
in "Show Table" dialog, click on Close.

In upper left menu, click on "SQL"

delete "SELECT;"

paste in one of the queries I typed
out for you

then click on red exclamation mark
to run it

then delete everything,
paste in other query I typed
out for you

then click on red exclamation mark
to run it

You now should have 2 new tables

if so, no need to save query


"Donna" wrote:
First of all, you have been so kind and patient, I just hate to keep
bothering you; I know you are very busy.

Ok, I know I am sounding pretty dumb, but I didn't create any files
before,
I just copied the export specification info, which only listed the info I
gave you. I don't know what "tblSaveIMEXColumns" is (sorry). I do need to
come up with a way to actually run the process, which it appears you have
some code her to do that, but I don't know what to do with it.

"Gary Walter" wrote:

great! want to try something else?

do you remember what you named your
original export specification (it would
be in tblSaveIMEX.SpecName)?

you created 2 tables earlier using
following queries:

SELECT
M.Attributes,
M.DataType,
M.FieldName,
M.IndexType,
M.SkipColumn,
M.SpecID,
M.Start,
M.Width
INTO
tblSaveIMEXColumns
FROM
MSysIMEXColumns AS M
ORDER BY
M.SpecID,
M.Start;

SELECT
I.DateDelim,
I.DateFourDigitYear,
I.DateLeadingZeros,
I.DateOrder,
I.DecimalPoint,
I.FieldSeparator,
I.FileType,
I.SpecID,
I.SpecName,
I.SpecType,
I.StartRow,
I.TextDelim,
I.TimeDelim
INTO
tblSaveIMEX
FROM
MSysImexSpecs AS I;

Add a field to "tblSaveIMEXColumns"
'"RightAlign" Yes/No, default 0

then change RightAlign to -1
for column(s) wanted right-aligned

then, paste following code into
your module:

'***start code***
Public Function fExportSpec(pPath As String, _
pQryName As String, _
pSpec As String) As Boolean
On Error GoTo Err_fExportSpec
Dim strPath As String
Dim rstQry As DAO.Recordset
Dim rstSpec As DAO.Recordset
Dim strSQL As String
Dim hFile As Long
Dim strTextLine As String
Dim strFldName As String
Dim lngWidth As Long
Dim varCurFld As Variant

'test example:
'to save "qryTestExport"
'to file "C:\newxx.txt"
'using Export Specification "testspec"
'
'in Immediate Window, type
'?fexportspec("C:\newxxx.txt","qryTestExport","tes tspec")

'NOTE: Code below does not test for valid parameters!
' Probably good idea to add that.
'
strPath = pPath

Set rstQry = CurrentDb.OpenRecordset(pQryName, dbOpenSnapshot)

strSQL = "SELECT C.FieldName, C.Start, C.Width, RightAlign " _
& "FROM tblSaveIMEX AS I " _
& "INNER JOIN tblSaveIMEXColumns AS C " _
& "ON I.SpecID = C.SpecID " _
& "WHERE i.SpecName = '" & pSpec & "' " _
& "ORDER BY C.Start"
Set rstSpec = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'First get a new file handle
hFile = FreeFile

'Open the strPath to write the text
Open strPath For Output As hFile

With rstQry
.MoveFirst

Do While (Not .EOF)
strTextLine = ""
'collect field values into strTextLine
'in order/width per IMEX Spec;
'left-align all but field(s)
'where wanted RightAlign
With rstSpec
.MoveFirst
Do While Not .EOF
strFldName = !FieldName
lngWidth = !Width
varCurFld = rstQry(strFldName)
If !rightalign = 0 Then
varCurFld = Left(varCurFld _
& Space(lngWidth), lngWidth)
Else
varCurFld = Right(Space(lngWidth) _
& varCurFld, lngWidth)
End If
strTextLine = strTextLine & varCurFld
.MoveNext
Loop

End With


'print the collected string to textfile
Print #hFile, strTextLine

'get next record
.MoveNext
Loop

End With

Close hFile ' Close file.

rstQry.Close
rstSpec.Close

fExportSpec = True

Exit_fExportSpec:
Set rstQry = Nothing
Set rstSpec = Nothing
Reset
Exit Function

Err_fExportSpec:
MsgBox Err.Number & " " & Err.Description
Resume Exit_fExportSpec
End Function
'***end code***

save and compile.

Create a new form
(click on "Create form in Design View")

Add a command button to close the
form using wizard...
(select command button icon in toolbar,
double-click on form,
in wizard, choose
Form Operations/Close Form,
click Next,
choose Text radio button,
click Next,
type in "cmdClose" for meaningful name,
then click Finish)

While your new command button
is selected, in upper menu

Edit/Duplicate

Right-mouse click on new button
and choose "Properties"

Under "All" tab, change Name and Caption

Name ....cmdExportAP
Caption..Export AP to Text File

further down in Properties,
double-click in white space
beside "On Click"

you should now have "[Event Procedure]"
in white space, with little box to the right
of the white space.

click on that box (with 3 dots)

You now should be in VBA window
with cursor blinking in middle of


Private Sub cmdExportAP_Click()

End Sub

"in the middle," paste following code

'***start code***
On Error GoTo Err_cmdExportAP_Click
Dim varResponse As Variant
Dim strMsg As String
Dim strPath As String
Dim strQryName As String
Dim strSpec As String

strMsg = "Do you wish to export AP Pmts to text file?"
varResponse = MsgBox(strMsg, vbOKCancel)
If varResponse = vbCancel Then
' User chose to Cancel
MsgBox "You chose to cancel export."
GoTo Exit_cmdExportAP_Click
Else
'do it
strMsg = "Please enter full export path " _
& "and filename (C:\xxx.txt)"
strPath = InputBox(strMsg, "Export", strPath)

strQryName = "AP Payment Export"
strSpec = "??????"

'run export
If fExportSpec(strPath, strQryName, strSpec) = True Then
MsgBox "Successfully exported to " & strPath
Else
MsgBox "Export Failed!"
End If

End If

Exit_cmdExportAP_Click:
Exit Sub

Err_cmdExportAP_Click:
MsgBox Err.Description
Resume Exit_cmdExportAP_Click
'***end code***

replace "??????" with actual name of spec in line

strSpec = "??????"

Save and compile.

Save form and open, click on button.

Now you know how to add this button
to any form (I assume you don't really
need this separate form).