I have a stored procedure that work

[ascheper]

I have a stored procedure that works fine on it's own, now I need to call it in the before update event of a control on my form and save the output in a database field. I've searched MS knowlege and this site for an answer first, but cannot seem to find it. I may not be asking the correct question, but any help would be greatly appreciated, thank you.

the stored proceedure simply gets the users login name and I want to store that in a field in the customer table that the the form being used is attached to. I want to place a call to the sp in the before update event of a control, presumably using docmd.openstoredprocedure, and save the resulting user name in another field. I tried the following, which does not work:

me.CallListUser = docmd.OpentoredProcedure "sp_GetUserName"

 

[nat1967]

HI,

I actually just got finished writing some code to do just what you are asking. (i believe) I had to laugh when I read your message. What a coincidence....

ok, I am getting the user login id with an API call, using a function to store the name, then in the Form_Load event, saving the name to a table.

'****begin code*****
'*****API Call for User Log In Name*******
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

'****Function to store Log In Name******
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = &quot;&quot;
End If

End Function

'****form Load event that takes the stored name
'****and saves it to a field in the table
Public Sub Form_Load()
Dim rs As DAO.Recordset
Dim db As DAO.Database


Set db = CurrentDb()

Set rs = db.OpenRecordset(&quot;tblUserlog&quot

With rs
.AddNew
.Fields(&quot;User&quot = fOSUserName
.Fields(&quot;Timein&quot = Date & &quot; &quot; & Time
.Update
End With

rs.Close

End Sub
'****end code******

I am also saving the Date and Time.

HTH

Nathan

 

[ascheper]

Sorry for the delay in reading this, but thank you for the tip, I'm going to give it a try...