Hello:
I have another issue. I am working on a database project using a Microsoft Access Project (.adp) as a front-end and SQL Server 2000 on the back-end. I (and users) am using Windows NT authentication to log into the SQL Server database.
I have a main form that contains some sub-forms, and together they function as a data-entry form for entering new records into database.
On one of the sub-forms, I have a field called 'AdminID' located in a table called FundSites. This field records the current system user who entered the new record.
I have a code snippet below that refers to the BeforeUpdate event for the sub-form that contains the AdminID field in a text box on the sub-form:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim cnn As ADODB.Connection
Dim rst As Recordset
Dim strSQL As String
' Return reference to current database
Set cnn = Application.CurrentProject.Connection
strSQL = "SELECT * FROM dbo.FundSites"
Set rst = CreateObject("ADODB.Recordset"
rst.Open strSQL, cnn, adOpenForwardOnly, adLockPessimistic
Me![AdminID] = CurrentUser()
rst.Update
rst.Close
cnn.Close
Set cnn = Nothing
End Sub
Notice that for the AdminID field on the sub-form within the recordset, I state in the code:
Me![AdminID] = CurrentUser ()
This is the line of code that I what to change. Instead of the Access 2000 CurrentUser () function, I want the value to equal SQL Server's SYSTEM_USER function.
How can I make that happen? How can I change the ADO code above so that:
Me![AdminID] = the current system user in SQL Server 2000
Plus, the (SYSTEM_USER) value gets stored into the table when a new record is inserted.
I could use the SQL Server SYSTEM_USER function as follows:
SELECT system_user AS AdminID
It returns a value (e.g. domain name/user name). Correct? Could I use a stored procedure?
Any assistance would be greatly appreciated.
Thanks,
Cheryl
I have another issue. I am working on a database project using a Microsoft Access Project (.adp) as a front-end and SQL Server 2000 on the back-end. I (and users) am using Windows NT authentication to log into the SQL Server database.
I have a main form that contains some sub-forms, and together they function as a data-entry form for entering new records into database.
On one of the sub-forms, I have a field called 'AdminID' located in a table called FundSites. This field records the current system user who entered the new record.
I have a code snippet below that refers to the BeforeUpdate event for the sub-form that contains the AdminID field in a text box on the sub-form:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim cnn As ADODB.Connection
Dim rst As Recordset
Dim strSQL As String
' Return reference to current database
Set cnn = Application.CurrentProject.Connection
strSQL = "SELECT * FROM dbo.FundSites"
Set rst = CreateObject("ADODB.Recordset"
rst.Open strSQL, cnn, adOpenForwardOnly, adLockPessimistic
Me![AdminID] = CurrentUser()
rst.Update
rst.Close
cnn.Close
Set cnn = Nothing
End Sub
Notice that for the AdminID field on the sub-form within the recordset, I state in the code:
Me![AdminID] = CurrentUser ()
This is the line of code that I what to change. Instead of the Access 2000 CurrentUser () function, I want the value to equal SQL Server's SYSTEM_USER function.
How can I make that happen? How can I change the ADO code above so that:
Me![AdminID] = the current system user in SQL Server 2000
Plus, the (SYSTEM_USER) value gets stored into the table when a new record is inserted.
I could use the SQL Server SYSTEM_USER function as follows:
SELECT system_user AS AdminID
It returns a value (e.g. domain name/user name). Correct? Could I use a stored procedure?
Any assistance would be greatly appreciated.
Thanks,
Cheryl