Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Update Field to current logged in user ID with new record command button Expand / Collapse
Author
Message
Posted Monday, July 15, 2013 1:32 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
Hello all,


I have an access front end that uses a sql backend.

We want to add a field in a form so that when the NEW command button is clicked it will update the field in the data with the user that clicked the NEW command button and also change the default value of a text box to with that user id. This will allow for some data capture as to who created the record.

Currently in the database there is a table with the user list and this table has a LoginID field. This LoginID field contains the same data as their loginid to the sql server. For instance if I logged in as jsmith to the database there is also a record in this table with jsmith.

What is the best way this can be accomplished?

What events would I need to set in the command button?

Is there a DMV I can query to facilitate this?

Below is the table definition for the HRUsers table.


USE [GKHumanResource]
GO

/****** Object: Table [dbo].[HRUsers] Script Date: 7/15/2013 3:16:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[HRUsers](
[HRRepId] [varchar](50) NOT NULL,
[LoginId] [varchar](25) NULL,
CONSTRAINT [PK_HRUsers_HRRepID] PRIMARY KEY CLUSTERED
(
[HRRepId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



Post #1473869
Posted Monday, July 15, 2013 2:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:31 PM
Points: 13,207, Visits: 12,688
Assuming that each person who uses the application connects to the sql server with a unique login you could just query SYSTEM_USER.

select SYSTEM_USER

The above code will retrieve the username of the currently executing context.



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1473901
Posted Tuesday, July 16, 2013 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 5:46 PM
Points: 163, Visits: 601
We take a somewhat different approach to the issue of recording who created a record in a table. We define a column in each table called "WhoCreated" or something similar that has a default value set to suser_sname() - that captures the SQL Server login ID, as well as a date/time column indicating when it was created. Capturing who edits a record is more challenging - you can do that sort of thing on an Access form, but we choose to use triggers in SQL Server, and actually archive each edited record.

Wendell
Colorado, USA


Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Post #1474105
Posted Wednesday, July 17, 2013 7:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:41 AM
Points: 101, Visits: 323
Within Access you can use the Environ("UserName") function to get the user that is logged into Windows.

The suser_sname() may return the Domain/UserID if using Windows authentication.
Post #1474615
Posted Wednesday, July 17, 2013 9:38 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
Thanks for the suggestions but I have not been successful.

I used Environ("UserName"), suser_sname(), and select system_user to populate the value in the text box on the form.

I am setting this in the Control Source of the Textbox Data Properties. After doing so it returns a value of #Name? or Admin. I am no logged in as either of these ID's.

Then how do I also get the field to write this value to the record?

I have attached a couple of screen shots which will help provide further insight to where I am in access.

Basically this database will track HR related issues. So in regards to case creation the process will go as such:
1.) Create new case
2.) Access prompts user for a database login and password
3.) Once logged in a new form is given to the user.
4.) The Created By textbox is updated with the logged in user
5.) The CaseData table is updated with the HRRepID to which the logged in user is mapped.
-----in the casedata table there is a field called HRRepID and it has a foreign key relationship to a table named HRUsers. The HRUsers table has 2 fields HRRepID and LoginID. The LoginID is what is required to be in the Created By Textbox mentioned in step 4.
6.) The HR rep continues to complete the fields in the form the Created By text box cannot be changed.

Also below are the table definitions for CaseData and HRUsers.

Please take a look and let me know of some suggestions? Thanks.


USE [GKHumanResource]
GO

/****** Object: Table [dbo].[HRCaseData] Script Date: 7/17/2013 11:32:22 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[HRCaseData](
[Id] [int] IDENTITY(100,1) NOT NULL,
[CaseDate] [date] NOT NULL,
[Customer] [varchar](50) NOT NULL,
[HrRepId] [varchar](50) NOT NULL,
[Category] [int] NOT NULL,
[CaseDescription] [varchar](30) NOT NULL,
[CaseStatus] [bit] NOT NULL,
[ClosedDate] [date] NULL,
[Resolution] [varchar](1000) NULL,
CONSTRAINT [PK_ID_Customer_Category] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[Customer] ASC,
[Category] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[HRCaseData] WITH CHECK ADD CONSTRAINT [FK_Category_CatType] FOREIGN KEY([Category])
REFERENCES [dbo].[HRCategory] ([ID])
GO

ALTER TABLE [dbo].[HRCaseData] CHECK CONSTRAINT [FK_Category_CatType]
GO

ALTER TABLE [dbo].[HRCaseData] WITH CHECK ADD CONSTRAINT [FK_HRRep] FOREIGN KEY([HrRepId])
REFERENCES [dbo].[HRUsers] ([HRRepId])
GO

ALTER TABLE [dbo].[HRCaseData] CHECK CONSTRAINT [FK_HRRep]
GO

--************
--Table 2
--************

USE [GKHumanResource]
GO

/****** Object: Table [dbo].[HRUsers] Script Date: 7/17/2013 11:33:42 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[HRUsers](
[HRRepId] [varchar](50) NOT NULL,
[LoginId] [varchar](25) NULL,
CONSTRAINT [PK_HRUsers_HRRepID] PRIMARY KEY CLUSTERED
(
[HRRepId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO





  Post Attachments 
capture4.JPG (2 views, 12.76 KB)
capture5.JPG (2 views, 26.26 KB)
capture6.JPG (1 view, 50.17 KB)
Post #1474693
Posted Wednesday, July 17, 2013 9:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:41 AM
Points: 101, Visits: 323
Assuming your New Button name is "cmdNew" and your UserID Textbox Name is "txtUserID":

In the cmdNew_Click event, after the recordset is placed on the new record, add the code:

Me.txtUserID = Environ("USERID")
Post #1474701
Posted Wednesday, July 17, 2013 10:23 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434

I put the line of code in but it isn't updating the field and seems to have prevented record navigation command buttons.

Did I place the line you recommended in the correct position?


'------------------------------------------------------------
' cmdNewRecord_Click
'
'------------------------------------------------------------
Private Sub cmdNewRecord_Click()
On Error GoTo cmdNewRecord_Click_Err

On Error Resume Next
DoCmd.GoToRecord , "", acNewRec
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If

Me.txtUserID = Environ("UserID")


cmdNewRecord_Click_Exit:
Exit Sub

cmdNewRecord_Click_Err:
MsgBox Error$
Resume cmdNewRecord_Click_Exit




End Sub

Post #1474720
Posted Wednesday, July 17, 2013 10:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:41 AM
Points: 101, Visits: 323
It is in the right place.
I'm not sure why you have an On Error Resume Next.

Set a breakpoint in the code on the Me.txtUserID = Environ("USERNAME") by clicking on that line and pressing F9.

Then open your form and click the New button.

If the code execution does not stop on the breakpoint then that would explain why the control is not updating.

If code execution stops on the breakpoint, press F8 to execute that line and then hover over the "Me.txtUserID" and see what value it has.
Post #1474724
Posted Wednesday, July 17, 2013 10:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:41 AM
Points: 101, Visits: 323
My appologies. The function to return the logged in user is:
Environ("USERNAME") not "UserID"
Post #1474733
Posted Wednesday, July 17, 2013 11:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
No problem Mr. Brush. I picked up on that syntax.

I put the break point in and it isn't being touched.

The OnError Resume Next was put in by access. Its not my code.

Do I need to have anything in the control source of the txtUserID properties in Access?
Post #1474749
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse