Tracking Time

Hi all, 

i want to build a time tracking function in order to track each occasion a 
record in Table A is being updated. Table B stores the Time, Now(), Name, 
fOSUserName() and a field related to the Key field of table A.
When a record in the form related to table A is updated (on the Before 
update event) i want this to be recorded in Table B...
Question:
Do i need a separate form (not visible) to capture this data or can i code 
Form A, on the Before Update event or on the Close event, to update the 
related record in Table B?

Thanks!

0
Utf
5/7/2010 7:30:04 AM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
841 Views

Similar Articles

[PageSpeed] 13

Hi Peter

What you are trying to do is not complicated.  You use the before update 
event to write the change to a separate table.  I use two tables.  If it is 
not a memo field, it goes in one table where every changed field is treated 
as a text field.  If it is a memo field it goes in another table. 

I did a cut and paste from an application I used.  This is what is in each 
form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim intKey As Integer                                           ' 
Primary Key Value
    Dim strKeyName As String                                        ' Table 
name of the primary key in the form
    Dim strOptional As String                                       ' Option 
1 for additional data
    Dim strFormName As String                                       ' Full 
form name including reference to parent forms if a subform
        
    On Error GoTo Error_Form_BeforeUpdate
    
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  Change for each form 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    strKeyName = "tblPeople.PersonNo"                               ' Table 
Name of the field for the Primary key
    intKey = Me.PersonNo                                            ' PK 
value on the form
    strOptional = "Person changed was " & Me.txtName                ' 
Cancatenated Description
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    ' Find the form name.  Check if it is a subform and add the name to the 
string
    strFormName = Me.Name                                           ' Name 
of the form
    Set frmToCheck = Me                                             ' Name 
of this form
    
    ' Examine the form to see if it is a subform.  Create a cancatenated 
string of the form!subform name
CheckSubForm:
    If funIsSubForm(frmToCheck) = True Then                         ' Check 
if it is a subform
        strFormName = Me.Parent.Name & "!" & strFormName            ' Add 
the parent to the string
        GoTo CheckSubForm
    End If
   
    ' Run the update routine
    Call funLogTrans(Me, _
                    intKey, _
                    strFormName, _
                    strKeyName, _
                    strOptional)
                            ' Me is the form passing the information
                            ' MyKey is the value of the PK
                            ' strFormName is the name of the form being 
modified including full path for subforms
                            ' strKeyName is the name of the Primary Key 
field in the table e.g. "tblPeople.PersonNo"
                            ' strOptional1 is the cancatenated descriptive 
string.
                            
Exit_Form_BeforeUpdate:
    Exit Sub
    
Error_Form_BeforeUpdate:
    MsgBox "Error in Form_BeforeUpdate: " & Err.Number & " - " & 
Err.Description
    Resume Exit_Form_BeforeUpdate
End Sub

This is in a separate module.

Option Compare Database
Option Explicit
    Public frmToCheck As Form

Public Function funLogTrans(frm As Form, _
                            intKey As Integer, _
                            strFormName As String, _
                            strKeyName As String, _
                   Optional strOptional As String) _
                        As Boolean
                            ' Frm is the form passing the information
                            ' intKey is the value of the PK
                            ' strFormName is the name of the form being 
modified including full path for subforms
                            ' strKeyName is the name of the Primary Key 
field in the table e.g. "tblPeople.PersonNo"
                            ' strOptional1 is the cancatenated descriptive 
string.
Dim dbs As DAO.Database
Dim ctlCtrl As Control
Dim MyMsg As String
Dim strHist As String
Dim lngOldValue As Long
Dim lngNewValue As Long

' Loop through controls to find ones that changed
For Each ctlCtrl In frm.Controls
    If (funActiveCtrl(ctlCtrl)) Then                                 ' Check 
it is an updateable control
        If IsNoOldValue(ctlCtrl) = True Then                         ' Is 
the oldvalue valid for this control
            If ctlCtrl.Enabled = True Then                            ' Is 
the control enabled.
                If ((ctlCtrl.Value <> ctlCtrl.OldValue) _
                    Or (IsNull(ctlCtrl) And Not IsNull(ctlCtrl.OldValue)) _
                    Or (Not IsNull(ctlCtrl) And IsNull(ctlCtrl.OldValue))) 
Then
                        lngNewValue = Len(IIf(IsNull(ctlCtrl), 0, ctlCtrl))
                        lngOldValue = Len(IIf(IsNull(ctlCtrl.OldValue), 0, 
ctlCtrl.OldValue))
                        If lngOldValue > 255 Or lngNewValue > 255 Then       
   ' If a memo, write to that table
                            strHist = "tblHistMemo"                          
      ' Memo table
                            Else
                            strHist = "tblHist"                              
      ' Non memo table
                        End If
                        
                        ' This function creates new history records
                        Call funAddHist(strHist, _
                                        intKey, _
                                        strFormName, _
                                        strKeyName, _
                                        ctlCtrl, _
                                        strOptional)
            
                                        ' strHist = Select which table to 
