Update Query Access 2003 using Global Variable

[sellert]

Greetings, I am trying to update a table with a global variable. Basically I have a form that gets the user name from the Windows environment and stores it to a global variable called "User". This part works becasue I use it to validate access to the database.

I am trying to update a table with the Users name when they perfrom an action and I just get a Parameter query prompt for the variable when I try to use it.

This is the what is entere into the Query grid:
Field: BUSR_ID_CREATE
Table: STSZ_TRANSACTIONS
Update To: [EPSI_SecurityModule].[User]

I have tried:
Field: BUSR_ID_CREATE
Table: STSZ_TRANSACTIONS
Update To: [EPSI_SecurityModule]![User]

I have tried:
Field: BUSR_ID_CREATE
Table: STSZ_TRANSACTIONS
Update To: EPSI_SecurityModule!User

I have tried:
Field: BUSR_ID_CREATE
Table: STSZ_TRANSACTIONS
Update To: [User]

I have tried referencing the environment directly:
Field: BUSR_ID_CREATE
Table: STSZ_TRANSACTIONS
Update To: UCase(Environ("UserName"))

Here is the Access SQL view of the query:
UPDATE
PO_HEADERS INNER JOIN STSZ_TRANSACTION ON PO_HEADERS.PO_NUMBER = STSZ_TRANSACTION.PO_NUMBER
SET
STSZ_TRANSACTION.PO_SEQNO = PO_HEADERS.PO_SEQNO, STSZ_TRANSACTION.STOCK_TYPE = 'PROJECT', STSZ_TRANSACTION.JOBNUMBER = '24590', STSZ_TRANSACTION.JOBLOCATION = 'B2459000',

STSZ_TRANSACTION.BUSR_ID_CREATE = [EPSI_SecurityModule].[User],

STSZ_TRANSACTION.DATA_ORIGIN = 'EPSI', STSZ_TRANSACTION.SHOPORFIELD = 'F', STSZ_TRANSACTION.DELIVERY_DESTINATION = 'Jobsite', STSZ_TRANSACTION.EXPEDITED = 0, STSZ_TRANSACTION.MATERIAL_USE = 'PP', STSZ_TRANSACTION.TWMATERIAL = 'BULK'
WHERE (((PO_HEADERS.PO_SUBTYPE) Not In ('REL','~','BO','SN')));

The isolated Text with blank line above and below in the SQL is the only part I am having problems with.
What am I doing wrong?


Thanks,
SELLERT

If I'm not learning I must be coasting. If I am coasting I must be going down hill.

 

[SoCalAccessPro]

If all your trying to do is retun the username, I do that all the time. The following SQL was just tested in a query and works fine. fosUserName is the name of the function that gets my user data.

SELECT [2006 USD PRINTER MODEL CNT].*, FOSUsername() AS UsersName
FROM [2006 USD PRINTER MODEL CNT];

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[lewds]

Hi..
Try turning the variable into a Function:

In a standard module create:

Public Function FnUser()
FnUser = Ucase(Environ("UserName"))
end Function

Then for the Query:
Field: BUSR_ID_CREATE
Table: STSZ_TRANSACTIONS
Update To: FnUser()

As shown, this will update all the records in the STSZ_TRANSACTIONS table to one USER name.

I am assuming that you have other criteria in the query to limit the update to a particular transation?

Hope this Helps......