Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2011
    Posts
    9

    Unanswered: Afterupdate macro help

    I have an access database in which every time someone updates a field I want three actions to occur.

    1. I want to populate a field called "last modified" to reflect the current date time. I already have the a macro that when an button is clicked, it will populate the field with the current time. But to simplify the process and eliminate users forgeting to click it. I want it to automatically do this every time a field is modified.

    2. I want to call a function that gets the user's windows logon name (called "getuser()") and populate it into a field called "current user". I already have a working funtion that once again, when someone clicks a button, it performs this operation.

    3. I want to take the modifiy record and store it as a new record in a table called History. I was think I would prefer to have this action occur only when the record selection changes or the database is closed, but I don't think upon "database close" is a valid argument.

    Thank you in advance for any suggestion.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by sterikics View Post
    1. I want to populate a field called "last modified" to reflect the current date time. I already have the a macro that when an button is clicked, it will populate the field with the current time. But to simplify the process and eliminate users forgeting to click it. I want it to automatically do this every time a field is modified.

    2. I want to call a function that gets the user's windows logon name (called "getuser()") and populate it into a field called "current user". I already have a working funtion that once again, when someone clicks a button, it performs this operation.
    This will do:
    Code:
    Private Sub Form_Dirty(Cancel As Integer)
    
        Me.[last modified].Value = Now
        Me.[current user].Value = GetUser
    
        
    End Sub
    However it will happen only once for each record being modified, i.e. if you modify 2 fields in the same record, the value of the TextBox will change only when the first field is being modified and not for every field.

    Quote Originally Posted by sterikics View Post
    3. I want to take the modifiy record and store it as a new record in a table called History.
    For this you'll need to create a procedure (let's call it SaveToHistory):
    Code:
    Private Sub SaveToHistory()
    
        Dim strSQL As String
        Dim strValues As String
        Dim fld As DAO.Field
        
        
        For Each fld In Me.Recordset.Fields
            If Len(strSQL) > 0 Then
                strSQL = strSQL & ", "
                strValues = strValues & ", "
            End If
            strSQL = strSQL & fld.Name
            Select Case fld.Type
                Case dbText, dbMemo:    strValues = strValues & "'" & Nz(fld.Value, "") & "'"
                Case dbDate:            strValues = strValues & "#" & Format(fld.Value, "mm/dd/yyyy") & "#"
                Case Else:              strValues = strValues & fld.Value
            End Select
        Next fld
        strSQL = "INSERT INTO History (" & strSQL & ") VALUES (" & strValues & ");"
        CurrentDb.Execute strSQL, dbFailOnError
        
    End Sub
    Now you can call SaveToHistory from the Form AfterUpdate event handler:
    Code:
    Private Sub Form_AfterUpdate()
    
        SaveToHistory
        
    End Sub
    Form_AfterUpdate will be called every time the form saves a record.

    Quote Originally Posted by sterikics View Post
    ... but I don't think upon "database close" is a valid argument.
    That's true but Form_Close is, and a form is closed when the database is closed.

    Note: You should refrain from using spaces or non-alphanumeric characters (except the underscore "_" for naming the objects (table names, column names, control names, etc.) in your databases. Sooner or later, this will be a source of problems.
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    Code:
    Private Sub Form_Dirty(Cancel As Integer)
    
        Me.[last modified].Value = Now
        Me.[current user].Value = GetUser
    
        
    End Sub
    Worked great if I tied it into an AfterUpdate() in Access 2007. Modified "Sub Form_Dirty(Cancel As Integer)" with "Sub <Field>_AfterUpdate()". Each time a field is changed it will populate the Last_Modified with current machine time and the Current_User to the windows logon using the Function FOSGetUser() shown below:
    Code:
    Function fOSGetUser() 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
            fOSGetUser = Left$(strUserName, lngLen - 1)
        Else
            fOSGetUser = vbNullString
        End If
    End Function
    Thank you so much for the help, Sinndho.

    Question: Does Form_Close apply when someone changes the record selection either manually via the navigation bar at the bottom of the form or thru a search for record macro? Also, by tieing it to a Form_AfterUpdate() will that create a new record in History every time a field is updated leading to lots of records being created in History? Finally, if these assumtions are correct, is there a way to set a switch that when a record is modified and then later close/switched it creates the record in History?

  4. #4
    Join Date
    Nov 2011
    Posts
    9
    Code:
    Private Sub Form_AfterUpdate()
        SaveToHistory
    End Sub
    To answer my previous question. This Module does only applies after the record changes or the form is closed. However, I get an Error:

    Compile error:
    Expected variable or procedure, not module

    I think it wants SaveToHistory(), however doing this it gives me an error: Expecting =

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is probably because you have a module and a procedure (sub or function) with the same name.
    Have a nice day!

  6. #6
    Join Date
    Nov 2011
    Posts
    9
    That is the case, Module name SaveToHistory contains Private Sub SaveToHistory(). I renamed the Module SaveToHistory to Save_To_History and renamed the function SaveToHistory() to CopyHistory(). Tried, to call Save_To_History, Save_To_History(), CopyHistory, and CopyHistory(). All give the same error.
    Last edited by sterikics; 11-07-11 at 14:02.

  7. #7
    Join Date
    Nov 2011
    Posts
    9
    Found my problem. I didn't replace the word "Form" in the line Form_AfterUpdate() with the name of the form I am using the expresion on. However, it is not copying the record to History. No errors are given.

    In the Form_AfterUpdate(), am I suppose to be calling the Module as Save_To_History, or the function as CopyHistory()?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by sterikics View Post
    Found my problem. I didn't replace the word "Form" in the line Form_AfterUpdate() with the name of the form I am using the expresion on. However, it is not copying the record to History. No errors are given.
    You should not: whatever the name of the form is, the event handler for the AfterUpdate event of a form is always named "Form_AfterUpdate". If you rename it, the AfterUpdate event will not be handled. This explain why nothing is written into the History table, the call to the procedure "SaveToHistory" never occurs.

    Quote Originally Posted by sterikics View Post
    In the Form_AfterUpdate(), am I suppose to be calling the Module as Save_To_History, or the function as CopyHistory()?
    As a general rule, you always call a procedure (Sub or Function), never a module.

    To simplify:

    1. Keep the name "Form_AfterUpdate" in:
    Code:
    Private Sub Form_AfterUpdate()
    2. Keep the name "SaveToHistory" in:
    Code:
    Private Sub SaveToHistory()
    3. Rename the module where "SaveToHistory" resides into: "Mod_SaveToHistory".
    Have a nice day!

  9. #9
    Join Date
    Nov 2011
    Posts
    9
    Was getting a different error, but found it was caused by an empty Private Sub Form_AfterUpdate. I removed the empty code but am still getting an error:

    Compile error:
    Sub or Function not defined

    Here are all the code I have so far:

    Unlabel Expressions via Expression Builder:
    Code:
    Option Compare Database
    Sub Combo22_AfterUpdate()
            Set rs = Me.RecordsetClone
            rs.FindFirst "[Serial Number] = " & Me.Combo22
            If rs.NoMatch Then
                DoCmd.GoToRecord acNew
            Else
                'Display the found record in the form.
                DoCmd.GoToControl "[Serial Number] = " & Me.Combo22
            End If
            Set rs = Nothing
    End Sub
    
    Private Sub Asset_Type_AfterUpdate()
            Me.Last_Modified.Value = Now
            Me.Current_User.Value = fOSGetUser()
    End Sub
    
    Private Sub Classification_AfterUpdate()
            Me.Last_Modified.Value = Now
            Me.Current_User.Value = fOSGetUser()
    End Sub
    
    Private Sub Condition_AfterUpdate()
            Me.Last_Modified.Value = Now
            Me.Current_User.Value = fOSGetUser()
    End Sub
    
    
    
    Private Sub Location_Bldg_AfterUpdate()
            Me.Last_Modified.Value = Now
            Me.Current_User.Value = fOSGetUser()
    End Sub
    
    Private Sub Location_misc_AfterUpdate()
            Me.Last_Modified.Value = Now
            Me.Current_User.Value = fOSGetUser()
    End Sub
    
    Private Sub Location_Room_AfterUpdate()
            Me.Last_Modified.Value = Now
            Me.Current_User.Value = fOSGetUser()
    End Sub
    
    Private Sub Model_Desc_AfterUpdate()
            Me.Last_Modified.Value = Now
            Me.Current_User.Value = fOSGetUser()
    End Sub
    
    Private Sub Model_Number_AfterUpdate()
            Me.Last_Modified.Value = Now
            Me.Current_User.Value = fOSGetUser()
    End Sub
    
    Private Sub Part_Number_AfterUpdate()
            Me.Last_Modified.Value = Now
            Me.Current_User.Value = fOSGetUser()
    End Sub
    
    Private Sub Serial_Number_AfterUpdate()
            Me.Last_Modified.Value = Now
            Me.Current_User.Value = fOSGetUser()
    End Sub
    
    Private Sub Form_AfterUpdate()
    
        SaveToHistory
        
    End Sub
    Module GetUser
    Code:
    Option Compare Database
    
    Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
        "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    Function fOSGetUser() 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
            fOSGetUser = Left$(strUserName, lngLen - 1)
        Else
            fOSGetUser = vbNullString
        End If
    End Function
    Module Mod_SaveToHistory
    Code:
    Option Compare Database
    
    Private Sub SaveToHistory()
    
        Dim strSQL As String
        Dim strValues As String
        Dim fld As DAO.Field
        
        
        For Each fld In Me.Recordset.Fields
            If Len(strSQL) > 0 Then
                strSQL = strSQL & ", "
                strValues = strValues & ", "
            End If
            strSQL = strSQL & fld.Name
            Select Case fld.Type
                Case dbText, dbMemo:    strValues = strValues & "'" & Nz(fld.Value, "") & "'"
                Case dbDate:            strValues = strValues & "#" & Format(fld.Value, "mm/dd/yyyy") & "#"
                Case Else:              strValues = strValues & fld.Value
            End Select
        Next fld
        strSQL = "INSERT INTO History (" & strSQL & ") VALUES (" & strValues & ");"
        CurrentDb.Execute strSQL, dbFailOnError
        
    End Sub
    Last edited by sterikics; 11-07-11 at 18:16.

  10. #10
    Join Date
    Nov 2011
    Posts
    9
    Copied and pasted the code provided ariginally and errors were resolved. However, Table History is still not being updated.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Set a breakpoint (or a Stop instruction) in the Form_AfterUpdate procedure and see if it's executed when the corresponding event occurs:
    Code:
    Private Sub Form_AfterUpdate()
    Stop
        SaveToHistory
        
    End Sub
    Have a nice day!

  12. #12
    Join Date
    Nov 2011
    Posts
    9
    I added the stop command as prescribed. Now I am getting a Compile Error: Sub or Function not defined. The debugger highlights SaveToHistory in blue, but once I close the error, it highlights Private Sub Form_AfterUpdate() in yellow and has a yellow arrow pointing to that line. I do not see any evedience that the Stop command is being ran. Removing the Stop command results in same complilation error.

    Removing SaveToHistory and keeping the stop comand brings up the MVB window after the event occurs, highlighting the stop command in yellow. So I assume that is the desired result of the Stop command.

    I copied the code for the SaveToHistory function into the Form_AfterUpdate() with no Stop or calling function SaveToHistory and it works. Figuring out how to get the fuction to call correctly would be nice, but I currently have a working product. Thank you so much for all the help. If you wish to continue working this, I would appreciate further idea's on how to get the fuction to call properly.
    Last edited by sterikics; 11-14-11 at 12:15.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •