SOLVED

Querying values by choosing a field from List of fields/columns name from a combobox,

Copper Contributor
Dear master of Access
I have a question hope you will find it interesting to answer it.
I have a form which have 2 combo boxes, one got list of Fields/columns from my table/query. I am trying to choose a field from combo box and then based on that field I pass a value from another combo box . But at the moment I am not able to do it and I can't figure out how to make it work.
Any help will be much appreciated. The idea is I could choose any field from combo box and pass the value to accordingly and get result in a form with docmd......
Thank you.
8 Replies

@Asad_khan1971 This is a very common function. We usually refer to it as "Cascading Combo Boxes".

 

A search on that term will turn up a plethora of explanations and examples.

I don't think my explanation of the problem was elaborate enough. I try again.
So I have a combo box which lists the fields from a table not the values of a colum. My other combo box contains the values of fields which are listed in the combo box one. What I am trying here is select a field name from first combo and then select value from the second combo and then click a button which will open another form to show the records based on criteria passed. I can do it with value combos but combo with table field name with other combo contains value I am can't figure out how to do it. Sorry it's not the case of cascading combos.

@Asad_khan1971 

 

"... combo box which lists the fields from a table ..."

 

"My other combo box contains the values of fields which are listed in the combo box one"

 

That's a first for me. I never heard of that approach before, so I'm curious as to why you do this.

 

I suspect what you have is a table with fields like "Data1", "Data2", where you're encoding values into the names of the fields? Is that the case?

 

If so, that's the real problem and this rather unconventional approach to cascading combo boxes (and that is, indeed, the same principle) is a result of that table design.

 

So, what ARE some examples of the real field names in this table? And what are some examples of the values in those fields you want for the second combo box?

It's a kind of survey db the example of field names are:
Field/column name: Was data synchronised?
Value: yes or no
Please see attached image.
Basically it's a try to create a query tool. So user could select a field choose the criteria for the field...
Thanks for your time and interest

Please also note value in combo 2 are already there
best response confirmed by Asad_khan1971 (Copper Contributor)
Solution

@Asad_khan1971 As I feared, it sounds like you have a "spreadsheet style" table. That is fine for Excel, but not for a Relational Database Application, such as those built in Access.

 

The solution -- the one that will pay off most in the long run -- is to correct this flawed table design, as described in this series of blog posts.

 

Fields in this table should actually be records in a single field, or a few fields, depending on what is in each of them. Following the correction to the underlying table, this particular problem with combo boxes will simply not exist.

Thank you sir, with utmost respect, I don't think I am able to convey my point. My db is working fine, this was just an idea.
I am sorry it's all my fault.
Thank you again for your time.

@Asad_khan1971 

 

I think I understand pretty well. Over the last 25 years of working with Access Relational Database Applications, I've seen this mistaken approach to table design many, many times. Excel tables  have multiple columns, each of which is one kind of thing. You only offered one column name, but that's enough to give us the picture. That one is a yes/no question, is it not? And you have many other fields like it, all of them being yes/no questions, do you not? That is the problem. It is the reason you are trying to create this cascading combo box set up to compensate for that design problem. 

 

I could offer a work-around that would look up the field in that table and use that to populate the second combo box with values, but that would be a work-around, not a solution.

 

Please invest some time in learning more about Relational Database Design. Your survey will be significantly better.

Here is a link to one of the more popular survey templates. It's been downloaded thousands of times. It'll be useful to you as well, I believe.

1 best response

Accepted Solutions
best response confirmed by Asad_khan1971 (Copper Contributor)
Solution

@Asad_khan1971 As I feared, it sounds like you have a "spreadsheet style" table. That is fine for Excel, but not for a Relational Database Application, such as those built in Access.

 

The solution -- the one that will pay off most in the long run -- is to correct this flawed table design, as described in this series of blog posts.

 

Fields in this table should actually be records in a single field, or a few fields, depending on what is in each of them. Following the correction to the underlying table, this particular problem with combo boxes will simply not exist.

View solution in original post