August 4, 2003 at 3:23 pm
I am using a SQL Server 2000 database with a Microsoft Access 2000 form on the front end.
CREATE TABLE [dbo].[tsPtnrTyp] (
[PtnrTypId] [int] NOT NULL ,
[PtnrTypName] [varchar] (50) NOT NULL ,
[PtnrTypNote] [varchar] (500),
[PtnrTypStartDate] [datetime] NOT NULL DEFAULT GetDate(),
[PtnrTypIsActive] [varchar] (3) NOT NULL DEFAULT 'Yes',
[PtnrTypEndDate] [datetime] NOT NULL DEFAULT 9/9/9999,
[PtnrTypDateAdd] [datetime] NOT NULL DEFAULT GetDate(),
[PtnrTypUIDAdd] [varchar] (20) NOT NULL DEFAULT System_User,
[PtnrTypWrkStnAdd] [varchar] (20) NOT NULL DEFAULT Host_name,
[PtnrTypDateUpd] [datetime] NOT NULL DEFAULT GetDate(),
[PtnrTypUIDUpd] [varchar] (20) NOT NULL DEFAULT System_User,
[PtnrTypWrkStnUpd] [varchar] (20) NOT NULL Host_Name)
When a record is updated and saved, I want the last three columns (PtnrTypDateUpd, PtnrTypUIDUpd, and PtnrTypWrkStnUpd) to be updated to contain the current date/time, the user who updated record, and the workstation from where the record was updated. On the Access form there is a "Save" command button with the following code behind it:
Private Sub Save_Record_Click()
On Error GoTo Err_Save_Record_Click
Me!PtnrTypDateUpd = Now()
Me!PtnrTypUIDUpd = System_User
Me!PtnrTypWrkStnUpd = Host_Name
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, "frmPtnrTyp_Update"
The Access code will not accept "System_User" or "Host_Name", it gives an error message of "Variable not defined".
If the last two fields are coded as Me!PtnrtypUIDUpd = Null, Me!PtnrTypWrkStnUpd = Null , so it will take on the SQL Server table defaults when saved, it gives the error message of "You tried to assign the Null value to a variable that is not a Variant data type".
Any suggestions will be appreciated.
August 4, 2003 at 4:39 pm
Put the code in a trigger. If you really want this type of auditing you dont want it in client code where it could be bypassed.
Andy
August 4, 2003 at 11:45 pm
Hi nulad,
quote:
The Access code will not accept "System_User" or "Host_Name", it gives an error message of "Variable not defined".
like Andy suggested, put the code in an UPDATE trigger.
Access does not recognize System_User or Host_Name as an SQL Server function, but simply as a string to be inserted or, like in your case, a variable which hasn't been defined.
Cheers,
Frank
Edited by - a5xo3z1 on 08/04/2003 11:49:32 PM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 5, 2003 at 11:50 am
Thanks for the advice! Worked out great.
August 11, 2003 at 2:48 am
for System_User in SQL Server .. there is a function called CurrentUser() in Access
for Host_Name in SQL Server .. I don't know the same function in Access .. can anybody tell me ??
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
August 11, 2003 at 3:08 am
Hi alamir,
quote:
for System_User in SQL Server .. there is a function called CurrentUser() in Access
that's right! But unless you haven't turned on Access security, I think it will always return 'Admin' as current user.
This was also the topic here http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=15054
quote:
for Host_Name in SQL Server .. I don't know the same function in Access .. can anybody tell me ??
I don't know of such a function, but I know you can retrieve Windows environment variables on the workstation with ENVIRON("<your_variable>")
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply