how to update text box depending on value

[sspreyer]

hi all

right I have auditing system in my database that tracks any changes made to any records. it logs it on a separate table and i show it on a continuous form.

right my problem is that I have loads of check boxes and text boxes on my main form on the form that records the main form I have a field call "new value" this is a text box but if I change a checkbox on the main form it will show -1 or 0 in the new value field on the recording form but I would like it to say yes or no I have tried the following

 Private Sub New_Value_AfterUpdate()
 If [New_Value].Value = -1 Then
[New_Value].Value = "yes"
End If
 End Sub

this only works if I enter -1 manually but the data is entered automatically after the record is saved on the main form

I have also tried

on enter
before update I get error

but I'm running out of ideas

some how I need it to change on new entry

thank in advance

Shane

 

[bob fitz]

Shane

I'm not sure that I fully understand your set up or how you want it to work, but I think the code you have needs to be in the After Update event of the form that is being edited.
the code may need to be altered, so that it references the form that has the text box.

BTY, Hasn't it been a lovely day in our part of the world.

 

[sspreyer]

hi bob
yeah just hope it stay's it was lovely to day few beers in garden

here is the code I have on the main form on before update event

 Private Sub Form_BeforeUpdate(Cancel As Integer)
 On Error GoTo Form_BeforeUpdate_Err
    
    Call Audit_Trail(Me, "ID", ID.Value)
    
Form_BeforeUpdate_Exit:
    Exit Sub
    
Form_BeforeUpdate_Err:
    MsgBox err.Number & " - " & err.Description
    Resume Form_BeforeUpdate_Exit
    
End Sub

call audit trial code in module

 Option Compare Database
Option Explicit
 Public Function Audit_Trail(MyForm As Form, UniqID_Field As String, UniqID As String)
On Error GoTo Err_Audit_Trail
    
