Solved More than 100 comboboxes (1 Viewer)

jack555

Member
Local time
Today, 11:21
Joined
Apr 20, 2020
Messages
47
Final data taken out is in CSV format with all fields are coded like "1",2", "3" etc. There are more than 100 comboboxes with structured dropdown items. Comboboxes can be linked to tables or value list. Tables will be more helpful since queries can be built to describe the codes but value lists not. The downside I foresee is, it may be required to create 100+ tables which I think is not ideal.

Is there any standard approach to handle the dropdown values for numerous comboboxes? I need only codes for CSV but with description for all other purposes.

Please help.
 

arnelgp

error reading drive A:
Local time
Today, 15:21
Joined
May 7, 2009
Messages
12,315
use 1 table, add "extra" column (signalling the "combo" owner it belongs), eg:

tblComboValues (table)
ID (autonumber)
Values (text)
comboNumber (integerr)

Values----------------------------comboNumber
"value 1"-------------------------1
"value 2"-------------------------1
"another value"------------------2
"another value 2"----------------2

etc.

for first combo on form, the rowsource:

select ID, [values] from tblComboValues where comboNumber = 1;
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:21
Joined
Jan 20, 2009
Messages
12,279
It can be done in one table by having a ComboID field to designate which combo RowSource they apply to.

Value Lists can also have text descriptions by having more than one column and designating which column is displayed and which is Bound.

However I get the feeling the data structure might not be ideal.
Remember that the csv can be generated from a query. You don't have to store the data like that.

What you have now will require design changes every time another attribute is added.
 

jack555

Member
Local time
Today, 11:21
Joined
Apr 20, 2020
Messages
47
Thank you for the suggestion. I tried the above method, it helps to keep all combo in one table. While building the query to pull the description for various fields, I failed to build the relationships since they all in one table. How to achieve this? Below is an example query what I have not but need gender description and promo description in separate fields.

1615867902544.png
 

arnelgp

error reading drive A:
Local time
Today, 15:21
Joined
May 7, 2009
Messages
12,315
you need to separate the Customer table from the combo table.
see this demo.
 

Attachments

  • CustomerCombo.accdb
    524 KB · Views: 18

jack555

Member
Local time
Today, 11:21
Joined
Apr 20, 2020
Messages
47
you need to separate the Customer table from the combo table.
see this demo.
Yes, this is the outcome I look for. In this way, ComboID will be unique and this needs to be stored in the main table. But comboID values not always a running unique number. For example "1" in the gender field represents "Male" and another field may represent "Applicable" or something else. It is little tricky for me but believe there may be a way to do it. Below is the sample representation of the scenario. There are numerous comboboxes that exist to ensure standardised data collection for CSV export.

Combo1
ValueDescription
1Male
2Female

Combo2
ValueDescription
1Applicable
2Not Applicable

Combo3
ValueDescription
1Taxi
2Bus
3Car
4Bike

Table1
IDCustNameGenderPromoTransport
1Jack112
2Mark121
3Susi214
4Julie213

Query Output
IDCustNameGenderPromoTransport
1JackMaleApplicableBus
2MarkMaleNot ApplicableTaxi
3SusiFemaleApplicableBike
4JulieFemaleApplicableCar
 

arnelgp

error reading drive A:
Local time
Today, 15:21
Joined
May 7, 2009
Messages
12,315
use only 1 table for your Combo.
see the demo, the field ComboID (autonumber) is the one being
saved to tblCustomer (Gender and Promo field).
 

jack555

Member
Local time
Today, 11:21
Joined
Apr 20, 2020
Messages
47
use only 1 table for your Combo.
see the demo, the field ComboID (autonumber) is the one being
saved to tblCustomer (Gender and Promo field).
I understood as below. I will have 4 fields in the combo table with additional combovalue filed. Will store the comboID(autonmber) in the main table. can use query to show combovalue or combodesc or both as required. thank you.

ComboID(autonumber)ComboValueComboDescComboObjectID
11Malecombo1
22Femalecombo1
31Applicablecombo2
42Not Applicablecombo2
 

arnelgp

error reading drive A:
Local time
Today, 15:21
Joined
May 7, 2009
Messages
12,315
there is also a sample query (query1) on the demo.
you need to put "as many" tblcombo to the query.
in your case you need to drag 3 tblCombo to the query
and linked each field to its comboID.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:21
Joined
Feb 19, 2002
Messages
31,689
Here is a mini-app that I use in all my apps that uses a single table for all your simple lookup lists.
 

Attachments

  • TableMaintExample20210204.zip
    196 KB · Views: 18

jack555

Member
Local time
Today, 11:21
Joined
Apr 20, 2020
Messages
47
Here is a mini-app that I use in all my apps that uses a single table for all your simple lookup lists.
Thank you. Your app has so many features. Could you please refer me to the exact feature where multiple combo items stored in one table. I am so novice in using access. Sorry for asking the basic question again.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:21
Joined
Feb 19, 2002
Messages
31,689
Your question was:

Is there any standard approach to handle the dropdown values for numerous comboboxes?

And that is why I posted the table maintenance app.

1. If you have a hundred combo boxes on a single form, your table is not normalized and you need to read up on normalization before you go any further. This app will be a nightmare for you if you don't change your method. You are essentially using Access to be a spreadsheet. Relational databases are not spreadsheets and you will be verrrrrrrrrrrry unhappy with Access if you think it works like excel.
2. The app I posted allows you to manage hundreds of lookup lists in a single table with a single update interface. You would use a query in your rowsource that selects the "table" you want from the table in the mini-app. You would reference it by its tableID.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:21
Joined
May 21, 2018
Messages
4,879
1. If you have a hundred combo boxes on a single form, your table is not normalized and you need to read up on normalization before you go any further. This app will be a nightmare for you if you don't change your method. You are essentially using Access to be a spreadsheet. Relational databases are not spreadsheets and you will be verrrrrrrrrrrry unhappy with Access if you think it works like excel.
Listen to Pat. If you have 100 combos you have a failed design..
 

Users who are viewing this thread

Top Bottom