Rename query results before emailing to helpdesk

[Rhaps]

Hi, I know there are a number of requests for help on this but I
couldn't get them to work well enough for me, I am very new to this so
please be kind.....and yes I have tried and tried and tried the Rename,
Name, etc with little success more frustration.

What I have is a file that is produced from our database with the
information which needs to be uploaded to an Oracle system for payment.
I have a query which produce's a file to export in the csv format, the
problem here is that helpdesk require that the file include the GrpID
so they can identify it amongst the hundreds others they get, this
GrpID changes with each file as it is a pay calendar based id.

Any ideas how to add in the GrpID from the query/table files? or am I
totally approaching this the wrong way and there is a better way to
accomplish what I need to do?.

Really any help would be much appreciated...

 

[John Spencer]

Well, we don't really know what you are doing.

Are you using VBA to export the query results? or are you doing the process
manually?

Do you want to add the GrpID to the file name or to all the records in
exported file or just as a header line?

If you are using VBA to do this, could you post the code you are using?

 

[Rhaps]

Hi, sorry about not replying sooner its Pay week so pretty busy, I
should have said that I initially setup a macro, but now I have
coverted it to vba hoping to have better luck there... I would like to
add the GRPID to the end of the file name only ie
AP_Upload_GRPCL300906.csv. Then I would like to email the file, or can
I just change the name of the query results and email that?

Here is the code, it is very basic..
Function Macro___Export_Data()
On Error GoTo Macro___Export_Data_Err

DoCmd.Echo True, "Export is Preparing Data"
DoCmd.TransferText acExportDelim, "Z Export - Final Data to Export
Export Specification", "Z Export - Final Data to Export",
"G:\Corporate\Finance\Payroll\Deductions\AP_Upload.csv", False, ""
Beep
MsgBox "Data has been exported to :
G:\Corporate\Finance\Payroll\Deductions\AP_Upload.csv", vbInformation,
""


Macro___Export_Data_Exit:
Exit Function

Macro___Export_Data_Err:
MsgBox Error$
Resume Macro___Export_Data_Exit

End Function
Thank You

 

[John Spencer]

You could just rename the file manually and mail that.

How do you know the name "GRPCL300906" of the group? If it is not in the
system then you could prompt for it.

Function Macro___Export_Data()
On Error GoTo Macro___Export_Data_Err

Dim strFileName as String

strFileName = Inputbox ("Enter Group ID","Get group","GRPCL300906")

IF Len(strFileName) = 0 then
Beep
MsgBox Group id is required
exit function
End if

strFileName = "G:\Corporate\Finance\Payroll\Deductions\AP_Upload" & _
strFileName & ".csv"

DoCmd.Echo True, "Export is Preparing Data"
DoCmd.TransferText acExportDelim, _
"Z Export - Final Data to Export Export Specification", _
"Z Export - Final Data to Export", _
"G:\Corporate\Finance\Payroll\Deductions\" & strFileName, _
False

Beep
MsgBox "Data has been exported to : " & strFileName, vbInformation
Macro___Export_Data_Exit:
Exit Function

Macro___Export_Data_Err:
MsgBox Error$
Resume Macro___Export_Data_Exit

End Function

 

[Rhaps]

Hi, thank you for your reply, this is great I can use this. The reason
I can not manually rename the file is because the database will be
managed by different personnel and to avoid naming errors of the file
to be uploaded I would like to have the group id extracted from the
query or original table.

I can manage with the input box for now but if you know how I can
extract the id from the query or a table in the database that would be
superb.

Again thank you for you help, very much appreciated.