Fill Combobox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I found contradictory information about how to fill a combobox. I would like
to fill it from a recordset but I am not sure if that is possible. I found
some code that does it somewhat like this:

Me.cbox.RowSourceType = "Table/Query"
Me.cbox.recordSet = rs

Unfortunately this does not work. I tried another way:

rs.MoveFirst
Me.Kombinationsfeld8.RowSourceType = "Value List"
While Not rs.EOF
Me.Kombinationsfeld8.AddItem Item:=rs.Fields.Item("testtext")
rs.MoveNext
Wend

Now I got a combobox with as many lines as items in my recordset but they
show up blank in the box and I cannot choose any of these blank lines. Can
anybody tell me what I did wrong here ?
(I am using MS Access 2003)

Thanks
 
Dear Thomas:

From your sample code, it would appear "rs" is a RecordSet. This RecordSet
would be based on either a query or a table. Somewhere in the code you did
not show, this would be apparent.

The combo box properties do not include a "recordSet" property. A good way
to exploit the facilities for object oriented programming in Access is to
type in everything in lower case and see if it changes automatically.

For example, when you type in:

me.cbox.rowsourcetype = "Table/Query"

It changes to:

Me.cbox.RowSourceType = "Table/Query"

This confirms that you have successfully referenced object "Me" and property
"RowSourceType". It indirectly shows you have correctly referenced "cbox"
even though the name did not contain any capital letters. If you had
misspelled this, it would not then have found that the object "cbox" had a
property "RowSourceType" and that property would still have read
"rowsourcetype".

I strongly recommend this as a "best practice" when writing object oriented
code. Use mixed case in the object names you create. This makes them
easier to read in many cases, but especially it allows quick confirmation of
any early bound references. That will avoid many errors in programming, as
I'm about to illustrate.

Now, in the line of code:

Me.cbox.recordSet = rs

I expect the capital S came from you typing it that way. Had you entered
(as I just suggested):

me.cbox.recordset = rs

it would have come back:

Me.cbox.recordset = rs

Here was a big lost opportunity. When it does not come back, as you should
have expected:

Me.cbox.RecordSet = rs

it should be clear that the combo box "cbox" does not have a property
RecordSet (the actual name of a property which does exist for some objects).
I do not know for an absolute fact if it is the case that no objects or
properties in the library from Microsoft are without any capital letters,
but I do know that, if I type one in in lower case and nothing gets
capitalized, then I get very suspicious.

So, this is the primary resource you have to find out the error here. There
is no such property of a combo box.

So, given that you do have a table or query behind the recordset you used,
there must be a table name or SQL string which can be the RowSource of the
combo box. It is this string, used to set up the recordset, which can be
used to assign the RowSource of the combo box. Not only this, but the
assignment can be made in the form's design view, already attached to the
combo box. Both the RowSourceType and the RowSource can be set there, and
need not be coded at all unless they must change programatically. Indeed, I
have never needed to change the RowSourceType of a combo box, even when its
RowSource does change in code.

It would seem you are probably making something that is very simple into
something laborious to construct and which will be unnecessarily difficult
to maintain, given that it is usually possible to set the combo box contol's
properties once as you design, and never need to code them unless they need
to change dynamically.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison
 
Hi Tom,

thanks for your detailed answer, I didn't know about that lowercase wrinkle.

The bad thing is MS IntelliSense shows the property "Recordset" for the
combobox. Or is my reference not completly correct? (I didn't work with vba
before).
Also the code I posted is not my exactely my code I abbreviated the name of
the combobox and typed it directly through IE again (sorry).

On the other hand the second way is working I just trouble with another
property which I figured out in the meanwhile.

The reason I am using a recordset is the following:
I am working on a multi user database (implemented in Access on Jet). A
problem is there are many comboboxes and all of them do query different
columns in the same table (via Access queries) which causes bad performance.
Therefore my idea was to use one recordset and then operate with the
recordset instead of querying again and again against the database.
 
Dear Thomas:

Your explanation of using a recordset to obtain a value list for a combo box
is interesting.

Usually, it is the other way around. We often must requery a combo box to
keep its list up-to-date against an ever changing multi-user database, and
then test the value one final time with the database locked just before
inserting or updating the database. Making the combo box list "static" but
fast is the opposite of this.

Actually, I don't think it words the say you are expecting. If you assign a
RowSource to the combo box, it will query the first time it needs some data,
and will read all rows then. The combo box will not requery unless
specifically told to do so. It is not clear to me how you are gaining any
performance here, only creating work for yourself.

I'd like to check into there being a RecordSet property for a combo box.
That seems wrong to me. It may take a bit of study.

Tom Ellison
 
Tom,

I have a list of "values" that occurs in that table many times and new
values are not added very often, so it is not that necessary to have it
up-to-date for each user at each time. Therefore I think it could be worth to
make it "kind of static". Am I wrong with that?

To give you a better overview, I have a from with a registercontrol and on
each page many comboboxes. I do have troubles in working with that form know
and I presume that the reason is all these boxes querying against the
database. Therfore I thought about:
1.) Query them on enter only
2.) Use a recordset since many of these boxes query against the same table

Regarding the property I read on msdn that there shouldn't be such a
property but when I type "me.cboxname." and then press Ctrl+Space and scroll
down it shows up, what confuses me somehow.
 
Dear Thomas:

If you are not concerned with any possible changes in the list for the combo
box, then you may wish to just use the combo box RowSource as a table or
query. This obtains the information from the list only once for an instance
of the form. It cannot be done less frequently than that. What I'm saying
here is that you have gained nothing in terms of limiting the access to the
database over your connection with the approach you have chosen, in spite of
the labor that goes into it. The default behavior of a combo box is the
same, to obtain the list only once, unless you specifically program the
control to requery that list.

You are not wrong, but you are coding unnecessarily, and this may have a
chance of introducing "bugs" as well. It is also a program maintenance
issue.

The combo box does not query until the first time the list is needed. This
may be equivalent to what you say, "Query them on enter only"

Using a recordset without filtering to the specific rows needed for a given
"subset" or rows needed for a specific combo box would not seem to me to
give any advantage such as what you expect. I suppose there could be a
condition where this could occur, but I'm skeptical of this. The analysis
to determine whether it is possible to gain anything this way would be
complex. A simple test might give results impirically. You may wish to do
this.

Actually, I have had considerable experience using recordsets compared with
using queries, and queries have always been faster in my tests.

I tested, and there is a Recordset property of a combo box as it shows up
when coding VBA. I cannot explain this. I would point out that, if you
type in "recordSet" it will change that to "Recordset".

How this property can be used I cannot tell. I say property, because if you
type a period after the word Recordset, it shows no component objects or
properties. That pretty much makes it a property, which is not typical of a
Recordset, which is an object with Methods as well.

When I put ? Me.cbox.Recordset in the immediate pane, I get:

Compile error:

Variable not yet created in this context.

Again, it is a "variable" not an object. This suggests you cannot assign an
actual recordset object to it. It is mysterious to me why this occurs at
all. I would not think it should be in the list as a property, object, or
method of a combo box. You may have caught an error here. I'm open to
anyone for some explanation of what this is.

Tom Ellison
 
Back
Top