Missing expected properties

[The_Doc_Man]

I thought I understood text boxes. Suddenly I'm not so sure.

I've got a form with a text box bound to an underlying record. The text box in question has a .ControlSource of a memo field. When I want to add some text to the memo field through a form, it is designed to do things like dynamic control of various command buttons and the navigation controls so that I cannot close the form or navigate away from it until I either COMMIT or CANCEL the pending changes. Safety feature to prevent my users from leaving behind an inconsistent form and bound record.

So in my little routine that tests for a change requiring entry to this "dirty" (no-navigate) state, I check the bound text box to compare the .Value and .Oldvalue properties. I want to do that because I display dirty fields differently than clean ones. However, the little code routine I use runs a sanity check on the control to verify that it has the required property. If it does not, then it takes a "safety" response that says the control has not been edited. Problem is, in the failing case, it has been edited at the time. There is no .Dirty property on a field, only on a form. So I can't scan the form for dirty fields. This means if I want to find out dirty controls, I have to search the form for them. I know to look in the .Text property for unbound controls - but this is a bound control that is supposed to have a .Value, or so I thought.

So when I watch my little sanity checker, what it does is it tries to find a property with the name .Value on a bound text box, as well as .OldValue - but in fact, neither property is there in this case. I double-checked that there is a bound field of the right .ControlSource name in the underlying record and that the bound field can be updated.

Is my problem because a memo field bound to a text box doesn't have a .Value or .OldValue? I.e. is this a MEMO field special case? The Access Help files seem to indicate that it should have that property because there is no note about Memo fields when I look up text boxes. Or did I miss something elsewhere?

 

[SOS]

I'm not sure but check this out as it may be applicable to your situation. I had a similar problem with .OldValue for something and found this and it was an eye opener.

 

[The_Doc_Man]

SOS, doesn't seem to apply. It isn't that the .VALUE is a particular value or not... it is that it doesn't exist at all. I had a choice about finding properties of a control so I started an enumeration that does a For Each pProperty in ctl.Properties loop, and looks at each pProperty.Name to see whether it matches the one I want. So I manually stepped through the loop only to find that .VALUE isn't one of the properties of a text box bound to that memo field. Even though ordinary TEXT BOXes bound to a Text (n<255) underlying field have that property. What a bummer.

I already know that for UNBOUND text boxes, there is no .VALUE or .OLDVALUE but you can still see the .TEXT property. I might have to "roll my own" for this one. That would tick me off big-time, though.

 

[DCrake]

If you have a form variable and use the keypress event on the memo field to set the status of the variable. That way you can detect if the memo field has been altered in any way. Just a thought.

 

[The_Doc_Man]

Last night I had a chance to think about it like a utility implementer instead of a database designer. I had a lucid moment of sorts.

In practical terms, there is no way to have an .OLDVALUE and .VALUE on top of a .TEXT property, even for a bound field - if it is type MEMO or the "BLOB" equivalent. Each property could be a gazillion characters long. You would potentially triple the size of your virtual memory requirements the moment you called in such a field. Performance in such a context would be non-existent. Such fields already cannot exist in the 2048-byte record buffer, either. MEMO fields are POINTERS to the "real" string somewhere else inside the .MDB file. It is why you can't index on a MEMO field, and why certain other operations don't work, either. I remembered that after I calmed down enough to stop pulling out my hair. Just as well, since my hair is already sparse enough to be tough to find when on such a rampage.

I have to rethink the design that had this requirement. Don't worry about finding a solution of another kind.

I already knew that there is no .VALUE or .OLDVALUE for an unbound text box, but didn't think hard enough about the implications of those same properties for a text box bound to a MEMO field. That's what I get for shooting from the hip. But then, as I'm from the USA, there are those who will say it is typical cowboy mentality. I would object to such cowboy characterization except that I am also wading through the cow "patties" associated with Access's little quirks such as this one. I was already good a bull-throwing. So I guess I must really be a cowboy. And here I didn't even realize it!

 

[Banana]

FWIW -

I understand that's one of several reasons why some folks recommend that Memo fields be broken off into a one-one table with a separate form to contain it. That way you can just use the form's dirty event for your routine, though it will have unforunate side effect of requiring saving edits to the other fields before you can enter the subform. Alternatively show only a snippet of the memo on the form with a double-click to open a "Memo form" to do the desired operation. I dislike an extra click but at least it would bring a bit of sanity, perhaps.

 

[SOS]

I already knew that there is no .VALUE or .OLDVALUE for an unbound text box,

Well, actually that isn't correct. There may be no OldValue but there definitely is a value property for an unbound text box and it is the default still of a text box.

 

[The_Doc_Man]

SOS, sorry but I proved that to myself last week by stepping through my code that does a "Does this control have this property" test. It was looking for .VALUE and not finding it. There is no value and no old value on an unbound text because both of them are derived from the underlying record. When there IS no underlying record, there is no value before or after you change the contents.

You are correct that the default property of a text box exists, but it isn't what you think it is.

 

[Banana]

Out of curiosity, do you get a Value if you access the AccessField instead of Control?

e.g., instead of

Me.txtMyMemo.Value

Do this:

Me.MyMemo.Value

or to be more explicit:

Me.Recordset.Fields("MyMemo").Value

(Technically, I don't think AccessField are same thing as Recordset Field, but I can't work out what are the collection that contains the AccessField objects when we use the Me.<someFieldName>)

 

[SOS]

SOS, sorry but I proved that to myself last week by stepping through my code that does a "Does this control have this property" test. It was looking for .VALUE and not finding it. There is no value and no old value on an unbound text because both of them are derived from the underlying record. When there IS no underlying record, there is no value before or after you change the contents.

You are correct that the default property of a text box exists, but it isn't what you think it is.

And I just had a test this morning which proved that a value does exist.

MsgBox Me.UnboundTextBox.Value

worked fine.

So, .Value does work and is a property of the text box.

 

[Banana]

Wait, I'm confused.

I thought The_Doc_Man said he didn't find a value property for a textbox bound to a memo field?

I went and tested, binding a textbox to a memo field, and verified it still had a Value property. Serves me right for not having done so earlier but here's what I did for OnCurrent:

Debug.Print Me.txtbar.Value 'OK
Debug.Print Me.txtbar.Properties("Value") 'Error

Looks like Value can be accessed but not via Properties collection.

 

[SOS]

And, I just tested this:

Dim ctl As Control
Dim prp As Property
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        For Each prp In ctl.Properties
            Debug.Print prp.Name
        Next prp
        
    End If
    
Next

and it did not return a .VALUE as a property in either the bound or unbound case.

 

[vbaInet]

The_Doc_Man may be referring to the properties collection of a control?

Edit: Yes SOS that was what I meant.

 

[missinglinq]

Sorry, Doc_Man, but SOS is correct. Unbound textboxes do, indeed, have no OldValue Property, but a Value Property.

 

[SOS]

But the value property is not apparently part of the control properties collection or some other method is needed to get that one as a named property in the collection.

 

[vbaInet]

Even when you do:

ControlName.Properties("Value")

The Value doesn't seem to be part of a control's property. I suspect that there are certain properties that cannot be set or retrieved via the control's Properties collection.

 

[The_Doc_Man]

The problem, as I've done more research on this, is that it is a question of what is explicit and what is inherited.

If you do an explicit test for the named property, it isn't there. But then, that is because of differences in the "default" property read back when you look for a control. That default differs between various bound control types. In many cases, the default value is the .TEXT property, which is the FORMATTED contents of the control, usually a text box or combo box. Note that a .TEXT property isn't available for many controls unless that control has focus at the time.

I'm finding a TREMENDOUS inconsistency in trying to ascertain when a given control has gone "dirty" on me because my goal is to show alternate color for modified fields until the user clicks my COMMIT button (which triggers field-level audit scans to see what got changed.) An OnDirty event fires for text boxes and combo boxes under certain circumstances, but not for list boxes. Even for a non-multi list box, it is devilishly hard to use VBA code to find out whether something has changed, mostly because of the inconsistencies of the displayed values.

It gets even more complex if you have a "spot" query as the source of the form's lookup. Like, the controlsource is a query. Right now I'm trying to populate a junction table with three lookups. The server comes from a list. The chosen administrator comes from a list. The administrator's role - primary, secondary, emergency, or other - comes from a list. Three combo drop-downs, bound to a three-field table - and NONE of the damned things behave correctly. The "old value" isn't in the list either.

This is the craziest part of all, and it is why I get so totally frustrated. I set a break point in the LostFocus routine just before I call the code where I would evaluate whether the control is dirty (so I can give it a pale pink background). I open the Locals window. I browse the object. In the list of properties, I can SEE the .VALUE and .OLDVALUE - but when I browse the .PROPERTIES collection, those names are not there. Yet I am trying to write a fairly generic routine to test whether an object has changed or not. If I can't test whether the object has a given property, I have to write a lot more special-purpose code that is going to drag me down. It is getting to the point that I am about to have to track what I believe to be the current contents of the underlying record in the OnCurrent routine and compare THAT to the control contents in order to decide what is and what isn't dirty. That makes life a LOT tougher, because that means I can't write a general subroutine or function to do this test. It now has to know what fields are actually on the form. A programmer's nightmare, the structural equivalent of having to denormalize a table. Yecch!

 

[Banana]

Fascinating. I should take a closer look at the inconsistency you report, The_Doc_Man - I actually wasn't aware until this thread that Value property isn't enumerated in the Properties collection.

Anyhoo, I see few workarounds:

1) In a tradeoff for efficiency, you could forget about per-control and just use Form's Dirty event to build an array of old values then compare the array in the Form's BeforeUpdate. For the visual aspect, trade off the accuracy for simplicity by having it go pale pink on the OnChange event unconditionally and do not clear until Form's AfterUpdate.

2) Subclass the controls so you now have a consistent Dirty event applied for all controls - have that custom class listen to more generic events (e.g. KeyPress, Click, etc) with a static variable to flag whether it was dirtied and raise the event. That would also need to be cleared somehow (e.g. Form's AfterUpdate & Form's Undo for instance).

3) Instead of relying on controls event and its inconsistencies, use an ADO recordset with events and listen to WillChangeField event. However, it could be trading a set of inconsistencies for another, though.

Hope this give you few possible ideas...

 

[The_Doc_Man]

Thanks for the comments, Banana - I was heading towards the OnChange idea next anyway. But now I have to figure out how to track that a change has occurred outside of the control itself.

Thanks for the idea about ADO, but I don't want to use ADO in this context or any ADP features because I'm shooting towards a lowest common denominator case.

It's just a mine field right now, because I cannot find a reasonable and consistent way - that works across all control types - to assure that a given object has or has not changed. This is the stuff programmer nightmares are made of. It is, as I said earlier, like being FORCED to denormalize your tables. I am being forced to de-generalize my code. This is definitely NG - Not Good.

It stems from the fact that Access is "mostly" object-oriented. If it were COMPLETELY object-oriented, I wouldn't have this problem. I would have the tools I needed to back-track inherited properties. I could find out if the object has the property or if its specific variant has the property. And this is a case where parentage nomenclature arises.

In Access, a control's parent is either a Detail section of a form or a Detail section of a Tab control - or something like that. But programming-wise, an object's parent can be the core object that was customized to make the new object type. Like, a text box is a control. So is a check box. The properties they have in common stem from being derived from a generic control. The unique properties they have derive from having been modified by object-encapsulating a generic control and augmenting it. Thus, the new object inherits the generic properties while adding some new ones.

Just to clarify... the reason i want to know the things I've been asking about is that when I try to write general purpose code to be touched by ham-fisted users, I have to try to prevent error traps in order to assure product stability. For example, if an object doesn't have a .VALUE I will get the "Object does not have the named property." That would occur if Access thinks the object is unbound OR if the object is one that doesn't use VALUE because it uses something else. So I can't pre-scan the property tables to see if my reference would cause a trap. It is OK for ME to see that while developing, but for a user to see this, I'm screwed.

If an object would cause a trap in one case and not in another (because of bound or unbound status), it is not enough to see the .CONTROLTYPE to know what it has in it. I can't just look up whether the property exists because of layered inheritance and lack of tools to follow back the inheritance chain. Since .VALUE/.OLDVALUE can only occur for BOUND controls, I have no mechanism to support unbound controls with change detection. Yet I still want to know when changes have been made so I can visually warn my users that something is going on that will require special action on their part. Maybe the data won't be stored, but it might affect HOW the data would be stored.

Needless to say, I'm finding that Access limitations are getting serious.

 

[Banana]

Thanks for the comments, Banana - I was heading towards the OnChange idea next anyway. But now I have to figure out how to track that a change has occurred outside of the control itself.

Yeah, which is why I think class module with a static variable and a binding to the form itself would help you to build your Dirty replacement event.

Thanks for the idea about ADO, but I don't want to use ADO in this context or any ADP features because I'm shooting towards a lowest common denominator case.

You probably know that already but for benefits of other readers - ADO isn't really a ADP feature per se - it's equally available in MDB/ACCDB and it's perfectly fine to mix DAO and ADO (which I do myself- in fact, I just converted one troublesome DAO recordset to ADO for performance reasons though I tend to do 95% DAO, 5% ADO). But I can understand the concern about adding one more reference to be broken.

It stems from the fact that Access is "mostly" object-oriented. If it were COMPLETELY object-oriented, I wouldn't have this problem. I would have the tools I needed to back-track inherited properties. I could find out if the object has the property or if its specific variant has the property. And this is a case where parentage nomenclature arises.

An idea... Can you get away with doing a blind check?

ctl!Value = ctl!OldValue

By using bang operators (and losing the efficiency + compile-time check), you may be able to actually access the Value. Of course, if it doesn't work, then it'll crap out.

In Access, a control's parent is either a Detail section of a form or a Detail section of a Tab control - or something like that. But programming-wise, an object's parent can be the core object that was customized to make the new object type. Like, a text box is a control. So is a check box. The properties they have in common stem from being derived from a generic control. The unique properties they have derive from having been modified by object-encapsulating a generic control and augmenting it. Thus, the new object inherits the generic properties while adding some new ones.

Well, I should point out that it's not quite that way in COM world. In COM world, everything is an interface. Sure, there's CoClass, but you're not supposed to work with actual classes but rather the interfaces. Even though the VBA Object Browser will tell you it's a class, it's lying here (it has no concept of an interface - to it, interface == class). It's interfaces. This is why we can set a textbox to a Access.Control object, but Access.Control interface doesn't have a .Value but it does have .OldValue. Interestingly, it seems to use .ItemData and .Columns, which are usually for the listboxes and comboboxes but not actually for the textboxes.

So that's one more thing for you to check - if you can work with .ItemData or other property that's actually related to the interface Access.Control, it may be able to work sanely for all controls that are accessible through Access.Control interface.

Else, do a Is comparsion:

Public Function DoIt(obj As Object) As Boolean

Select Case True
   Case obj Is TextBox
      ...
   Case obj Is ListBox
      ...
   Case obj Is CombBox
      ...
End Select

You also can use TypeName() or VarType() function for a bit of reflection.

Since .VALUE/.OLDVALUE can only occur for BOUND controls, I have no mechanism to support unbound controls with change detection.  Yet I still want to know when changes have been made so I can visually warn my users that something is going on that will require special action on their part.  Maybe the data won't be stored, but it might affect HOW the data would be stored.

FWIW, I've seen other developers settling on a solution of using a temporary table created as a binding for unbound controls to provide the illusion of bound controls and thus enjoy the full functionality.


HTH.