enter data into
                                        ' MyKey is the value of the PK
                                        ' strFormName is the name of the 
form being modified including full path for subforms
                                        ' strKeyName is the name of the 
Primary Key field in the table e.g. "tblPeople.PersonNo"
                                        ' ctlCtrl is the control that changed
                                        ' strOptional1 is the cancatenated 
descriptive string
                End If
            End If
        End If
    End If
Next ctlCtrl

funLogTrans = True                                                  'Let 
User know sucess

End Function

Public Function funActiveCtrl(ctl As Control) As Boolean
' This function checks what type of control is being examined.  If it is not 
an updateable type of control, it
' sets the function to false.

Select Case ctl.ControlType
    Case Is = acTextBox
    funActiveCtrl = True
    
    Case Is = acLabel
    Case Is = acRectangle
    Case Is = acLine
    Case Is = acImage
    Case Is = acCommandButton
    Case Is = acOptionButton
    Case Is = acCheckBox
    funActiveCtrl = True
    
    Case Is = acOptionGroup
    Case Is = acBoundObjectFrame
    Case Is = acListBox
    funActiveCtrl = True
    
    Case Is = acComboBox
    funActiveCtrl = True
    
    Case Is = acSubform
    Case Is = acObjectFrame
    Case Is = acPageBreak
    Case Is = acPage
    Case Is = acCustomControl
    Case Is = acToggleButton
    Case Is = acTabCtl

End Select

End Function

Public Function funAddHist(strHist As String, _
                            intKey As Integer, _
                            strFormName As String, _
                            strKeyName As String, _
                            ctlCtrl As Control, _
                            Optional strOptional As String)
                            ' strHist = Select which table to enter data into
                            ' MyKey is the value of the PK
                            ' strFormName is the name of the form being 
modified including full path for subforms
                            ' strKeyName is the name of the Primary Key 
field in the table e.g. "tblPeople.PersonNo"
                            ' ctlCtrl is the name of the control that changed
                            ' strOptional1 is the cancatenated descriptive 
string.

' This function creates new history records

Dim dbs As DAO.Database
Dim tblHistTable As DAO.Recordset

    Set dbs = CurrentDb
    Set tblHistTable = dbs.OpenRecordset(strHist, dbOpenDynaset)  ' Open 
either the memo or normal history table

    With tblHistTable
        .AddNew
            !DateChange = Now()
            !PersonNo = Forms!frmMenu.txtUserPersonNo
            !FormName = strFormName
            !KeyName = strKeyName
            !Key = intKey
            !FieldName = ctlCtrl.Name
            ' !UserId = Environ("Username") 'To pick up the environmental 
user ID
            !OldValue = ctlCtrl.OldValue
            !NewValue = ctlCtrl.Value
            !Optional = strOptional
       .Update
    End With

End Function

Public Function funAddHistSQLUpdate _
                            (strFormName As String, _
                            strPK As String, _
                            intKey As Integer, _
                            strFieldName As String, _
                            strOldValue As String, _
                            strNewValue As String, _
                            Optional strOptional As String)
                            ' strFormName is the name of the form being 
modified including full path for subforms
                            ' strPK is the name of the Primary Key field in 
the table e.g. "tblPeople.PersonNo"
                            ' intKey is the value of the PK
                            ' strFieldName is the name of the control that 
changed
                            ' strOldValue is the old value
                            ' strNewValue is the new value
                            ' strOptional is the cancatenated descriptive 
string.

Dim lngNewValue As Long
Dim lngOldValue As Long
Dim strHist As String

' Decide which table to insert the records
    lngNewValue = Len(NewValue)
    lngOldValue = Len(OldValue)
    If lngOldValue > 255 Or lngNewValue > 255 Then          ' If a memo, 
write to that table
        strHist = "tblHistMemo"                                ' Memo table
        Else
        strHist = "tblHist"                                    ' Non memo 
table
    End If

' This function creates new history records
Dim dbs As DAO.Database
Dim tblHistTable As DAO.Recordset

    Set dbs = CurrentDb
    Set tblHistTable = dbs.OpenRecordset(strHist, dbOpenDynaset)  ' Open 
either the memo or normal history table

    With tblHistTable
        .AddNew
            !DateChange = Now()
            !PersonNo = Forms!frmMenu.txtUserPersonNo
            !FormName = strFormName
            !KeyName = strPK
            !Key = intKey
            !FieldName = strFieldName
            ' !UserId = Environ("Username") 'To pick up the environmental 
user ID
            !OldValue = strOldValue
            !NewValue = strNewValue
            !Optional = strOptional
       .Update
    End With

End Function

