Creating ComboBoxes from Code

[DennisBrockhurst]

Hi

Is it possible, in Access 2003, to create combo boxes in VBA code a
required?

I have a requirement to allow a user to select from a large list o
products from a number of sources but I don’t know how big the list wil
be or how many products they will select. Therefore, I want to provid
the user with another combo box once they have used the last one

 

[Simon Lloyd]

H

Is it possible, in Access 2003, to create combo boxes in VBA code a
required

I have a requirement to allow a user to select from a large list o
products from a number of sources but I don’t know how big the list wil
be or how many products they will select. Therefore, I want to provid
the user with another combo box once they have used the last one.Dennis, welcome to The Code Cage, why do you need many comboboxes rathe

than have a multi select box

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[DennisBrockhurst]

Many reasons really,

1) The order in which the selection is made is very important and needs
to be preserved. If I am using an array of combo boxes (such as in VB)
I can use the index of the combo box as defining the order.

2) Using combo boxes, I can pull much more through from the source that
is hidden from the user about the products that they have selected.

3) With combo boxes, i can allow the user to choose the source they are
selecting from on the fly.

 

[Simon Lloyd]

Many reasons really

1) The order in which the selection is made is very important and need
to be preserved. If I am using an array of combo boxes (such as in VB)
can use the index of the combo box as defining the order

2) Using combo boxes, I can pull much more through from the source tha
is hidden from the user about the products that they have selected

3) With combo boxes, i can allow the user to choose the source they ar
selecting from on the fly.What would be the max number of comboboxes?, if there aren't too many i

may be better to already have them present but not visible until
selections i made...et

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Steve Schapel]

Dennis,

It is certainly possible, and easy, to manipulate the Row Source, as well as
other properties, of a combobox, using a macro or VBA procedure.

It is also possible, and easy, to have the list available in one combobox
dependent on the selection made by the user in another combobox.

Is this really the kind of thing you are seeking?

You can also create, and position on your form, a new control - but in my
experience the need to do so is extremely rare.

 

[DennisBrockhurst]

Dennis,

It is certainly possible, and easy, to manipulate the Row Source, a
well as
other properties, of a combobox, using a macro or VBA procedure.

It is also possible, and easy, to have the list available in on
combobox
dependent on the selection made by the user in another combobox.

Is this really the kind of thing you are seeking?

You can also create, and position on your form, a new control - but i
my
experience the need to do so is extremely rare.

Hi Steve

I think you are right and this is one of those rare moments. You sa
"You can also create, and position on your form, a new control" and tha
is exactly what I am trying to do. I have no problem setting th
control up by code once it exists, I just don't knoww how to create i
by code.

Many thanks
Denni

 

[Douglas J. Steele]

Realistically, you don't want to create a new control from code.

There's a limit to how many controls you can have on a form during the
lifetime of the form. Doesn't matter if you delete them and compact the
database: once you've had that many controls, you cannot add more.

The approach suggested by Simon (i.e.: decide a maximum number of combo
boxes you'll ever allow, add them all then toggle their visibility) is a far
superior approach.

 

[Steve Schapel]

Hi Dennis,

Just wondering... is another possible approach to explore would be to put
your combobox in a continuous view subform? So in other words, in any given
instance, the user can make one, or any number, of selections?

Otherwise, have a look at the Application.CreateControl method in VBA.

 

[DennisBrockhurst]

Steve,

Thanks for your help here. I think Doug had killed the idea
completely. if I only add one combo box at a time, the form will
eventually become unusable.

I will have to find another way of achieving what the user wants.

Many thanks for all your help
Dennis

 

[Steve Schapel]

Dennis,

Yes. I was not aware that the tally of controls added to a form would
include controls added programmatically but not saved at runtime. I would
have assumed not, but I believe Doug!

However, the main suggestion in my earlier reply was to use a continuous
view subform. You haven't commented on that idea. It would imply having a
table that you could bind the subform to, which, if I understand you
correctly, could then be a suitable way of you temporarily storing and using
the selections entered by the user.

 

[DennisBrockhurst]

Dennis,

Yes. I was not aware that the tally of controls added to a form would
include controls added programmatically but not saved at runtime.
would
have assumed not, but I believe Doug!

However, the main suggestion in my earlier reply was to use
continuous
view subform. You haven't commented on that idea. It would impl
having a
table that you could bind the subform to, which, if I understand you
correctly, could then be a suitable way of you temporarily storing an
using
the selections entered by the user.

Steve

I wanted to think long and hard about that before I responded as it i
not where I wanted to go but I think you may be right. From a littl
testing the form seems to be only able to accept 892 controls as a max.
what this means is that whatever I do in programatically adding
control will cause the app to crash over a period of time.

I think what you are suggesting is probably the best way to go forwar
wihout restrictin the user.

many thanks
Denni