'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
    
    'Ddim MyForm As Form
    Dim ctl As Control
    Dim ccnt As Control
    Dim sUser As String
    
    Dim strSQL As String
    Const cQUOTE = """" 'Thats 2 quotes in sequence
        
    Dim Action, nullval As String
    nullval = "Null"
    
    sUser = Environ("UserName") 'get the users login name
     
    Dim changecnt As Integer
    changecnt = 0
    
    'Check each data entry control for change and record old value of the control.
    For Each ccnt In MyForm.Controls
    
    Select Case ccnt.ControlType
      Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        If ccnt.Name Like "*" & "txt" & "*" Then GoTo TryNextCCNT   'Skip AuditTrail field.
        If (ccnt.Value <> ccnt.OldValue) Or _
           (IsNull(ccnt.Value) And Len(ccnt.OldValue) > 0 Or ccnt.Value = "" And Len(ccnt.OldValue) > 0) Then
          changecnt = changecnt + 1
        End If
    End Select
    
TryNextCCNT:
  Next ccnt
        
    If changecnt > 0 Then
     'gstrReason = InputBox("Reason for change(s)?", "Reason for change(s)?")
    End If
        
    'Check each data entry control for change and record old value of the control.
    For Each ctl In MyForm.Controls
        
    'Only check data entry type controls.
    Select Case ctl.ControlType
    Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        If ctl.Name Like "*" & "txt" & "*" Then GoTo TryNextControl 'Skip AuditTrail field.
        If ctl.Value <> ctl.OldValue Then
            Action = "*** Updated Record ***"
        
            'Broken down into 4 separate variables for ease of view and troubleshooting
            strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action], Reason)"
            strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
            strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
            strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Name & cQUOTE & ", " & cQUOTE & ctl.OldValue & cQUOTE
            strSQL = strSQL & ", " & cQUOTE & ctl.Value & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ", " & cQUOTE & gstrReason & cQUOTE & ";"
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            
             ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
            Action = "*** Added Info to Record ***"
        
            'broken down into 4 separate variables for ease of view and troubleshooting
             strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action])"
             strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
             strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
             strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Name & cQUOTE & ", " & cQUOTE & nullval & cQUOTE
             strSQL = strSQL & ", " & cQUOTE & ctl.Value & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ";"
             
             DoCmd.SetWarnings False
             DoCmd.RunSQL strSQL
             DoCmd.SetWarnings True
            
        'If old value is Null and new value is not Null
        
             
        'If new value is Null and old value is not Null
        ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
             Action = "*** Removed Info to Record ***"
             'Broken down into 4 separate variables for ease of view and troubleshooting
             strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action], Reason)"
             strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
             strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
             strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Name & cQUOTE & ", " & cQUOTE & ctl.OldValue & cQUOTE
             strSQL = strSQL & ", " & cQUOTE & nullval & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ", " & cQUOTE & gstrReason & cQUOTE & ";"
             
             DoCmd.SetWarnings False
             DoCmd.RunSQL strSQL
             DoCmd.SetWarnings True
         End If
     End Select
    
TryNextControl:
    Next ctl
    
Exit_Audit_Trail:
    Exit Function
    
Err_Audit_Trail:
    If err.Number = 2001 Then 'You canceled the previous operation.
      'do nothing
    Else
        Beep
        MsgBox err.Number & " - " & err.Description
    End If
    Resume Exit_Audit_Trail
    
End Function

right I haven't got clue how I can add the code in the previous post to this and is why I was trying to contain it with in the form that records the data
I will try now to explain in more detail

I have a main form and audit form if any changes are made on the main form it then records it on the audit form. it record's user name , time changed ,field changed, previous value, new value work's great with text boxes but issues comes when I change a check box because I'm recording to a textbox it show the checkbox as 0 or -1 some how if text box is -1 then changed new value field text box to yes

I have all so added a timer function on the form so every 30 sec it requery's the audit form and collect the new change's


this is something else I tried

Private Sub Form_Timer()
 me.requery
 if [new_value].value = -1 then
 [new_value].value = "yes"
 end if 
 end sub

this works but because I have my form set to continuous form it will only change the top record not all record if -1


thanks for your time


shane

 

[bob fitz]

Perhaps you could create a new field in the forms Record Source query. With something like:
Expr1: IIf(Nz([new_value])=0,"False","True")
Then set the Control Source property of a text box on the form to: Expr1

 

[sspreyer]

hi bob

right tried what you suggested only one problem if the new value text box does not receive -1 or 0 I get a error for example if the new value text box receives a text value not check box value it then shows #error because its look for -1 or 0 I wonder is there any way of capturing it in the audit module before it records it or stop the error message in the additional field in the query

thanks for your help and time


shane

 

[bob fitz]

I think this will work:

Function AuditTrail()
On Error GoTo Err_Handler
    
    Dim MyForm As Form, C As Control, xName As String
    Set MyForm = Screen.ActiveForm

    'Set date and current user if form has been updated.
    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
    "Changes made on " & Date & " by " & CurrentUser() & ";"

    'If new record, record it in audit trail and exit sub.
    If MyForm.NewRecord = True Then
        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
        "New Record """
    End If

    'Check each data entry control for change and record
    'old value of Control.
    For Each C In MyForm.Controls
        
    'Only check data entry type controls.
    
    Select Case C.ControlType
        Case acTextBox, acComboBox, acListBox, acOptionGroup[COLOR="red"], acCheckBox[/COLOR]
            ' Skip Updates field.
            If C.Name <> "Updates" Then
    
            ' If control was previously Null, record "previous
            ' value was blank."
                If IsNull(C.OldValue) Or C.OldValue = "" Then
                    MyForm!Updates = MyForm!Updates & Chr(13) & _
                    Chr(10) & C.Name & "--previous value was blank"
                    
                ' If control had previous value, record previous value.
                ElseIf IIf(IsNull(C.Value), "", C.Value) <> C.OldValue Then
                    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
                    C.Name & "==previous value was " & [COLOR="Red"]IIf(Nz(C.OldValue) = 0, "False", "True")[/COLOR]
                    
                End If
            End If
        End Select
    Next C

TryNextC:
    Exit Function
      
Err_Handler:
    If Err.Number <> 64535 Then
        MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
    End If
    Resume TryNextC
End Function

 

[sspreyer]

sorry for the late reply thanks bob couldn't get it to work but doesn't matter just put a sign next to the text boxes stating what 0 or -1 mean thanks for the help if get change I have another thread I m stuck on if you have min http://www.access-programmers.co.uk/forums/showthread.php?t=263084

 

[bob fitz]

sorry for the late reply thanks bob couldn't get it to work but doesn't matter just put a sign next to the text boxes stating what 0 or -1 mean thanks for the help if get change I have another thread I m stuck on if you have min http://www.access-programmers.co.uk/forums/showthread.php?t=263084

I'm surprised that it didn't work for you. It was fine for me when I tested it before posting it. Are you sure you got both the changes that I made in the code.

 

[sspreyer]

no worries bob thanks anyway

Shane