Public Function IsNoOldValue(ctlTest As Control) As Boolean
' Checks to see if the old value is valid for this control. If the field is 
a linked field, there will be no value
' There is an article at 
http://groups.google.com.au/group/comp.databases.ms-access/browse_thread/thread/c45bc197599675df/37df246c541b0042%2337df246c541b0042

Dim strTestValue As String
    On Error Resume Next
    strTestValue = ctlTest.OldValue
    IsNoOldValue = (Err.Number = 0)
End Function


Sub WriteHistory(strTableName As String, strPK As String, strFieldName As 
String, strFormName As String, _
                    Optional strWhere As String, Optional blnMoreUpdates As 
Boolean)
                    
    Dim rstOld As Recordset                     ' The old data from the temp 
table
    Dim rstNew As Recordset                     ' The new data from the real 
table
    Dim strOldTable As String                   ' SQL to retrieve old data 
and populate the recordset
    Dim strNewTable As String                   ' SQL to retrieve new data 
and populate the recordset
    Dim strCriteria As String                   ' The criteria to find the 
new record
    Dim strTempTable As String                  ' The name of the temporary 
table
    Dim intKey As Integer                       ' Value of the primary key
    Dim strOldValue As String                   ' Value before the change
    Dim strNewValue As String                   ' Value after the change
    Dim strOptional As String                   ' Optional information
    Dim dbs As Database
    Dim fld As Field                            ' Used to loop through all 
the fields in a record
    
    On Error GoTo Error_WriteHistory
   
    strTempTable = "temp" & strTableName            ' Name of the temporary 
table with the old data
 
    ' Create SQL statements for each recordset
    strOldTable = "SELECT  * " & " FROM " & strTempTable & _
                    " WHERE " & strTempTable & "." & strWhere
    strNewTable = "SELECT * " & " FROM " & strTableName & _
                    " WHERE " & strTableName & "." & strWhere
                    
    ' Create the recordsets
    Set dbs = CurrentDb
    Set rstOld = dbs.OpenRecordset(strOldTable)
    Set rstNew = dbs.OpenRecordset(strNewTable)
    
    ' Handle the situation where there is no old record.  This is a new 
monthly record
    If rstOld.EOF = True Then
        rstNew.MoveFirst
            intKey = rstNew.Fields(strPK)
            strOptional = ""
            
            ' Loop through the fields and put a 0 in the old record field
            For Each fld In rstNew.Fields
               strOldValue = 0                                  ' Old value 
(was null as there was no record)
               strNewValue = fld                                ' New value
               strFieldName = fld.Name                          ' Field name
               
               If strOldValue <> strNewValue Then               ' Check if 
there is a new value or whether it is blank
                   Call funAddHistSQLUpdate _
                        (strFormName, _
                        strPK, _
                        intKey, _
                        strFieldName, _
                        strOldValue, _
                        strNewValue, _
                        strOptional)                            ' Add a 
history record
               End If
                         
            Next
            blnMoreUpdates = False                              ' No more 
updates so delete the temp table
            GoTo After_Write                                    ' Skip the 
update for existing records
    End If
    
    ' Handles the situation where there is an old record.  Compare values 
where an existing record exists
    strCriteria = strPK & " = " & rstOld.Fields(strPK)          ' Create the 
criteria string
    rstNew.FindFirst strCriteria                                ' Find the 
new record
    
    rstOld.MoveFirst
        While Not rstOld.EOF                                            ' 
Find the old record
            strCriteria = strPK & " = " & rstOld.Fields(strPK)   ' Create 
the criteria string
           rstNew.MoveFirst
           rstNew.FindFirst strCriteria                                 ' 
Find the new record
                
                For Each fld In rstNew.Fields                           ' 
Loop through the fields in the record
                    strFieldName = fld.Name                             ' 
Name of the field
                    
                    If rstNew.Fields(strFieldName) <> 
rstOld.Fields(strFieldName) Then    ' Compare the records
                        intKey = rstNew.Fields(strPK)
                        strOldValue = rstOld.Fields(strFieldName)
                        strNewValue = rstNew.Fields(strFieldName)
                        strOptional = ""
                        
                        Call funAddHistSQLUpdate _
                                 (strFormName, _
                                 strPK, _
                                 intKey, _
                                 strFieldName, _
                                 strOldValue, _
                                 strNewValue, _
                                 strOptional)
                    End If
                Next
                
           rstOld.MoveNext                                              ' 
Move to the next record
        Wend
    
After_Write:
    'Clean up
    Set rstNew = Nothing
    Set rstOld = Nothing
    Set dbs = Nothing
    
    'If finished with history updates delete the table
    If blnMoreUpdates <> True Then
        If funTableExists(strTableName) Then
            subRunSelectQuery (strTableName)                               ' 
Delete the temporary table
        End If
    End If
    
