ComboBox behavior need explanation (1 Viewer)

FredCailloux · Feb 7, 2017

I have a ComboBox on an Access Form that is populated with a recordset comprising two columns. Column1 called lngCatIndex, a Long number and column2 called txtCategory, a corresponding text field. That ComboBox named combCategory is modified by a VBA command as such:

Code:

combCategory = MyVariable

When executing that code the combCategory change it's value to the proper corresponding display. The user will see the text only because the combCategory properties are as such:

linked column : 1
number of columns : 2
columns width : 0cm, 3cm

Which means that the linked column is a number, which value is invisible and the displayed column is column2 the text representing the column1 value. example:

Long number, String text
not visible, visible
1, Capacitor
2, Transistor
3, Inductor
etc...

Here is what appear to me as a rather strange behavior of my ComboBox. If I assing

Code:

combCategory = (a long number like     2      )

then the combCategory will display the corresponding Text from column two, Transistor.
If I assign

Code:

combCategory = (a string text like "Capacitor")

then the combCategory will display the corresponding Text from column two, Capacitor.

That assigned number or text are of radically different type. I would assume that one of the two would generate a VBA error, but no!
Furthermore, by assigning a different type to the combCategory the ComboBox will still display the corresponding Correct Value!!! despite the difference in assigned type. It's as if the ComboBox could accept any value of any column regardless of whether it is for column1 or column2 or whatever else and find a correspond value in it's recordset and accept that record as the corresponding one

That is definitely Not a "normal" computer coding philosophy.

If I had a ComboBox with 25 different columns of type Integer, Long, String, Double, String, Double, Integer, Long...
How would that work ? Rather confusing if you ask me!

Can anyone offer an explanation to this peculiar flexibility ?

Uncle Gizmo · Feb 7, 2017

Not sure without firing up MS Access and having a look. However I thought I should mention the usual way to do it is to control the combobox "default value" with VBA.

Sent from my SM-G925F using Tapatalk

FredCailloux · Feb 7, 2017

Uncle Gizmo said:
Not sure without firing up MS Access and having a look. However I thought I should mention the usual way to do it is to control the combobox "default value" with VBA.

Sent from my SM-G925F using Tapatalk

What is your YouTube channel? I'm interested

Uncle Gizmo · Feb 7, 2017

Hi Fred, it's here:-

https://www.youtube.com/user/UncleGizmo

Subscribe to My YouTube Channel HERE:-

https://www.youtube.com/user/UncleGizmo?sub_confirmation=1

Sent from my SM-G925F using Tapatalk

HiTechCoach · Feb 10, 2017

FredCailloux said:
I have a ComboBox on an Access Form that is populated with a recordset comprising two columns. Column1 called lngCatIndex, a Long number and column2 called txtCategory, a corresponding text field. That ComboBox named combCategory is modified by a VBA command as such:

Code:

combCategory = MyVariable

When executing that code the combCategory change it's value to the proper corresponding display. The user will see the text only because the combCategory properties are as such:

Which means that the linked column is a number, which value is invisible and the displayed column is column2 the text representing the column1 value. example:

Here is what appear to me as a rather strange behavior of my ComboBox. If I assing

Code:

combCategory = (a long number like 2 )

then the combCategory will display the corresponding Text from column two, Transistor.
If I assign

Code:

combCategory = (a string text like "Capacitor")

then the combCategory will display the corresponding Text from column two, Capacitor.

That assigned number or text are of radically different type. I would assume that one of the two would generate a VBA error, but no!
Furthermore, by assigning a different type to the combCategory the ComboBox will still display the corresponding Correct Value!!! despite the difference in assigned type. It's as if the ComboBox could accept any value of any column regardless of whether it is for column1 or column2 or whatever else and find a correspond value in it's recordset and accept that record as the corresponding one That is definitely Not a "normal" computer coding philosophy.

If I had a ComboBox with 25 different columns of type Integer, Long, String, Double, String, Double, Integer, Long...
How would that work ? Rather confusing if you ask me!

Can anyone offer an explanation to this peculiar flexibility ?

Sure ... if Not In List then add

You let out a key property:

Limit to list = ?

Based on the description of the behavior I would bet Limit To List = NO.

My guess is that you are expecting he behavior that happens when you set Limit To List = Yes.

Have you ever used the Combo Box's event for On Not In List?

FredCailloux · Feb 14, 2017

Not quite, the Limit To List is Yes. My interrogation is rather based on the fact that whether I force VBA to assign a Long number or a text String to the combCategory ComboBox it will accept both and for it's proper corresponding field. If it is the column1 that is linked to the data table then I assume that only the column1 Long number should be the accepted assignment and that VBA would generate an error if I tried to assign a text value. This is not was we see. Instead the ComboBox will accept the text value as if it was a match to the displayed field, column2, which is not the linked column. ???

HiTechCoach · Feb 14, 2017

Ah ... this is not about the user interface but manipulation it with VBA code.

It is up the the programmer to do it properly. When you take over control and assign the value with VBA code then you must also do all the work. You must validate the data before assigning it to the combo box.

FredCailloux · Feb 18, 2017

HiTechCoach said:
Ah ... this is not about the user interface but manipulation it with VBA code. It is up the the programmer to do it properly. When you take over control and assign the value with VBA code then you must also do all the work. You must validate the data before assigning it to the combo box.

Do yo mean that when using VBA I can shove anything, any type in that ComboBox and it will accept the value whatsoever without any consideration to the Control Source value type ? What about the linked Source, will it still get modified according to an invalid assignment still ?

The_Doc_Man · Feb 18, 2017

Am I just confused or did you reference columns 1 and 2 for a combo box? Technically, that is wrong because list/combo columns are ZERO-based. The first column is actually #0, isn't it? Or were you being a bit "loose" in your nomenclature?

HiTechCoach · Feb 19, 2017

FredCailloux said:
Do yo mean that when using VBA I can shove anything, any type in that ComboBox and it will accept the value whatsoever without any consideration to the Control Source value type ? What about the linked Source, will it still get modified according to an invalid assignment still ?

YES. It is your job as the developer, when you take over control, to do the data validation first before "shoving" the data in the combo box.

The User interface of a combo box does enforce "rules" to prevent the user from enter in bad data.

The VBA code level interface is for developers not users. Access assume the developer knows what they are doing so it let them. It does not enforce the same User Level "rules" or control over what can be done with code.

In my experience over the past 30+ years, it has been this way with ALL the 20+ developer platforms I have used, including Access.

MarkK · Feb 19, 2017

What is the ControlSource of the combo? If it is bound to a field in a table, then the control will only accept data of the type of that field. If the combo is not bound to a ControlSource then the combo can accept and display any simple data type.

FredCailloux · Feb 21, 2017

The_Doc_Man said:
Am I just confused or did you reference columns 1 and 2 for a combo box? Technically, that is wrong because list/combo columns are ZERO-based. The first column is actually #0, isn't it? Or were you being a bit "loose" in your nomenclature?

Actually , in my description I omitted to mention that there is a Key column for which the numbers are auto generated by access and that column would be column 0.

ComboBox behavior need explanation (1 Viewer)

FredCailloux

Registered User.

Uncle Gizmo

Nifty Access Guy

FredCailloux

Registered User.

Uncle Gizmo

Nifty Access Guy

HiTechCoach

Well-known member

FredCailloux

Registered User.

HiTechCoach

Well-known member

FredCailloux

Registered User.

The_Doc_Man

Immoderate Moderator

HiTechCoach

Well-known member

MarkK

bit cruncher

FredCailloux

Registered User.

Similar threads

Users who are viewing this thread