Access VBA Combobox bug (1 Viewer)

[Royce]

I have this form in form_load to set the value list of a combo box

Dim strList As String
    strList = "-32768,Form;-32766,Macro;-32764,Report;-32761,Module;1,Table;4,Table_ODBC;5,Query;6,Table_Lnk;-30000,WKSSet;"
    Me.cboObjectType.RowSource = strList

I have code to display the rowsource for the combobox when I double click it.
The rowsource becomes this:
"-32768";"Form";"-32766";"Macro";"-32764";"Report";"-32761";"Module";"1";"Table";"4";"Table_ODBC";"5";"Query";"6";"Table_Lnk";"WKS_Set"


The original code was more complex, the above is my attempt to simplify it as much as possible.


I have tried a number of things including a decompile, deleting the combo box and recreating it. Changing the order, changing the value of WKS_Set, nothing helps. Using semicolons everwhere, using quotes,not using quotes. Nothing has helped. Access just keeps dropping the value associated with WKS_Set.

Anybody seen this? Am I overlooking something obvious?

 

[GinaWhipp]

Is the combo box set for two columns? Because all I see is an odd number and that means one columns is blank.

 

[isladogs]

It works for me in the form load event.
However, if you tried adding the second version in the property sheet, you had omitted the type value for the final item 'WksSet'.

Earlier you said that should be -30000 which looks wrong to me.
If you are referring to a linked Excel worksheet, that also has a type value=6 as it is treated as a linked table

So I think your list should end with something like "6";"Table Link, Excel Sheet"
This will put both items together on one 'line'

 

[arnelgp]

an alternative to using string as Rowsource of your list,
you can use an Array and fill your combo:

Dim arrList As Variant
Dim var As Variant
arrList = Array("-32768,Form", "-32766,Macro", "-32764,Report", "-32761,Module", _
    "1,Table", "4,Table_ODBC", "5,Query", "6,Table_Lnk", "-30000,WKSSet")
For Each var In arrList
    Me.cboObjectType.AddItem var
Next

 

[Royce]

There are two columns, and all of the pairs are "pairs". Including the last one. If I step through the code, I can print the rowsource in immediate mode and it is correct. But as soon as I hit F5 and go back to form view the last value does not show in the combo. That's why I added the ability to double click the control and display the rowsource. At that point, the rowsource is wrong. the -30000 value has been stripped out.


I am not using this in Excel. I have an Access table (DBObjects) that has a selected set of objects that I use to create databases for new projects. It tracks dependencies, then automatically copies the the primary Form/report and all its subforms, queries, tables, VBA modules, classes to a new database. I am trying to add the ability to use Sets within Sets. Thus a "WKS_Set" is an object type that does not exist in the MicrosoftWorld, so I used -30000 as it's type, thinking that is not likely to be used in the future. I also tried 7,9, and other values. Why a combo would care about a value is beyond me, but I'm trying to figure it out. All the code worked UNTIL I added the -30000,WKS_Set pair.

 

[Royce]

My actual code looks more like what you have. A string is passed to trusted, long used and debugged function that uses the .AddItem property, along with a variable for the # of columns. I posted the simple code to make it easier to spot the error.

 

[Royce]

It works for me in the form load event.

Strange, that is where my code is being called. I tried Access 2010, and 2016. Same problem with both.



Did you include the -30000,WKSSet pair?

 

[isladogs]

Strange, that is where my code is being called. I tried Access 2010, and 2016. Same problem with both.

Did you include the -30000,WKSSet pair?

Yes I did - even though the -30000 value is incorrect!
I also tested in both 2010 & 2016.
Of course, you could just do this as a value list in the property sheet instead

I also added the following lines to the form load code (though these could also have been done via properties)

Me.cboObjectType.RowSourceType = "Value List"
    Me.cboObjectType.ColumnCount = 2
    Me.cboObjectType.ColumnWidths = "2cm;2cm"

Finally I set InheritValueList to 'No' in the property sheet to ensure nothing was being retained from previous use. It all still worked for me

See attached

 

[Royce]

I discovered it works fine if the control is unbound. As soon as I bind it to the field it breaks!

 

[Royce]

Setting the "Inherit Value List" to No fixed the problem for me.


Thanks!

 

[isladogs]

You're welcome.
For another example of strange combo box behaviour, have a look at this example Combobox zombies puzzle

Now what are you going to do about that 30000 value?

 

[Royce]

I'm going to use the -30000 until I find a reason not to. I have a reason for it, which I spelled out in some detail in one of my other replies.



This is NOT a typical usage of the ObjectType (Type in MSysObjects). It is a special purpose table where I have a need to include "custom types" as well as the standard Microsoft internal Jet SQL- types, which are officially "undocumented" as far as I know, and different from documented access types such as acForm.



There are several other "undocumented types" floating around in Access. I did not want to use sequential numbers, such as 9, or 10, as Microsoft might use them, nor did I want to use the -32756 as it might be used. -30000 is a number in the middle of nowhere that is not likely to be used now or later.

 

[isladogs]

I'd missed that previous reply.
However, I still don't see the point.

The values are widely used and I'm sure I could find official documentation if I tried. Using both the Type and Flags values from MSysObjects will identify each type of database object precisely.

For example, see these links
https://www.access-programmers.co.uk/forums/showthread.php?t=293579
https://access-programmers.co.uk/forums/showthread.php?t=295597

 

[Royce]

The point is using a custom type number greatly simplifies my application code in this specific instance.



I have used the standard types many times in many different databases, all the way back to Access 95. This is the first time I have ever needed a custom type. However, using it means that the existing recursive code works with only a minor modification. I tried several approaches that did not require a "custom code". All quickly got messy as I worked with them.



In the early days, Microsoft, and almost every post you saw that used the ObjectTypes, said they were "undocumented" so use with care. You don't see that much anymore, but as far as I know they are still officially "undocumented".


There are other types that are rarely used. For example 3 is Containers. 8 is Navigation Pane.


Also, there are gaps in the numbers. I know what some of them are. Others, no clue. Since I don't know the what, or why of the gaps, I'm not going to use one of the gap numbers. Again, -30000 seemed a reasonable choice.


Some place there is some code, probably C++, maybe even old C code, that has an enum list. I'd love to see the list.

 

[isladogs]

As you will know, in the early versions of Access, system tables were added or dropped with each version.
At that time, it was risky relying on system tables for anything long term.
Having said that MSysObjects is one of the few that have been in every version with no structural changes.

Things quietened down around A2000 but there were a lot of changes with the new ACCDB format in A2007.
A2010 added 3 more rarely used tables but since then no further changes have occurred.

If you are interested in learning more about the 60+ system tables, see this article on my website.
Purpose of system tables

I think using -30000 is safe enough if you want to use this approach.
As you say, certainly much safer than using a gap such as 7.
However, my point was why use real Type values for everything else and invent this one value?
Using both Type and Flags would give you what you need and more with no risk of ambiguity.
That's how Access distinguishes the different object types so why not use the same method?

Good luck with your project

 

[Royce]

The problem with "real types" is I am using the invented value for a phantom object that never really exists. There is no corresponding object in MSysObjects. In a sense, it is a "virtual" object, but I'm not sure that is the right word either.



The closest type would be a "container". (Mathematically the phantom object is a Set of other real objects.) I thought about using the "Container" (type 3), as a Container would never be in the DBObjects table, and it is a judgement call (your my design review team in this case.) but I thought making an explicit, non-standard type would make it clear that it is an invented type for a phantom object.

 

[isladogs]

Now I'm utterly baffled
I had assumed all this time it was a linked excel worksheet.
Now you're telling me it is a phantom object that doesn't exist in MSysObjects ... or anywhere else(?)
Yet you need to be able to select it in a combo....
My head hurts

Perhaps you should use the square root of -1 for the type value of this imaginary object

 

[isladogs]

Just looked back at the earlier posts in this thread
For information:
-32756 = Data Access Pages (no longer supported so perfectly safe for you to use!)
8 = Subdatasheet

For my own interest, I ran a couple of queries on the MSysObjects table of a large split database and got the following:
a) count of all MSysObjects
b) list of objects with type -32757,2,3 or 8

 

[Royce]

or as we used to say LOL!. I did notice someone mentioned Excel, but I ignored it. This has nothing to do with Excel. No wonder your head hurts. This project is used for building databases from an Access Model database that has a whole bunch of forms, reports, queries and modules. Think of it as an object repository that we pull from to create a new database at the start of a project.



Alas, Access VBA does not support imaginary numbers. (I think Fortran IV did??) Anyhow, I will just go with my imaginary 6 foot white rabbit. I am giving away my age.

 

[isladogs]

If you can have phantom objects, then I think Access should be able to support imaginary numbers as well. I shall just call you Harvey from now on.

I get the idea of an object repository but for my own benefit I'd still like to know what type of object this really is!
Something that doesn't exist yet but will be needed in the future?