Exit_WriteHistory:
    Exit Sub
    
Error_WriteHistory:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_WriteHistory
End Sub

Sub subLogReport(strReportName As String)
    Set dbs = CurrentDb
    Set tblHistoryReport = dbs.OpenRecordset("tblHistoryReport", 
dbOpenDynaset)  ' Open the report history table

    ' Create the history record
    With tblHistoryReport
        .AddNew
            !DateRan = Now()
            !PersonNo = Forms!frmMenu.txtUserPersonNo
            !ReportName = strReportName
       .Update
    End With
End Sub

You might have to play with it a bit but see how it goes.

Cheers

Neville Turbit
www.projectperfect.com.au




"Peter" wrote:

> Hi all, 
> 
> i want to build a time tracking function in order to track each occasion a 
> record in Table A is being updated. Table B stores the Time, Now(), Name, 
> fOSUserName() and a field related to the Key field of table A.
> When a record in the form related to table A is updated (on the Before 
> update event) i want this to be recorded in Table B...
> Question:
> Do i need a separate form (not visible) to capture this data or can i code 
> Form A, on the Before Update event or on the Close event, to update the 
> related record in Table B?
> 
> Thanks!
> 
0
Utf
5/7/2010 8:04:01 AM
Thanks you ineed Neville, i will try this out...looks very interesting..

Thanks!

"NevilleT" wrote:

