Gasman
Enthusiastic Amateur
- Local time
- Today, 02:00
- Joined
- Sep 21, 2011
- Messages
- 14,299
Hi all,
I have cocked up big time.
I was trying to add a date and time stamp for created, amended and by whom.
I first had individual code in two forms and added the new controls and used code like
However I was a little tight for space on some forms, so *thought* I could do it via SQL in a sub as below
I tested it in another database but just from the immediate window.
Putting it in the needed DB I got the error message that could not update at this time (as i was on the record and calling the code from the Form BeforeUpdateEvent.:banghead::banghead::banghead:
So I have corrupted a record whose keys are linked to other tables, but I thought I could repair at the table level by keying in the correct values for the links, but I get the error message every time I do so and try and save the table.
I have a backup and will go back to that, but want to know if there is another way and what I might possible have done. I've clsoed Access and reopened, but it still happens. Even tried directly on the back end with same result.
TIA
I have cocked up big time.
I was trying to add a date and time stamp for created, amended and by whom.
I first had individual code in two forms and added the new controls and used code like
Code:
Me.CreatedBy = ENVIRON("UserName")
Me.Created = NOW()
I tested it in another database but just from the immediate window.
Putting it in the needed DB I got the error message that could not update at this time (as i was on the record and calling the code from the Form BeforeUpdateEvent.:banghead::banghead::banghead:
So I have corrupted a record whose keys are linked to other tables, but I thought I could repair at the table level by keying in the correct values for the links, but I get the error message every time I do so and try and save the table.
I have a backup and will go back to that, but want to know if there is another way and what I might possible have done. I've clsoed Access and reopened, but it still happens. Even tried directly on the back end with same result.
TIA
Code:
Sub UpdateTimeStamp(pTable As String, pKey As String, pKeyValue As Long, pAction As String)
' This will update relevant table with Created Date and user or Amended Date and user
On Error GoTo ErrHandler
Dim strSQL As String, strNow As String
strNow = Format(Now(), strcJetDateTime)
strSQL = "Update " & pTable & " "
Select Case UCase(pAction)
Case "ADD"
strSQL = strSQL & "SET Created = " & strNow & ",CreatedBy = '" & Environ("UserName") & "'"
Case "EDIT"
strSQL = strSQL & "SET Amended = " & strNow & ",AmendedBy = '" & Environ("UserName") & "'"
End Select
strSQL = strSQL & " WHERE " & pKey & " = " & pKeyValue
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
Exit_Sub:
DoCmd.SetWarnings True
Exit Sub
ErrHandler:
MsgBox "Error " & Err.Number & " " & Err.Description & " in UpdateTimeStamp module"
Resume Exit_Sub
End Sub
Attachments
Last edited: