Update user name in table automatically

[Rakesh935]

Hi,

Requesting help for the below mentioned:

Can we update the user name/computer name into an exisiting access table for the top 10 records via SQL query.

If yes, then please help me in building the query else requesting to provide suggestion for further alternatives to update.

Table Name: "tblWorked File"

Note:
It would be great if the update process can get executed automatically.

Thank you,
Rakesh

 

[apr pillai]

Hope you are using Access2007 or later version. If so you can do this.

We need a small VBA Function to set two Temporary Variables to assign User Name and Computer Name. We could have done this through a Macro but the Environ() function is not recoganized by Macro.
We need a macro also to automate the updating process.

Copy the following VBA Code into a Standard Module and save the Module:

Public Function defNames()
  'Save User Name to variable usrName
  TempVars!usrname = CurrentUser
  'Save Computer Name into the variable computer
  TempVars!computer = Environ("ComputerName")
End Function

Create a Query similar to the SQL given below with a sub-Query for criteria (in Amt field) with appropriate changes in field names:

UPDATE [tblWorked File] SET [tblWorked File].UserName = [tempvars]![usrname], [tblWorked File].ComputerName = [tempvars]![computer]
WHERE ((([tblWorked File].Amt) In (SELECT TOP 10  [tblWorked File].Amt
FROM [tblWorked File]
ORDER BY [tblWorked File].Amt DESC;)));

Finally, we need a macro to automate the updating process. Create a macro with commands as shown in the attached image.

Run the Macro from a Command Button Click. If you want the macro to run automatically immediately after the User opens the database then rename the macro as Autoexec.

 

[Galaxiom]

Environment variables are vulnerable to being manipulated by the user so are a poor choice for determining the computer and user. Better to retreive the actual data from the system.

Public Function LoginName()
 
     LoginName = CreateObject("wscript.network").UserName
 
End Function
 
Public Function ComputerName()
 
     ComputerName = CreateObject("wscript.network").ComputerName
 
End Function

 

[Rakesh935]

Hello,

Below are the fields that i have in the "tblWorked File"......

[Date], [BO_Name], [BO_STATUS],
[BO_COMMENTS], [BUSINESS PARTNER], [ORG_NAME],
[ORG_COUNTRY], [ORG_CITY], [ORG_LANGUAGE],
[TELEPHONE_CRM], [STREET], [DISTRICT],
[POSTL CODE], [WEB SITE], [ORG_TELEPHONE],
[ORG_FAX], [SIC_CODE], [DUNS_NUMBER], [SOURCE LINK],
[BO_Name1], [CP_STATUS], [CP_COMMENTS], [CP FIRST NAME],
[CP LAST NAME], [CP SALUTATION], [JOB TITLE], [CP EMAIL],
[CP TELEPHONE], [CP FIRST NAME1], [CP LAST NAME1],
[CP SALUTATION1], [JOB TITLE1], [CP EMAIL1],
[CP TELEPHONE1], [CP_SOURCE LINK]

Where "BO_Name" would be the User Name...

I tried re-modifying the query as provided but unfortunately things doesn't seems to work...and the replaced the Amt field with BUSINESS PARTNER as it is a unique id provided to us by customers....below is the query which i re-modified...

UPDATE [tblWorked File] SET [tblWorked File].UserName = [tempvars]![usrname], [tblWorked File].ComputerName = [tempvars]![computer]
WHERE ((([tblWorked File].[BUSINESS PARTNER]) In (SELECT TOP 10 [tblWorked File].[Date], [tblWorked File].[BO_Name], [tblWorked File].[BO_STATUS], [tblWorked File].[BO_COMMENTS], [tblWorked File].[BUSINESS PARTNER], [tblWorked File].[ORG_NAME], [tblWorked File].[ORG_COUNTRY], [tblWorked File].[ORG_CITY], [tblWorked File].[ORG_LANGUAGE], [tblWorked File].[TELEPHONE_CRM], [tblWorked File].[STREET], [tblWorked File].[DISTRICT], [tblWorked File].[POSTL CODE], [tblWorked File].[WEB SITE], [tblWorked File].[ORG_TELEPHONE], [tblWorked File].[ORG_FAX], [tblWorked File].[SIC_CODE], [tblWorked File].[DUNS_NUMBER], [tblWorked File].[SOURCE LINK], [tblWorked File].[BO_Name1], [tblWorked File].[CP_STATUS], [tblWorked File].[CP_COMMENTS], [tblWorked File].[CP FIRST NAME], [tblWorked File].[CP LAST NAME], [tblWorked File].[CP SALUTATION], [tblWorked File].[JOB TITLE], [tblWorked File].[CP EMAIL], [tblWorked File].[CP TELEPHONE], [tblWorked File].[CP FIRST NAME1], [tblWorked File].[CP LAST NAME1], [tblWorked File].[CP SALUTATION1], [tblWorked File].[JOB TITLE1], [tblWorked File].[CP EMAIL1], [tblWorked File].[CP TELEPHONE1], [tblWorked File].[CP_SOURCE LINK]
FROM [tblWorked File]
ORDER BY [tblWorked File].[BUSINESS PARTNER] DESC)));

All i want is to capture the username under BO_Name and then create a sub-form upon the query and in the sub-form only those records must reflect/visible for which the user name of the computer and the BO_Name in the table is matching.

Note:
I had changed the selection criteria from 10 to 1. And
I tring to capture the date also automatically (System date).

Please help me with this requirement.

Thank you,
Rakesh