> Hi Peter
> 
> What you are trying to do is not complicated.  You use the before update 
> event to write the change to a separate table.  I use two tables.  If it is 
> not a memo field, it goes in one table where every changed field is treated 
> as a text field.  If it is a memo field it goes in another table. 
> 
> I did a cut and paste from an application I used.  This is what is in each 
> form.
> 
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>     Dim intKey As Integer                                           ' 
> Primary Key Value
>     Dim strKeyName As String                                        ' Table 
> name of the primary key in the form
>     Dim strOptional As String                                       ' Option 
> 1 for additional data
>     Dim strFormName As String                                       ' Full 
> form name including reference to parent forms if a subform
>         
>     On Error GoTo Error_Form_BeforeUpdate
>     
> '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  Change for each form 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>     strKeyName = "tblPeople.PersonNo"                               ' Table 
> Name of the field for the Primary key
>     intKey = Me.PersonNo                                            ' PK 
> value on the form
>     strOptional = "Person changed was " & Me.txtName                ' 
> Cancatenated Description
> '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>     
>     ' Find the form name.  Check if it is a subform and add the name to the 
> string
>     strFormName = Me.Name                                           ' Name 
> of the form
>     Set frmToCheck = Me                                             ' Name 
> of this form
>     
>     ' Examine the form to see if it is a subform.  Create a cancatenated 
> string of the form!subform name
> CheckSubForm:
>     If funIsSubForm(frmToCheck) = True Then                         ' Check 
> if it is a subform
>         strFormName = Me.Parent.Name & "!" & strFormName            ' Add 
> the parent to the string
>         GoTo CheckSubForm
>     End If
>    
>     ' Run the update routine
>     Call funLogTrans(Me, _
>                     intKey, _
>                     strFormName, _
>                     strKeyName, _
>                     strOptional)
>                             ' Me is the form passing the information
>                             ' MyKey is the value of the PK
>                             ' strFormName is the name of the form being 
> modified including full path for subforms
>                             ' strKeyName is the name of the Primary Key 
> field in the table e.g. "tblPeople.PersonNo"
>                             ' strOptional1 is the cancatenated descriptive 
> string.
>                             
> Exit_Form_BeforeUpdate:
>     Exit Sub
>     
> Error_Form_BeforeUpdate:
>     MsgBox "Error in Form_BeforeUpdate: " & Err.Number & " - " & 
> Err.Description
>     Resume Exit_Form_BeforeUpdate
> End Sub
> 
> This is in a separate module.
> 
> Option Compare Database
> Option Explicit
>     Public frmToCheck As Form
> 
> Public Function funLogTrans(frm As Form, _
>                             intKey As Integer, _
>                             strFormName As String, _
>                             strKeyName As String, _
>                    Optional strOptional As String) _
>                         As Boolean
>                             ' Frm is the form passing the information
>                             ' intKey is the value of the PK
>                             ' strFormName is the name of the form being 
> modified including full path for subforms
>                             ' strKeyName is the name of the Primary Key 
> field in the table e.g. "tblPeople.PersonNo"
>                             ' strOptional1 is the cancatenated descriptive 
> string.
> Dim dbs As DAO.Database
> Dim ctlCtrl As Control
> Dim MyMsg As String
> Dim strHist As String
> Dim lngOldValue As Long
> Dim lngNewValue As Long
> 
> ' Loop through controls to find ones that changed
> For Each ctlCtrl In frm.Controls
>     If (funActiveCtrl(ctlCtrl)) Then                                 ' Check 
> it is an updateable control
>         If IsNoOldValue(ctlCtrl) = True Then                         ' Is 
> the oldvalue valid for this control
>             If ctlCtrl.Enabled = True Then                            ' Is 
> the control enabled.
>                 If ((ctlCtrl.Value <> ctlCtrl.OldValue) _
>                     Or (IsNull(ctlCtrl) And Not IsNull(ctlCtrl.OldValue)) _
>                     Or (Not IsNull(ctlCtrl) And IsNull(ctlCtrl.OldValue))) 
> Then
>                         lngNewValue = Len(IIf(IsNull(ctlCtrl), 0, ctlCtrl))
>                         lngOldValue = Len(IIf(IsNull(ctlCtrl.OldValue), 0, 
> ctlCtrl.OldValue))
>                         If lngOldValue > 255 Or lngNewValue > 255 Then       
>    ' If a memo, write to that table
>                             strHist = "tblHistMemo"                          
>       ' Memo table
>                             Else
>                             strHist = "tblHist"                              
>       ' Non memo table
>                         End If
>                         
>                         ' This function creates new history records
>                         Call funAddHist(strHist, _
>                                         intKey, _
>                                         strFormName, _
>                                         strKeyName, _
>                                         ctlCtrl, _
>                                         strOptional)
>             
>                                         ' strHist = Select which table to 
> enter data into
>                                         ' MyKey is the value of the PK
>                                         ' strFormName is the name of the 
> form being modified including full path for subforms
>                                         ' strKeyName is the name of the 
> Primary Key field in the table e.g. "tblPeople.PersonNo"
>                                         ' ctlCtrl is the control that changed
>                                         ' strOptional1 is the cancatenated 
> descriptive string
>                 End If
>             End If
>         End If
>     End If
> Next ctlCtrl
> 
> funLogTrans = True                                                  'Let 
> User know sucess
> 
> End Function
> 
> Public Function funActiveCtrl(ctl As Control) As Boolean
> ' This function checks what type of control is being examined.  If it is not 
> an updateable type of control, it
> ' sets the function to false.
> 
> Select Case ctl.ControlType
>     Case Is = acTextBox
>     funActiveCtrl = True
>     
>     Case Is = acLabel
>     Case Is = acRectangle
>     Case Is = acLine
>     Case Is = acImage
>     Case Is = acCommandButton
>     Case Is = acOptionButton
>     Case Is = acCheckBox
>     funActiveCtrl = True
>     
>     Case Is = acOptionGroup
>     Case Is = acBoundObjectFrame
>     Case Is = acListBox
>     funActiveCtrl = True
>     
>     Case Is = acComboBox
>     funActiveCtrl = True
>     
>     Case Is = acSubform
>     Case Is = acObjectFrame
>     Case Is = acPageBreak
>     Case Is = acPage
>     Case Is = acCustomControl
>     Case Is = acToggleButton
>     Case Is = acTabCtl
> 
> End Select
> 
> End Function
> 
> Public Function funAddHist(strHist As String, _
>                             intKey As Integer, _
>                             strFormName As String, _
>                             strKeyName As String, _
>                             ctlCtrl As Control, _
>                             Optional strOptional As String)
>                             ' strHist = Select which table to enter data into
>                             ' MyKey is the value of the PK
>                             ' strFormName is the name of the form being 
> modified including full path for subforms
>                             ' strKeyName is the name of the Primary Key 
> field in the table e.g. "tblPeople.PersonNo"
>                             ' ctlCtrl is the name of the control that changed
>                             ' strOptional1 is the cancatenated descriptive 
> string.
> 
> ' This function creates new history records
> 
> Dim dbs As DAO.Database
> Dim tblHistTable As DAO.Recordset
> 
>     Set dbs = CurrentDb
>     Set tblHistTable = dbs.OpenRecordset(strHist, dbOpenDynaset)  ' Open 
> either the memo or normal history table
> 
>     With tblHistTable
>         .AddNew
>             !DateChange = Now()
>             !PersonNo = Forms!frmMenu.txtUserPersonNo
>             !FormName = strFormName
>             !KeyName = strKeyName
>             !Key = intKey
>             !FieldName = ctlCtrl.Name
>             ' !UserId = Environ("Username") 'To pick up the environmental 
> user ID
>             !OldValue = ctlCtrl.OldValue
>             !NewValue = ctlCtrl.Value
>             !Optional = strOptional
>        .Update
>     End With
> 
> End Function
> 
> Public Function funAddHistSQLUpdate _
>                             (strFormName As String, _
>                             strPK As String, _
>                             intKey As Integer, _
>                             strFieldName As String, _
>                             strOldValue As String, _
>                             strNewValue As String, _
>                             Optional strOptional As String)
>                             ' strFormName is the name of the form being 
> modified including full path for subforms
>                             ' strPK is the name of the Primary Key field in 
> the table e.g. "tblPeople.PersonNo"
>                             ' intKey is the value of the PK
>                             ' strFieldName is the name of the control that 
> changed
>                             ' strOldValue is the old value
>                             ' strNewValue is the new value
>                             ' strOptional is the cancatenated descriptive 
> string.
> 
> Dim lngNewValue As Long
> Dim lngOldValue As Long
> Dim strHist As String
> 
> ' Decide which table to insert the records
>     lngNewValue = Len(NewValue)
>     lngOldValue = Len(OldValue)
>     If lngOldValue > 255 Or lngNewValue > 255 Then          ' If a memo, 
> write to that table
>         strHist = "tblHistMemo"                                ' Memo table
>         Else
>         strHist = "tblHist"                                    ' Non memo 
> table
>     End If
> 
> ' This function creates new history records
> Dim dbs As DAO.Database
> Dim tblHistTable As DAO.Recordset
> 
>     Set dbs = CurrentDb
>     Set tblHistTable = dbs.OpenRecordset(strHist, dbOpenDynaset)  ' Open 
> either the memo or normal history table
> 
>     With tblHistTable
>         .AddNew
>             !DateChange = Now()
>             !PersonNo = Forms!frmMenu.txtUserPersonNo
>             !FormName = strFormName
>             !KeyName = strPK
>             !Key = intKey
>             !FieldName = strFieldName
>             ' !UserId = Environ("Username") 'To pick up the environmental 
> user ID
>             !OldValue = strOldValue
>             !NewValue = strNewValue
>             !Optional = strOptional
>        .Update
>     End With
> 
> End Function
> 
> Public Function IsNoOldValue(ctlTest As Control) As Boolean
> ' Checks to see if the old value is valid for this control. If the field is 
> a linked field, there will be no value
> ' There is an article at 
> http://groups.google.com.au/group/comp.databases.ms-access/browse_thread/thread/c45bc197599675df/37df246c541b0042%2337df246c541b0042
> 
0
Utf
5/7/2010 8:56:01 AM
Reply:

Similar Artilces:

periods of time
Hi Can somebody help me with this please,i need a formular that can calculate the difference between the two times and show the answer as a plus or minus or green/red.For example in the below, the answer would be +1:30 as the actual time is an 1hr 30min before the expected. Expected arrival time 8:00 Actual arrival time 6:30 answer Thanks Ben Hello again Ben, You need the following formula in column D: =(B1-A1)*24 You will need to format the result cells to be General formats not dates. To do the highlight all the expected results cells for column D and hold Ctrl...

Problem: emails being sent multiple times
Outlook Express on Vista. I have a colleague who has a problem that some of his emails are being sent many many times eg 50 or 100 times. He only clicks send once. Any ideas on what the problem might be or how to fix? Cheers Alec There is no Outlook Express on Vista On Vista it is Windows Mail and all relevant questions about it should = be posted to the microsoft.public.vista.mail newsgroup --=20 Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "Alec" <alecmcq@tpg.com.au> wrote in message ...

Linear time axis on line chart
Hello, I want to have a linear time axis along the bottom of my chart, and to display some values associated with a series of events and to have their position on the axis grouped and spaced by the time that they occured. This means that if I had three things happen at 9:15, 9:30 and 10:00 on monday, then another event at 9:00 on Tuesday, the values for the first three would appear in quite close proximity along the axis, then a fairly large gap before the next one. - does that make sense? I can nearly get what I want with a scatter chart, but some of the events will have one or two o...

401 times out on sending worksheet mssg to headquarters
Doing a 401 and backdating to beginning of year to get these records from store db (the store db was originally QS2000 and upgrade to 2.0 was done; 101 sync with HQ was done as well and both were successful); the test database connection on both server and client are successful, and 401 without backdating processes OK but the backdated 401 hangs at the end, after uploading store data to HQ is successful, then Sending Worksheet message to headquarters is the task that hangs and times out after an hour; upload bandwidth isn't the best, this may be the reason? Is there a KB or section...

entering times automaticly
Just wondering if it is possible for employees to type a 4 digit # (like the last four of a social) and exel will enter a time into a position. We currently use time cards, and have to enter them all in by hand at the end of the night. Let me know if you have any ideas. Hi Have a look at this: http://www.cpearson.com/excel/DateTimeEntry.htm It may be of use to you! -- Andy. "George" <George@discussions.microsoft.com> wrote in message news:7CD8BB41-B86D-4387-B28B-F8E09873D73D@microsoft.com... > Just wondering if it is possible for employees to type a 4 digit # (like ...

