Data for Combo Boxes - Most efficient method?

[Maria]

Please excuse my lack of experience – I am just starting to program in
Access. I am using Access 2003 – not sure if this is important to know.

I have a DB with 3 forms y 4 tables. One table “tblValorParaCampos†is only
used for binding Combo Boxes with data.

Basically all 3 forms use the above table to populate Combo Boxes that exist
on each form.

This is an example of one form:

Form: fmProducts
Combo Boxes – Marca, MaqTipo, Modelo, Combustible
Table: tblProducts
Data for the Combo Boxes is using the “tblValorParaCampos†table and each
Combo box is bound using their respectful “Row Source†properties.

This method works – there is no issue with the Combo Boxes being populated
or working – it works properly.

My problem is that my users are not allowed to enter new data in the Combo
Box. If there is to be new data – I would place the new data in the
“tblValorParaCampos†table due to my boss not wanting anyone to enter new
data for any Combo Box. And the DB will be empty when my users start using
the DB.

However, I am not sure if this is the most efficient way of getting data
into the Combo Boxes. I was thinking it would be a better idea for the Combo
Box to get its data source from its respectful table without having to use a
different table. Obviously I would still have to populate any new data for
any Combo box.

But I cannot figure out a way of doing so - Is there a more efficient way?

All help is much appreciated.
thx
Maria

 

[Bob Quintal]

Please excuse my lack of experience – I am just starting to
program in Access. I am using Access 2003 – not sure if this is
important to know.

I have a DB with 3 forms y 4 tables. One table
“tblValorParaCampos†is only used for binding Combo Boxes with
data.

Basically all 3 forms use the above table to populate Combo Boxes
that exist on each form.

This is an example of one form:

Form: fmProducts
Combo Boxes – Marca, MaqTipo, Modelo, Combustible
Table: tblProducts
Data for the Combo Boxes is using the “tblValorParaCamposâ€
table and each Combo box is bound using their respectful “Row
Source†properties.

This method works – there is no issue with the Combo Boxes being
populated or working – it works properly.

My problem is that my users are not allowed to enter new data in
the Combo Box. If there is to be new data – I would place the
new data in the “tblValorParaCampos†table due to my boss not
wanting anyone to enter new data for any Combo Box. And the DB
will be empty when my users start using the DB.

However, I am not sure if this is the most efficient way of
getting data into the Combo Boxes. I was thinking it would be a
better idea for the Combo Box to get its data source from its
respectful table without having to use a different table.
Obviously I would still have to populate any new data for any
Combo box.

But I cannot figure out a way of doing so - Is there a more
efficient way?

All help is much appreciated.
thx
Maria

You want the separate table for the combo boxes or even a table for
each combobox. When the database grows to many thousands of records,
the combo will load much faster than if it needs to select the few
distinct rows to populate the combo box.

 

[Arvin Meyer MVP]

Use the NotInList event of the combo box to add values to it. I have a
generic code sample that will work for any combo box in the database at:

http://www.datastrat.com/Code/NotInListCode.txt

and a sample database using that code at:

http://www.accessmvp.com/Arvin/NotInListDemo.zip

 

[Maria]

Thank you Arvin

I have look at the DB but as I understand how it works - this will allow
ANYONE to add a new data item to the list, however, as stated below my boss
does not want anyone but me to enter in new items. That is the reason why I
used a separate table to enter in data - so that when there is new data to
put in the combo I would manually enter the new data in the table.

Thanks for the DB as well as a new area to view other types of tips for
Access, I appreciate it.

Maria

 

[Arvin Meyer MVP]

Instead of adding entries, just set the NotInList property to Yes and block
all additions.