stop blank fields being logged

[peterjb44]

Hi all,

the code below is used for a log for changes, problem is it logs blank fields, could you please help me with the extra code so it ignores blank fields.

BTW I didn't write the code....too much of a novice

Function LogChanges(lngID As Long, Optional strField As String = "")
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varOld As Variant
    Dim varNew As Variant
    Dim strFormName As String
    Dim strControlName As String
 
    varOld = Screen.ActiveControl.OldValue
    varNew = Screen.ActiveControl.Value
    strFormName = Screen.ActiveForm.Name
    strControlName = Screen.ActiveControl.Name
    Set dbs = CurrentDb()
    Set rst = dbs.TableDefs("LogT").OpenRecordset
 
    With rst
        .AddNew
        !FormName = strFormName
        !ControlName = strControlName
        If strField = "" Then
            !FieldName = strControlName
        Else
            !FieldName = strField
        End If
        !RecordID = lngID
        !UserName = Environ("username")
        If Not IsNull(varOld) Then
            !OldValue = CStr(varOld)
        End If
        !NewValue = CStr(varNew)
        .Update
    End With
    'clean up
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
End Function

thanks

Peter

 

[nschroeder]

I would adjust it like this:

    varOld = Screen.ActiveControl.OldValue
    varNew = Screen.ActiveControl.Value
    If IsNull(varNew) "" then Exit Sub
    If varNew = "" then Exit Sub
    strFormName = Screen.ActiveForm.Name

I'm not sure if you need both If statements. You can play around with it and find out.

 

[nschroeder]

You also may need to consider the possibility of them blanking out an existing value. If that's a change you want to log, then change the above to:

    If varNew = "" and varNew = varOld then Exit Sub

 

[peterjb44]

Hi nschroedor,

I tried your codes, but it didn't work

this code was red

If IsNull(varNew) "" then Exit Sub

thanks for your reply

peter

 

[peterjb44]

anyone got any idea how to sort this for me.

I've been messing with the code to sort it, but still unable to do it

thanks

peter

 

[Solo712]

anyone got any idea how to sort this for me.

I've been messing with the code to sort it, but still unable to do it

thanks

peter

The statement is messed up a bit. It should read:

If IsNull(varNew) Then Exit Sub

Also, you can combine the two statements into one :

If IsNull(varNew) OR varNew = "" Then Exit Sub

Best,
Jiri

 

[peterjb44]

Hi Solo712

thanks for the reply,

I think I didn't explain correctly.....on a new form when putting data into a field it records the new value even though the old value is blank, that is what I want to stop being logged(as I would have too many in my log table), after that if the field has data in and it gets changed then I need that logged

thanks

peter

 

[Solo712]

Hi Solo712

thanks for the reply,

I think I didn't explain correctly.....on a new form when putting data into a field it records the new value even though the old value is blank, that is what I want to stop being logged(as I would have too many in my log table), after that if the field has data in and it gets changed then I need that logged

thanks

peter

Ok I get it. That's easy to do. Just change the "varNew" to the "varOld" in the statements I sent you. You will also have to specify what you want to do with the newVar if it is blank and it is not being policed in the code elsewhere.

Best,
Jiri

 

[peterjb44]

I did try that, but I get a error cos it not a sub, it a function

so I changed the end bit from exit sub to end and when I click on my button to exit and save it does nothing, when I close using the X in top right corner it says cannot save this record at this time

I changed the end bit to end function but a get a compile error: syntax error

any ideas?

sorry i'm new to this


thanks

peter

 

[Solo712]

I did try that, but I get a error cos it not a sub, it a function

so I changed the end bit from exit sub to end and when I click on my button to exit and save it does nothing, when I close using the X in top right corner it says cannot save this record at this time

I changed the end bit to end function but a get a compile error: syntax error

any ideas?

sorry i'm new to this


thanks

peter

Yeah, I copied the original suggestion and it had "Exit Sub" in it. My bad. Should be

If IsNull(varOld) OR varOld = "" Then Exit Function

Best,
Jiri

 

[peterjb44]

thanks Solo712, that now works

 

[nschroeder]

Back from the weekend. Sorry about the coding errors. My cutting and pasting failed me. Thanks for the assist Solo712.

 

[Solo712]

thanks Solo712, that now works

You are welcome !