Calculate hours between to separate dates and times
I need to work out the amount of hours between a finish time on one day and the start time on the next day. In some cases the start time may not be until 2 days time. Just subtract them, =A2-A1, and format the cell as [h] (or [h]:mm if you want the mins as well) -- __________________________________ HTH Bob "Dave08" <Dave08@discussions.microsoft.com> wrote in message news:24DC1455-F22C-498E-8230-3066870EA4DD@microsoft.com... >I need to work out the amount of hours between a finish time on one day and > the start time on the next day. In some cases the start time ...

Outlook sends same email many times
HI, Occasionally Outlook will send the same email many times to my recipient. I don't know this because the message remains in my outbox with the appearance of not being transmitted. But in fact, Outlook has sent the message to recipient over and over until I deleted it from the outbox. One person received an eight emails, but this same email was still in my outbox. This seems to happen when I have attached a file to the email. This is a real problem... Can anybody help? Thanks in advance. ...

Tracking Attachment File Names #2
Tracking Attachment File Names I posted the following to this group in May last year but didn't get a nibble. I'll try again in the hope that someone has crossed a bridge or two in the mean time: Why is Outlook 2003 so reluctant to expose details of attachments to the user? I would like to be able to search for, view, and print a report that targets the file names of attachments. The attachment file name only appears on a printed message if the message is formatted as plain text. Even then, the file name is only fully displayed if it is not too long to fit in the space allocated. ...

Date and time query
I have a table that records time-stamps with the date and time contained in the same column. How do I query these fields to only show the time or only show the dates? LASTUPDATEDT 4/23/2007 11:48:30 AM For same date: WHERE DateValue(MyDateTimeField) = #04/23/2007# or WHERE MyDateTimeField BETWEEN #04/23/2007# AND #04/24/2007# For same time: WHERE TimeValue(MyDateTimeField) = #11:48:30# -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "mpcoon" <mpcoon@discussions.microsoft.com> wrote in message news:89547704-8235-4B9D-BE0...

Time difference #2
Hi, In Col. 'A' a time is enter through time(08,0,0) function and in Col. 'B' the current time through Now() function. How I can calculate the difference or the number of hours and minutes laps between two times. please help me to get the answer. thanks.. Hi use =B1-A1 and format as time or if the time can span midnight try =B1-A1+(B1<A1) -- Regards Frank Kabel Frankfurt, Germany ismail wrote: > Hi, > In Col. 'A' a time is enter through time(08,0,0) function and in Col. > 'B' the current time through Now() function. How I can calculate the &...

