Update Field to current logged in user ID with new record command button

  • 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

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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!

  • 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.

  • 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

  • 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")

  • 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

  • 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.

  • My appologies. The function to return the logged in user is:

    Environ("USERNAME") not "UserID"

  • 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?

  • If your form is "bound" to a recordsource (table or view), the textbox's control source would be the name of the column in the recordsource that will receive the value. You can open the textbox properties and click on the dropdown for the control source to see a list of columns. Then just pick the correct one.

  • The correct column is picked therefore I don't believe that is the issue.

    What else could I be missing?

  • You haven't indicated that the line of code:

    Me.txtUserID = Environ("UserName")

    is executing. You will have to figure that out.

    I would take out the line:

    On Error Resume Next

    Also, set VB Editor to "Break on all Errors"

    Try this code instead of what you have:

    Private Sub cmdNewRecord_Click()

    On Error GoTo cmdNewRecord_Click_Err

    If Not .NewRecord Then DoCmd.RunCommand acCmdRecordsGoToNew

    Me.txtUserID = Environ("UserID")

    cmdNewRecord_Click_Exit:

    Exit Sub

    cmdNewRecord_Click_Err:

    MsgBox Err.Number & " - " & Err.Description

    Resume cmdNewRecord_Click_Exit

    End Sub

  • Mr Brush,

    The line of code is definitely not executing.

    I added break on all errors and the vb editor doesn't pop up.

    Also if I set the default value of the property for the txtUserID box to Functions/Built In Functions/Database =CurrentUser() then create a new record it writes it as Admin in the text box. I am not logged into the database nor my computer as Admin so I'm not sure where it is pulling this data.

    Please let me know if you have any other thoughts?

  • I can't help you get the line of code to execute, since I cannot debug it myself.

    The reason CurrentUser() returns Admin is that CurrentUser() is returning the Access security user, which is by default, "Admin."

    Here is another option...

    Forget the Me.txtUserID = code in the cmdNew_click sub.

    Add the following function to a VB Module (not a form class module):

    Public Function GetWindowUser() as String

    GetWindowUser = Environ("USERNAME")

    End Function

    Then in the txtUserID Default property (instead of CurrentUser()), put:

    =GetWindowUser()

    That should work as an alternative.

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply