Exporting queries with different file name (prompting if exists)

[Kath via AccessMonster.com]

Hello,
I am working on a system that exports pay information every pay period. I
need to export a file based on pay date (which I would like to pass the
parameter entered by the user to the file name, but..not sure how). I
currently have it naming the file per the investment company specs -but using
current date as the date (assuming she will run this on the exact day she
does payroll.)

What I would like is:
1) If the file exists, prompt her to state "File exists - replace?" also
if possible
2) Pass the parameter (pay date) she enters when she click the command button.


Any help would be greatly appreciated.
Thanks!



My code is this (for the first pay):

'------------------------------------------------------------
' Export 1st Pay
'
'------------------------------------------------------------
Function Export_1st_Pay()
On Error GoTo Export_1st_Pay_Err

Dim MyExportPath As String
MyExportPath = "s:\fa\payroll\pension\penfiles\fy06\"

DoCmd.TransferText acExportDelim, "", "401K REPORT - 1st Pay",
MyExportPath & "RHG0_" & Format(Now(), "yyyyMMdd") & ".csv", True, ""

Export_1st_Pay_Exit:
Exit Function

Export_1st_Pay_Err:
MsgBox Error$
Resume Export_1st_Pay_Exit

End Function

 

[Nikos Yannacopoulos]

Kath,

Try this:

Function Export_1st_Pay()
On Error GoTo Export_1st_Pay_Err

Dim MyExportPath As String
Dim MyPayDate As String
Dim MyResponse, msg, ttl, typ
MyExportPath = "s:\fa\payroll\pension\penfiles\fy06\"

MyPayDate = InputBox("Please enter date in format: yyyymmdd", _
"Enter Pay Date", Format(Date(), "yyyyMMdd"))
If Dir(MyExportPath & "RHG0_" & MyPayDate & ".csv" <> "" Then
msg = "Do you want to replace the existing file?"
ttl = "File already exists!"
typ = vbExclamation + vbYesNo
MyResponse = Msgbox(msg, typ, ttl)
If MyResponse = vbNo Then Exit Function
End If
DoCmd.TransferText acExportDelim, "", "401K REPORT - 1st Pay", _
MyExportPath & "RHG0_" & MyPayDate & ".csv", True, ""

Export_1st_Pay_Exit:
Exit Function

Export_1st_Pay_Err:
MsgBox Error$
Resume Export_1st_Pay_Exit

End Function

Note: the above approach makes use of an input box for the user to type
in a date in the required format (current date defaulted in); by nature,
this approach is vulnerable to typing errors. A safer and more
professional looking approach would be to create a simple form with a
calendar control, which pops up when the user runs the code, the user
selects a date with a mouse click, and the date is returned to the code.

HTH,
Nikos