TransferSpreadsheet Woes (1 Viewer)

[KevlarKev]

Hi guys,

I am using the transfer spreadsheet command in Access to export a query I have to my desktop. Here is the code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrySampleSubExport", "C:\Users\kevin.field\Desktop\Test.xls"

It all works perfectly, but now I need you help to make it functional.

The problem I have is that all of the users of the database are on a network and each have their own login names. Mine for example is kevin.field.

Is there any way that the VBA can get clever enough to know who is logged in and use their name instead? If not, is there a way I could fudge it by building a table with usernames that the VBA can reference depening on who is logged into the database? I already capture log in names on the database.

Help!

 

[JohnLee]

Hi,

I'm assuming that you are talking about a user being in a specific form view.

You can use the following to get the User:

Dim sUser As String 'Declare the sUser as String
 
sUser = Environ("UserName") 'Get the Logged on Users name

I use the above code to identify which user is in a particular record they are accessing within that form and populate a field with that information along with the time and date that record was amended.

I hope this helps.

Regards

John