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