Newbie needs to track updates!

[LarryEEEE]

Hi...I'm working blind with VBA and need some guidance.
I have a Personnel Form with several subforms. I need to keep track of who updates data and when. I have built a table called "tblUpdates" with Fields "datUpdate", "strUser" and "strField" and an AutoForm from it called "FormUpdates". I would like to keep a record of activity in this table.

In my personnel form on my "strLastName" OnChange Event I have written the following:

--------------------------------
Private Sub strLastName_Change()

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblUpdates&quot
DoCmd.OpenForm "FormUpdates"
rec.MoveLast
rec.MoveFirst

rec.Edit
rec.AddNew
rec!datUpdate = Now()
rec!strUser = CurrentUser
rec!strField = "Last Name"
rec.Update

End Sub
_____________

This gives me an Error 3021 "No current User". I know I'm not even close to getting it right and would really appreciate if someone can put me on the right track.

 

[rushdib]

You can use the following function to receive the user name from the system

Declare Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" _
(ByVal lpName As String, ByVal lpUserName As String, _
lpnLength As Long) As Long

Function Getname() As String
Dim strUserName As String
Dim intValidChar As Integer

strUserName = Space(30)
WNetGetUser "", strUserName, 30
intValidChar = 1
Do
If ((Mid(strUserName, intValidChar, 1) >= "a&quot And _
(Mid(strUserName, intValidChar, 1) <= &quot;z&quot) Or _
(Mid(strUserName, intValidChar, 1) = &quot;_&quot Then
intValidChar = intValidChar + 1
Else
Exit Do
End If
Loop
Getname = Left(strUserName, intValidChar - 1)

End Function

 

[Databaseguy]

Your code would then look like this:

Private Sub strLastName_Change()

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblUpdates")
DoCmd.OpenForm "FormUpdates"
 rec.MoveLast
 rec.MoveFirst

rec.Edit
rec.AddNew
rec!datUpdate = Now()
rec!strUser = GetName()
rec!strField = "Last Name"
rec.Update

End Sub

Also, are you adding or editing. You've got both the methods in your code. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[rushdib]

You can use the event in the form &quot;On Enter&quot; when you are adding new records and &quot;Before Update&quot; when you are editing records.

 

[Guest_imported]

CORRECTION... the errror I get is &quot;No current record&quot;, not &quot;no current user.&quot; I'm sorry.

 

[MichaelRed]

Private Sub strLastName_Change()

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblUpdates")
DoCmd.OpenForm "FormUpdates"
' rec.MoveLast
' rec.MoveFirst

'rec.Edit
rec.AddNew
rec!datUpdate = Now()
rec!strUser = CurrentUser
rec!strField = "Last Name"
rec.Update

End Sub

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over