How to base the contents of a field on another field(combo box)

[wer4him]

I have control1(field1) on a form that I would like to auto-populate based on the choice made in a combo box control2(control source = column1 of table/query) on that same form.

I would like the control source for control1 to remain from the table that the form is based on (field1), but the contents populated from the same table/query that the combo box control2 is choosing from, only from a different column.

I have been successful with creating an unbound control and displaying the contents of the additional column, but I would like to store that data in the form's table.

I know it would be easier to store only the contents of Control2 in the form table and use a query to bring the data from both tables but this is a very large database and field1 is already populated and I have lots of reports and other queries and forms based on it. So rather then revamping the entire database structure I would like to autopopulate field1 of the form table.



Any help is appreciated

[alansidman]

Look here

http://www.baldyweb.com/Autofill.htm

[changedsoul]

This is nice to know. How does it work for a combo box though. Say you wanted to use combo boxes to filter multiple items. for example, One combo box you can select "Male" or "Female". This would then populate another combo box with a list of bike types that only belong to what is selected in the first combo box. Then maybe the third combo box would fill with a list of rims that are available for the Bike type selected in the second combo box...and so on.


Is this easy to do? and how can it be done?

[June7]

That is dependent (cascading) comboboxes. Review: http://datapigtechnologies.com/flash...combobox2.html

[changedsoul]

Hello, thank you for that link to the video. This is exactly what I wish to accomplish. I have tried to duplicate it but cant seem to get it to work at all. I have attached a small sample database, can you please have a look and see what it is I am doing wrong?

[RuralGuy]

See if this one gets you a little further along.

[changedsoul]

Thanks. This helped me allot. I thought I had tried what you did. In fact when I tried to get the one I uploaded to match, I had problems. It turns out, Access seems to be very picky when making the query settings. I had to do it a few times before it seemed to stick. First time I tried, I made the settings but the combo boxes still didnt work. I went back into the query settings and they were back to the way they were before. It would have been nice to get a message saying something was wrong.

It took me a few times, but now I am in the habit of going back and checking my settings to make sure they stick. So thank you for giving me a sample that works so i was able to find my problem.

Also, I noticed you had another key word "DISTINCT" to limit duplicate entries. Is this knowledge you have gained from experience, or is there a gui approach to obtain that same query?

[June7]

What is 'qui approach'?

UniqueValues (i.e. DISTINCT) is a property available in the query designer on the Property Sheet.

Or type the SQL statement in query SQL View or directly in combobox RowSource property (the query designer can be invoked from the RowSource).

[changedsoul]

Thank you. I am still learning where things are in access. I didn't see it in the properties because I was un aware I had to click out side the query builder where the combo boxes are.
Thanks again.

[RuralGuy]

Are we ready to mark this thread as Solved and start a new thread with another issue?

[June7]

Don't know what you mean by "had to click out side the query builder where the combo boxes are" - doesn't make sense to me. The Property Sheet shows for query in design view.

[wer4him]

Look here

http://www.baldyweb.com/Autofill.htm

Thank you this is what I wanted to do. I appreciate all the help. You can consider this resolved.