sell cvv + transfer + ship + dumps + track 1&2...all country ! 08-22-10
Hello all !! I'm seller cvv from Viet Nam Contact me: - Email: khaisell******.com.vn - Y!m: khaisell us (visa , master) = 2 $ us (amex , dis) = 4 $ us (full) = 20$ us card with DOB = 10$ ====== uk (visa , master) = 5 $ uk (amex , dis ) = 10 $ uk (with dob) = 20$ uk (full) = 40$ ====== EU Italia = 18$ Germany = Sweden = 18$ Spain = 18$ I'm seller cvv from Viet Nam Contact me: - Email: khaisell******.com.vn - Y!m: khaisell us (visa , master) = 2 $ us (amex , dis) = 4 $ us (full) = 20$ us card with DOB = 10$ ====== uk (visa , master) = 5 $ uk (amex , di...

Installation time out
when installing CRM 4 I get this error crm.setup.server.installconfigdatabaseaction failed.Timeout expired.Can anyone help me -- donn ...

Charting various times. Help!!
Hi there. I currently have a table which I have run a query on that tables like this Date. Target time. Time achieved. Diff. hh/mm/yyyy hh:mm hh:mm n I have worked out how to chart the 'Diff' column as this is in number format. However when I try and throw in the actual times into a chart I get a chart containing values from 0 through to 0.8?? I have set my format as 'short time' in both the query and the table but to no avail. I have searched everywhere and cannot find an answer. All I want is a chart with the 't...

Track Changes problem in Excel 2007
I have excel workbooks created in Excel 2003 that I exchange with others, some of whom have Excel 2007. With some (but not all) of those users, the track changes function comes back corrupted, with the changes noted for the wrong cells. My users add rows, sort and filter the worksheet (along with making corrections to the data). Has anyone else had this problem or have any idea how it can be fixed? I don't ever have this problem with users who use pre-2007 Excel. Thanks so much ...

Can I track changes made to a shared file per userid.
I was wondering if I could track who made what changes in a shared excel file. If so, how should I go about it? Thank you in advance! Julie On that same Tools|Share Workbook dialog, there's an Advanced Tab. There's an option (default is On???) that allows you to track those changes. When you're ready to look at the changes, tools|track changes|accept or reject changes (You may already have it turned on.) But be aware that if you keep lots of changes, then excel can slow down. Julie _at_GBS wrote: > > I was wondering if I could track who made what changes in a share...

Student Tracking Database
Hi,I have a database that staff enter details into that tracks studentprogress at interimperiods throughout the year. This then produces reports that get senthome and parentsuse to see how their child is doing. The database is setup with amain table tblPupilDatathat stores core information about the pupil and the subjects thatthey are in. This tableis related to individual subject tables with referential integrityenforced which stores thedetails staff enter on the students progress.Table structures are as follows:tblPupilDataPupilID - Autonumber (Primary Key), Name - Text, M/F - Text, DOB -...

How do I set up a formula on a time sheet to calculate time in 1/.
I am setting up a time spreadsheet and I want all of my totals to be calculated in 1/4 increments. Example: IN OUT IN OUT TOTAL 7:01 AM 11:47AM 12:50 PM 4:32 PM = 8:30 hours Don't know what you total formula is, but let us say it is =(B2-A2)+(D2-C2) If you want to round this to the nearest quarter hour use =ROUND(((B2-A2)+(D2-C2))*96,0)/96 If you want to round up a quarter hour, use =ROUNDUP(((B2-A2)+(D2-C2))*96,0)/96 -- HTH RP (remove nothere from the email address if mailing direct) "gschmid" <gschmid...

Time Formula #2
Hi I run Excel 2K I download data from a mainframe. This data has a date & time format in it (dd/mm/yyyy hh:mm:ss) I work in a place that has a 3 shift cycle - day shift, afternoon shift, night shift. Day shift starts at 7:20am and ends at 15:29pm Afternoon shift starts at 15:30pm and ends at 23:19pm Night shift starts at 23:20pm amd ends at 7:19am I need a formula that looks at the cell with the date/time in it and displays the word DAY (for the time frame of day shift), AFTERNOON (for the timeframe of afternoon shift) and NIGHT (for the timeframe of night shift) In my previous ...

How do you track loans/transfers in the Budget?
I can't seem to figure out how to track my loan payments in the budget! I know you can assign a [category] to your payment, but then how would you track your total balance left in a loan account? Is there a better way to do this? Can you show transfers to accounts in the budget? I know you can in Quicken, but what about Money? Thanks Mark A common misconception is that the budget in Money is there to track your movement of money. It isn't. It tracks your income and expenses. Income > Expenses, you're getting richer. Expenses > Income, you're getting poorer. Bu...

Two scanners at the same time
Hi! One of my employees has a hard time using the Symbol LS 2008 because you have to press a button. I have thus installed a Metrologic MS 9520. I like the Symbol better. The MS is plugged via wedge. Can I also have a Symbol scanner (USB) plugged in at the same time? As long as you are using a PS/2 keyboard you should be fine. The Metrologic Wedge interface won't work unless a Keyboard is plugged in behind it - or at least that was my experience before we switched to all USB devices... Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com -----------...

Dates on tracked payments
I just upgraded to 2003 from 2000. I transfered all my setting over and have been having trouble entering common transactions like my upcoming bills and paychecks. When I select a common transaction from my checking account Bills list Money lists the upcoming date correctly. When I click on this transaction to enter it the date is from a month ago. Is there a cache file or something that I need to deltete to make Money update these transaction dates? ...

Scheduling Time Off through the SDK
In Microsoft CRM 3.0 you can manually create Time Off for users by accessing their calendar from their user form (Settings-Business Unit Settings-User). This time off seems to be different than an appointment. How can I accomplish this through the SDK? Thanks, ...

Time recognized as a whole number?
I want to use a time value presented in hours and minutes as a number that can be used to multiply to get a value. ie. so many hours and minutes equals a number and using that number times a dollars per hour number to get a gross. Did you suddenly change your clock? See previous post. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "Bob Morrison" <morrib@chartermi.net> wrote in message news:k5vni.13$b_7.10@newsfe12.lga... >I want to use a time value pr...

Time "and" Date for data points
I have date and time in my first column for my x axis, but I can't get both date and time to display in the chart. My data spans two weeks and now displays as I want it, but the data is useless without showing the relevant date/time for the data points (across the bottom of the chart). Is there a work around for this. I've tried different date/time cell formats which will display as I want it in the cell, but can't get the same display in the chart. I'm not sure what I'm doing wrong. Thanks for any help. Diana Cowtoon wrote: > I have date and time in my...

time.h+ Linking error
Hi, I have an application where I use functions like time(),gmtime() etc from time.h. My code compilers but I get Linking error saying that "unresolved external symbol time" . Could you please tell me which library I need to include for time.h? Thanks, Harish A quick search of the Windows CE newsgroups will answer this: http://groups.google.com/groups/search?as_q=gmtime&as_epq=&as_oq=&as_eq=&num=10&scoring=&lr=&as_sitesearch=&as_qdr=&as_mind=1&as_minm=1&as_miny=2009&as_maxd=1&as_maxm=1&as_maxy=2009&as_ugroup=m...