Catch the current date, time and login name when record is being edited.

[AccessBeetle]

I have a qry bound form showing the search results based on the user entered criteria. There is a button called Edit on that form. The form is locked initally to stop user to edit any record accidentally.
When user clicks on edit, all the controls go unlocked and user can edit the record. There three fields on the form showing who entered data (ModeUserId, ModeDate, ModeTime). I want these fields to change there value accordingly(meaning it should show current date, time and user logged in)
How do I do that?

[missinglinq]

The code would be:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me.ModeDate = Date
  Me.ModeTime = Time 
  Me.ModeUserId = CurrentUserID
End Sub

The only question, though, is where are you going to get the value for CurrentUserID?

Do you want the Windows User ID or do your users have to log into the database, or what?

What version of Access are you running/going to be running this on?

Linq ;0)>

[AccessBeetle]

It is custon login interface so I will be catching that using dlookup function.
Hey but it did not change the values like it should have.
Access 2003, SP 2

[AccessBeetle]

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Provide the user with the option to save/undo
'changes made to the record in the form
 
    If MsgBox("Changes have been made to this record." _
        & vbCrLf & vbCrLf & "Do you want to save these changes?" _
        , vbYesNo, "Changes Made...") = vbYes Then
            DoCmd.Save
            Me.ModDate = Date
  Me.ModTime = Time
  Me.ModUserID = DLookup("[Username]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'")
 
        Else
            DoCmd.RunCommand acCmdUndo
    End If
 
 
End Sub

OK I changed the code like above. Now it shows correct ModUserId but date is not being edited at all and time is wrong. I mean it does not show the current time.It shows some garbage time. What is going on wrong here?

[AccessBeetle]

ok here is the solution.
1. Now() funtion with input mask works better.
2. Me.Refresh refreshes form with updated data.

Private Sub Form_AfterUpdate()
Me.Refresh
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Provide the user with the option to save/undo
'changes made to the record in the form
 
    If MsgBox("Changes have been made to this record." _
        & vbCrLf & vbCrLf & "Do you want to save these changes?" _
        , vbYesNo, "Changes Made...") = vbYes Then
            DoCmd.Save
            Me.ModDate = Now()
            Me.ModTime = Now()
            Me.ModUserID = DLookup("[Username]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'")
 
        Else
            DoCmd.RunCommand acCmdUndo
    End If
 
 
End Sub

[missinglinq]

If Date() and Time() didn't work for you it means that you've got a missing reference library. Since these are very basic functions in Access, and share the library with many others, you should keep this in mind if you start having problems with other functions failing.

Be aware that while you're seeing the date in ModDate and the time in ModTime, when looking at your form, in actuality both of these fields, in the underlying table, are going to show both date and time, which is what Now() returns. In other words, the data in ModDate and ModTime will always be identical, which violates the rules!

Also, DoCmd.Save saves design changes made to the form, it does not save changes made to the record! To save the record you need to use something like

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False

Linq ;0)>

[AccessBeetle]

Thanks for heads up!! I really appreciate your help. Thanks for telling otherwise I would have keep it that way.

If Date() and Time() didn't work for you it means that you've got a missing reference library.

How to fix it?

[missinglinq]

You’ll have to actually have the functions used in code, i.e. set

Me.ModDate = Date()

like you had it when it was misbehaving, then run it (to be sure it’s still acting up and wasn’t just a temorary Access burp; Access does that sometimes) then use Doug Steele’s detailed instructions on how to troubleshoot the problem:

http://www.accessmvp.com/DJSteele/Ac...nceErrors.html

Linq ;0)>

[AccessBeetle]

Thanks. I did write the function in code but as you have wrote it eliminnates the brackets. it only takes something like this.

 If MsgBox("Changes have been made to this record." & vbCrLf & vbCrLf & "Do you want to save these changes?", vbYesNo, "Changes Made...") = vbYes Then
            DoCmd.RunCommand acCmdSaveRecord
            Me.Text61 = Date
            Me.Text63 = Time
            Me.ModUserID = DLookup("[Username]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'")
 
        Else
            DoCmd.RunCommand acCmdUndo
    End If           

but the link you have given is for Access 2000 and Access 2002. I am using Access 2003 with SP 2. Let me know if you know the specific link for it.

Also, when I wrote the aboce code it give me runtime error 2115. Any solution for that?
THanks for all your help

[Megalog]

I'd remove the code to save the record.. since this is a BeforeUpdate event. Not sure why your date/time fields are getting funky though. Make sure that the fields in your table are not named "Date" or "Time", they're reserved words. And make sure any controls on your form are also not named that.

[AccessBeetle]

No I don't have any control named like "Date" or "Time". Also, my field names are ADate and ATime. When I use the code in Post #9, it gives me Runtime error MS Access can't find the field 'Time' referred to in your expression. The ADate field is updated with Me.Text61 = Date, but not the ATime field.

Also, I want to tell user that the record has been updated, that is why I put that code there? Is there anywhere else I can pput it which notifies and gives user an option if he wants to go ahead with the editing he made.

[missinglinq]

What event do you that code in? It needs to be in the Form_BeforeUpdate event, and as Megalog said, you really don't need the line to save the record. I think moving it to this event will probably solve the 2115 error, as well.

And Doug's advice on Reference troubleshooting applies to version 2003 as well. Not recognizing the Time() function is really indicative of a missing reference, as it is a standard Access function.

Linq ;0)>

[Megalog]

I would think having it save the record while in a BeforeUpdate event would cause the error, or send it into a loop of some sort.

I would use the code in post #5, just remove any line that saves the record.