Storing the Current System User in SQL Server DB via Form

[Cheryl3D]

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&quot
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

 

[tlbroadbent]

Here is one method that works. There may be better ways to do this.

1) Create a view to get the system_user. I named it vSysUser.

SELECT SYSTEM_USER AS AdminID

2) In the form open event add the following command.

Me.AdminID = DLookup("[AdminID]", "vsysuser&quot If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[cmmrfrds]

I usually put a create date, modify date, and user id on my tables. You can put default values on the sql server tables for the create date and user id and then update the values in a trigger when the record is modified. This way the application logic does not get involved and maybe get missed on one of the updates.

default userid set to suser_sname()
default createDate set to getdate()

In update trigger.
Set modifyDate to getdate()
and
userid to suser_sname()

 

[Cheryl3D]

Hi tbroadbent:

I tried to use you method, but it did not work. I created a view called 'getAdminID_View' based on the SELECT SQL statement as you prescribed.

I added the code to the form's (sub-form's) on_open event:
Me.AdminID = DLookup("[AdminID]", "getAdminID_View&quot

When I tried to open the main form (containing the sub-form)
I received the following error message:

You can't assign a value to this object.

Even when I put the same line of code in the BeforeUpdate event for the sub-form as follows:

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&quot
rst.Open strSQL, cnn, adOpenForwardOnly, adLockPessimistic

Me.AdminID = DLookup("[AdminID]", "getAdminID_View&quot

rst.Update
rst.Close
cnn.Close
Set cnn = Nothing
End Sub

It still did not work. The value inserted into the table's AdminID field was still "Admin".

Any suggestions? And thanks for the help.

Cheryl

 

[tlbroadbent]

I should have paid more attention to your first post. I think your basic design is seriously flawed.

You open a recordset to return all the columns and rows in the table. You try to set a value of an object on the form. Then you use the update method on the record set without changing anything in the record set. This accomplishes nothing.

Is the form bound to the table or a view? If the form is bound to the table or a view there is no need to open another record set. Simply update the form and the controls on the form such as the text box. Is the AdminID bound to a column on a table or unbound?

I assumed Me.AdminID was an unbound control that you would use to store the SQL USER name. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Cheryl3D]

Hi:

The form is bound to a view. Also, I forgot to mention that the AdminID textbox is not visible. It's simply used to store the value of the AdminID field to pass to the table.

The AdminID textbox on the form is bound to a column in the view(table).

Please explain further what you mean by "Simply update the form and the controls on the form such as the text box."

Can you provide an example?

Thanks,
Cheryl

 

[tlbroadbent]

If the form is bound to a view and the view is updatable, when changes are made to the fields on the form, the record on the table will be updated.

Is AdminID control enabled? Is it also bound to the view? Are the other fields on the form updatable? Is the view updatable? If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm