I have a set of routines that do various things to controls - enable, visible, lock and so on. It is faster, cleaner and prevents screen flickering when setting a property that is already set. Here is one such routine:
Code:
Public Sub gbl_CtlsAbled(ByRef arr As Variant, ByVal Stav As Boolean)
Dim i&
For i = LBound(arr) To UBound(arr)
With arr(i)
If .enabled <> Stav Then .enabled = Stav
End With
Next i
End Sub
All works fine when I pass it textboxes and comboboxes, but when I try to pass it checkboxes, it is doing an unwanted conversion to ByVal. The value of the checkboxes is what I am getting in the routine, rather than a reference to the control, so of course, any action I take in the Sub has no effect on the actual control. Trying to set properties throws an error, and setting the value does not change the control.
Any ideas why checkboxes are not handled correctly, and if there is a way to force them to behave?
I do it with a paramarray instead. Works for me and a little simpler to call without defining the array.
Code:
Public Sub gbl_CtlsAbled(ByVal Stav As Boolean, ParamArray Ctls() As Variant)
Dim i&
For i = LBound(Ctls) To UBound(Ctls)
With Ctls(i)
If .Enabled <> Stav Then .Enabled = Stav
End With
Next i
End Sub
I do it with a paramarray instead. Works for me and a little simpler to call without defining the array.
Code:
Public Sub gbl_CtlsAbled(ByVal Stav As Boolean, ParamArray Ctls() As Variant)
Dim i&
For i = LBound(Ctls) To UBound(Ctls)
With Ctls(i)
If .Enabled <> Stav Then .Enabled = Stav
End With
Next i
End Sub
Oh yeah, I'd forgotten about ParamArray. That is cleaner, but it's still not working - I'm still not getting a ref to the original control. When I put in a breakpoint and step the code, I have the value of the checkbox, but when I change the value of the Ctls() element, nothing happens to the control. When I leave the routine and go back to the calling code, the value of the control has not changed. I'm still getting a ByVal version, when it's a checkbox.
Also I never seen an issue when setting a property that is already set causes delay or flicker.
Disabling a disabled control, hiding a hidden control, etc should not cause any delay or issue.
Yes, yours works. Mine did not, but I was also trying to set the value of the control, and I remember now that I have run into this before. The code was
Code:
ctl(i) = False
and that doesn't work. It has to be
Code:
arr(i).Value = False
Apparently the default property of the checkbox somehow does not make it into the sub. Switching to ParamArray as you suggested made the .Enabled setting work correctly, but the .Value also had this additional wrinkle.
Also I never seen an issue when setting a property that is already set causes delay or flicker.
Disabling a disabled control, hiding a hidden control, etc should not cause any delay or issue.
Really? I have been dealing with that for as long as I can remember - enabling an already enabled control and similar activities cause an annoying flicker. I would have thought that Access would first check to see is a property is already set to the desired value, and not do anything if it is. But no - it flickers. And I ran some timing tests as well. It was a long time ago, and I don't remember the exact values, but it was significantly faster to first test a property myself and NOT set it if it was already the way I wanted.
I meant that I had a local copy of the control's value, rather than a pointer to the actual control. When I changed the value, the local instance in the array reflected that change, but the control did not. Adding the .Value specification fixed that.
In any case, all is good now. Thank you for the steer.
If you are still having trouble ---
Give each control a name that is different from the name of the bound column so ActiveYN becomes chkActiveYN and CustomerID becomes txtCustomerID.
Once you do that, Access always knows what you want. You get different intellisense from each name.
I meant that I had a local copy of the control's value, rather than a pointer to the actual control. When I changed the value, the local instance in the array reflected that change, but the control did not. Adding the .Value specification fixed that.
Do you mean that in connection with: ctl(i) = False
=> As this is a variant array, you do not pass a value to the standard property of the control element, but define a new entry for the array field.
Code:
Dim ControlArray(0) As Control
Set ControlArray(0) = Me.ExampleTextBoxControl
ControlArray(0) = "abc" ' <- without Set => value is passed instead of reference
=> default property of ExampleTextBoxControl is used because the array field cannot accept a value.
Debug.Print ControlArray(0).Name
vs.
Code:
Dim VariantArray(0) As Variant
Set VariantArray(0) = Me.ExampleTextBoxControl
VariantArray(0) = "abc" '<-- new value for array item .. index 0 now contains the string "abc" and no longer the reference to the TextBox.
Debug.Print VariantArray(0).Name '=> Error
If you are still having trouble ---
Give each control a name that is different from the name of the bound column so ActiveYN becomes chkActiveYN and CustomerID becomes txtCustomerID.
Once you do that, Access always knows what you want. You get different intellisense from each name.
Thanks, I already do that, always. Controls on my forms are always named cboSomething, txtSomething, chkSomething, tglSomething, cmdSomething, sfSomething, tabSomething and so on. I ran into just such confusion as you mention once, from an Access wizard that generated such names from source columns. Since then I have used this naming convention, always and everywhere.
Do you mean that in connection with: ctl(i) = False
=> As this is a variant array, you do not pass a value to the standard property of the control element, but define a new entry for the array field.
Code:
Dim ControlArray(0) As Control
Set ControlArray(0) = Me.ExampleTextBoxControl
ControlArray(0) = "abc" ' <- without Set => value is passed instead of reference
=> default property of ExampleTextBoxControl is used because the array field cannot accept a value.
Debug.Print ControlArray(0).Name
vs.
Code:
Dim VariantArray(0) As Variant
Set VariantArray(0) = Me.ExampleTextBoxControl
VariantArray(0) = "abc" '<-- new value for array item .. index 0 now contains the string "abc" and no longer the reference to the TextBox.
Debug.Print VariantArray(0).Name '=> Error
Hm, I'm going to have to experiment with that to get it clear in my head. My routines for fiddling controls generally set graphic properties, like when a set of conditions requires me to disable or enable some input fields, or displaying/hiding error messages. There of course I always refer to the properties by their name. But with the checkboxes, I was trying to set their values, and tried to use the default property that works normally, when referring to the controls directly, rather than to a reference passed into a routine. But there are places in the code where it seems to me that it worked that way. I'm not in front of it now, but I will look at it again and see if I can track down exactly what I am doing where, and get this clear for myself once and for all. Thank you for the explanation.
Now that's interesting. Variants are incredibly useful in some very specific situations, but they also have issues, like this. Do you know of anything that explains how variants behave in various situations, when there are several possibilities? I generally try to avoid them, and I may well wind up changing all this to ParamArrays or arrays of specific controls types, just to avoid such issues. I've been pretty happy with these tiny routines as a way of cleaning up and speeding up code, making displays friendlier, and getting lots done in a very tidy manner, but I may need to tweak it all a bit to make it completely solid.
That bit about using .Value is probably a good idea. I've been moving more and more to spelling things out, even when they are not necessary, just to keep it all clear. For instance, I always specify ByRef and ByVal, and use named parameters instead of positional notation. Not relying on a default property is probably another habit I should cultivate.
I played with trying to torture variants a while back, putting an array into a variant, but making it an array of variants, each of which could contain its own array, again of variants, and so on. I was looking for how deep I could go with such references, but instead of confusing the compiler, I confused myself before I managed to break the compiler, so I gave it up as pointless. I suppose there probably is some limit on that sort of construction, but I have no idea what it might be.
In the example shown above, there is only one possibility. Because Variant can accept values, when assigning "abc", no check is made to ask the object of the stored reference if it has a standard property that can accept a value.
In the example shown above, there is only one possibility. Because Variant can accept values, when assigning "abc", no check is made to ask the object of the stored reference if it has a standard property that can accept a value.
But that's what I mean by different possibilities. A variant CAN store a value, but it also CAN store a reference pointer. You say no check is done for a standard property, but such a check COULD be done - part of the underlying code with variants is determining what sort of object it contains. If you use a property, it assumes an object and look for that object's property. If you assign a value, even if a reference was passed in that variant, it makes some sort of internal change and now considers it a value. It COULD check to see if the variant contains an object, and if it does, whether that object has a default property.
I wonder now, if my attempted use of the default property simply reset the variant - when the routine starts, it pointed to the checkbox, but after the execution of arr(i) = true, the original reference to the checkbox was lost and the variant now simply contains a boolean value - my routine changes the variant from its original value, rather than following that original value back and changing the value of what it pointed to.
It's late here and I'm wrecked from messing with a SQL Server database all day, but I'll look at it tomorrow and see if I can verify that. It would make sense, though.
I think the bigger thing to be aware of is that vba is both sloppy and "overly helpful".
Often I see people post code and they ask why it does not work because it worked somewhere else, and my question is why did it ever work. VBA allows you to omit a lot of syntax due to default properties, and will often Cast values when you may not expect it. Often this is helpful, but sometimes real confusing.
In the overly helpful area VBA will Cast variant variables automatically by taking a guess at what you want, and people take this for granted. For example If it looks like it is supposed to be a date, then vba will subtype the variant as a date. You can test this with an unbound textbox
if you put a number or string it subtypes it to a variant string
If you put in a date it casts this as a date
Code:
Debug.Print VarType(Text22.Value)
It does not cast numerics or booleans.
Howerver if you did this code it does automatically case
Dim X as long
Debug.Print VarType(Text22.Value) ' returns 8 string
x = text22.value
debug.print vartype(x) ' returns 3 for long
Other weird casting issues and default values and where people get confused because of Access being overly helpful (except when it is not)
I have no idea why this is failing, I simply want to make the background red if the Date is before Today. The results I am getting are all over the place.
Hello, I have a form with a few option buttons that use OpenArgs to change the dates in a report. If the date is a Friday #7 then highlight the textbox on the report. I tried using Weekday([OpenArgs])=7 under conditional formatting but it never changes the color. How should I approach this...
This sub is on a form to catch the previous control and works correctly if the control is a command button. If the control is a textbox "Ctl" holds the contents of the box as a string. Private Sub btnAbout_Click() Dim ctl As Control Set ctl = Screen.PreviousControl ' getHelp...
Hi all, Is there any reason anyone can think of as to why you would NOT use Option Explicit? The lack of doing so came back to bite this user big time. I did not spot although inadvertently I was on the right track. :) https://eileenslounge.com/viewtopic.php?f=29&t=39787&start=20 Solution is...
Has anyone work with this data type? Is there any tricks. I helped someone with a database that was originally just a standard date. When they converted the field to date time extended it broke a lot of things. Most date functions and formatting functions do not work with this. Things like...
Hello, Hope you can give some ideas, because I have tried to find one and no success. I have to Subforms (A, B): I have data on SubForm A I want to pass to Subform B but this is how: 1) SubForm A I have a combo box which filter the data I wan to pass to SubForm B 2) after filter data in...
I think the bigger thing to be aware of is that vba is both sloppy and "overly helpful".
Often I see people post code and they ask why it does not work because it worked somewhere else, and my question is why did it ever work. VBA allows you to omit a lot of syntax due to default properties, and will often Cast values when you may not expect it. Often this is helpful, but sometimes real confusing.
In the overly helpful area VBA will Cast variant variables automatically by taking a guess at what you want, and people take this for granted. For example If it looks like it is supposed to be a date, then vba will subtype the variant as a date. You can test this with an unbound textbox
if you put a number or string it subtypes it to a variant string
If you put in a date it casts this as a date
Code:
Debug.Print VarType(Text22.Value)
It does not cast numerics or booleans.
Howerver if you did this code it does automatically case
Dim X as long
Debug.Print VarType(Text22.Value) ' returns 8 string
x = text22.value
debug.print vartype(x) ' returns 3 for long
Other weird casting issues and default values and where people get confused because of Access being overly helpful (except when it is not)
I have no idea why this is failing, I simply want to make the background red if the Date is before Today. The results I am getting are all over the place.
Hello, I have a form with a few option buttons that use OpenArgs to change the dates in a report. If the date is a Friday #7 then highlight the textbox on the report. I tried using Weekday([OpenArgs])=7 under conditional formatting but it never changes the color. How should I approach this...
This sub is on a form to catch the previous control and works correctly if the control is a command button. If the control is a textbox "Ctl" holds the contents of the box as a string. Private Sub btnAbout_Click() Dim ctl As Control Set ctl = Screen.PreviousControl ' getHelp...
Hi all, Is there any reason anyone can think of as to why you would NOT use Option Explicit? The lack of doing so came back to bite this user big time. I did not spot although inadvertently I was on the right track. :) https://eileenslounge.com/viewtopic.php?f=29&t=39787&start=20 Solution is...
Has anyone work with this data type? Is there any tricks. I helped someone with a database that was originally just a standard date. When they converted the field to date time extended it broke a lot of things. Most date functions and formatting functions do not work with this. Things like...
Hello, Hope you can give some ideas, because I have tried to find one and no success. I have to Subforms (A, B): I have data on SubForm A I want to pass to Subform B but this is how: 1) SubForm A I have a combo box which filter the data I wan to pass to SubForm B 2) after filter data in...
Good characterization - I sometimes liken such software to a Labrador - sloppily overhelpful to the point of being obnoxious. I try very hard to avoid that in my own software - being so helpful that it actually causes the user more annoyance than assistance. I'm fortunate that I am in very close contact with most of my users, and I constantly stress to them that if they have a problem with my software, it's almost certainly my fault and not their stupidity - the software should match their workflow requirements. Best case is when they don't even have to think about it, but just use it, because it matches what they need to be doing at every point. As a consequence, they are not at all bashful about letting me know when something is not quite right, like being overly helpful.
You're right about the automatic casting, and that's another thing I have gradually changed to avoid, like specifying CBool() around an expression, even when it has an obvious truth value. It's a bit of extra typing, which unfortunately I'm not very good at, but it's worth it to avoid the occasional miscast by VBA.
And thanks for the links. Some good reading there.
@pdanes
Here is another one to know. Most people are unaware of this and I know from experience that this can be mind boggling trying to figure out what went wrong.
This sub is on a form to catch the previous control and works correctly if the control is a command button. If the control is a textbox "Ctl" holds the contents of the box as a string. Private Sub btnAbout_Click() Dim ctl As Control Set ctl = Screen.PreviousControl ' getHelp...
@pdanes
Here is another one to know. Most people are unaware of this and I know from experience that this can be mind boggling trying to figure out what went wrong.
This sub is on a form to catch the previous control and works correctly if the control is a command button. If the control is a textbox "Ctl" holds the contents of the box as a string. Private Sub btnAbout_Click() Dim ctl As Control Set ctl = Screen.PreviousControl ' getHelp...
I've read about this bit with parentheses, but never had a problem with it, since I never call a Sub with parentheses. But I very much believe that it could lead to some serious hair-pulling.
The referenced article, What do you mean "cannot use parentheses?" is great - clearest explanation of the matter I've seen. Well, every language has its quirks. If it was easy, people wouldn't need programmers.
