Set Tag Property for Unbound Text Boxes

[Kodash]

Hi Guys,

I am having a little problem with making an audit trail for a form with some unbound text controls on it and wondered if you could point me in the right direction.
Since .OldValue won't work on unbound text boxes, google suggested that I put the old value in the control's tag property. This is what I came up with:

Dim ctl, tbox As Control
Dim strName, strOLD, StrNew, NewTag As String
Dim NextOne As Label
        
For Each tbox In Me.Controls
If TypeName(tbox) = "TextBox" Then
   Select Case tbox.Value
        Case Nz(tbox.Value) > 0
        Case Else
''''''''''''''''''''''''''''''''''''''''''''''''''
'        NewTag = Nz(tbox.Value)
'       tbox.Properties("Tag") = NewTag
''''''''''''''''''''''''''''''''''''''''''''''''''
        
    End Select
End If
Next tbox

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
    Select Case ctl.Value
        Case Nz(ctl.Value) > 0
        Case Else
        strName = Nz(ctl.Name)
        strOLD = Nz(NewTag)
        StrNew = Nz(ctl.Value)
                If strOLD <> StrNew Then
                MsgBox (strOLD & "    " & StrNew)
                Else
                End If
    End Select
End If
 Next ctl
End Sub

The first loop is supposed to dynamically assign the tag value, with whatever the textbox value is, however it doesn't :lol: I've asterixed out the line i think may be the issue, but could someone provide a hint please?

Thanks in advance

 

[CJ_London]

I would step through the code to see which parts of the code work - for example if tbox.Value is null, I suspect the case statement does not run

perhaps your typename function isn't working? Not sure why you need it

if tbox.controltype=actextbox then...

does the same thing

Also, why store oldvalues for unbound controls? they are always going to be null or perhaps the defaultvalue

 

[Kodash]

Hi CJ,

I'm pretty certain everything else works, just this bit. I can get it to read the tags just not fill them.

I may change the typename function, tbh it's an expansion on a sample on the internet ;/

The reason I need to old values, is because of the way the person who handed it to me set it up, I think it's his method of making sure the records aren't updated straight away, you need to click a button (I can see how this paragraph makes little sense)

The textboxes are populated from a Recordset that he set up, and then the button updates said rs.

My eventual plan is to have some SQL Inserts in place of the msgbox. So I can record the changes in a seperate table

 

[CJ_London]

unbound controls are not updated end of story, you are coding for coding sake and I cannot see your (or your predecessor's) rationale

you haven't said whether you have stepped through the code and you have not addressed the issue of nulls in oldvalue.

See this link

https://support.microsoft.com/en-us/kb/197592

also have a look at this

http://www.techrepublic.com/article/a-simple-solution-for-tracking-changes-to-access-data/


Personally I would ignore unbound controls - which you can do with adding an extra if statement

If TypeName(ctl) = "TextBox" Then
    [COLOR=red]if ctrl.controlsource<>"" then[/COLOR]    
        Select Case ctl.Value

 

[Galaxiom]

 Select Case tbox.Value
Case Nz(tbox.Value) > 0

The above is asking if the value of the textbox is equal to the Boolean result of this expression: Nz(tbox.Value) > 0

Use this:

Select Case Nz(tbox.Value)
        Case Is > 0

 

[spikepl]

Your declarations declare all variables as variant, save for the last one in each statement. Google.

 

[gemma-the-husky]

Not sure about the syntax for checking the control type. Offhand I think I use something like

If ctrl.controltype = actextbox then etc

 

[vbaInet]

kodash,

CJ_London & gemma-the-husky, Galaxiom and spikepl have highlighted some important points, so here's a tidied block of code that addresses those issues:

    Dim ctl     As Access.Control
    Dim tbox    As Access.Control
    Dim NextOne As Access.Control
    Dim strName As String
    Dim strOLD  As StringStrNew
    Dim NewTag  As String
    
    For Each tbox In Me.Controls
        If tbox.ControlType = acTextBox Then
            Select Case CLng(Nz(tbox.Value, 0))
                Case Is > 0
                Case Else
                    NewTag = Nz(tbox.Value)
                    tbox.Tag = NewTag
            End Select
        End If
    Next tbox

However, there are other issues which I'll highlight:
1. The Tag property is a String type so saving a Null to it will result in an error. I can see that you've tried to circumvent this by using the Nz() function, however, you're not saving the "real" value of the textbox to the tag when it returns Null.

2. The second iteration (i.e. your second For...Next loop is redundant) because, at this point, the value of the textbox is the same as the value in the Tag. The value of the textbox has not changed so why perform a second check?
You should also look to merge both For...Next loops.

3. The Nz() function is returning a String but you want a Number, so I've coerced the value to a Number type using the CLng() function. When performing an equality check against a numeric value it's best to cast to the correct type (i.e. a Long, Int, Decimal, Double etc).

4. Fyi: To access the Tag property you don't need the Properties("Tag") syntax. Just use tbox.Tag or ctl.Tag as I've done in the above code.

5. Fyi: TypeName and ControlType are interchangeable in this context but ControlType is more relevant and succinct in this context.

Hope this helps point you in the right direction.

 

[jdraw]

Welcome vbaInet. Haven't heard from you in a while, hope all is well.

 

[vbaInet]

Welcome vbaInet. Haven't heard from you in a while, hope all is well.

All's well thanks jdraw. I hope the same for you too!

 

[jdraw]

We're A-OK, back from FL.