July 15, 2013 at 1:32 pm
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
July 15, 2013 at 2:45 pm
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/
July 16, 2013 at 6:57 am
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!
July 17, 2013 at 7:38 am
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.
July 17, 2013 at 9:38 am
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
July 17, 2013 at 9:52 am
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")
July 17, 2013 at 10:23 am
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
July 17, 2013 at 10:33 am
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.
July 17, 2013 at 10:48 am
My appologies. The function to return the logged in user is:
Environ("USERNAME") not "UserID"
July 17, 2013 at 11:46 am
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?
July 17, 2013 at 12:03 pm
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.
July 17, 2013 at 12:06 pm
The correct column is picked therefore I don't believe that is the issue.
What else could I be missing?
July 17, 2013 at 12:33 pm
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
July 17, 2013 at 1:12 pm
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?
July 17, 2013 at 1:41 pm
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