Populating one combobox based on value in another?

[Blitzer]

Hi,

My first post the forum and I have to say I'm very new to Access and relational databases, so please be gentle with me.

I bought a dirty great book on Access, have searched the Access help and looked at various forums on the internet to try and find the answer to what I'm sure is a very straight forward question, but I can't find it!


Basically, all I am trying to achieve is to restrict the options of a combo-box that gets it's data from TABLE A, depending on the selection made from a combo-box based on data from TABLE B.

For example, assuming the tables contain the following related data...

TABLE-COLOUR

black
white
red
green
blue

TABLE-MODEL

Ford Ka
Ford Fiesta
Ford Focus


With TABLE-CARCOLOUR being a means to link car/colour options...

TABLE-CARCOLOUR

Ford Ka - white
Ford Ka - blue

Ford Fiesta - red
Ford Fiesta - green
Ford Fiesta - blue

Ford Focus - black


I have tried various different queries to achieve the desired effect but the queries either fail to work entirely or simply don't do as I require. I have been able to create a query and subsequently a form that gives me the required combo-boxes to choose the MODEL and COLOUR, but I can never get the COLOUR combo-box limited as per the options in TABLE-CARCOLOUR.

Can someone please provide me with a simple and easy to follow example or if not point me in the right direction as to what feature in the software I should be using?

I'm sure it's something that should be quite easy to achieve in Access, I just can't find out how to do it!

Thanks in advance for any help.

 

[georgedwilkinson]

You can search on this forum for cascading combo boxes. I personally never use them so I'm not much help there.

There's a sample at http://www.access-programmers.co.uk/forums/showpost.php?p=452314&postcount=31, though I've never evaluated it.

 

[missinglinq]

Here's a tutorial that's helped a lot of people get started with cascading comboboxes:

http://bytes.com/forum/thread605958.html

 

[Blitzer]

Seems to me I was a bit niave when I puchased Access and did not fully realise just how involved it was going to be! I think one of the major problems for a newbie to Access is knowing the "technical" terms for the objectives they are setting out to achieve. It can be very difficult searching for something when you don't even know what it's called.

I think I have also been fooled (or foolish lol) in thinking that from the product description of Access that it would have been a litle easier to get into than it has. Access is clearly a powerful piece of software in the right hands - just not mine at the moment.

Anyway, thank you for the replies so far at least I know know what to search for. I can't say I've managed to follow the example linked so far, but then my brain has turned pretty much to mush today having spent hours going round in circles trying to find the answers.

I certainly did not realise how much "programming" would actually be necessary to get the most out of Access, particularly to achieve what I thoguht would have been very straight forward. Seems I've got a lot of learning to do. Thanks again.

 

[Alansidman]

Check out the video tutorials at http://www.datapigtechnologies.com/AccessMain.htmhttp://www.datapig.com They are very helpful for beginners.

 

[georgedwilkinson]

I have to disagree that the power in Access is in programming. I think you can do amazing things with Access without writing the first line of code.

The thing you really need to learn is how to design a database correctly (not something you can learn from Access help files or samples). With a properly normalized design, Access just kinda works right.

 

[boblarson]

Try checking this out for starters:

http://www.accessmvp.com/strive4peace/

 

[Blitzer]

Check out the video tutorials at http://www.datapigtechnologies.com/AccessMain.htm They are very helpful for beginners.

Thanks for the link, I found a very easy to follow video on there that seemed to do exactly waht I was after. Or so I thought, as when I tried to replicate the method in the video I found that because I'm using Access 2007 that I don't have the same options in the Query builder making it impossible for me to replicate the video clip method! Once again, I've ground to a halt because I can't suss out where to look next.

 

[georgedwilkinson]

You can run the Access interactive command tool to figure out how to do things in 2007 that you know how to do in older versions:
Interactive Command Reference - For 2007

 

[Blitzer]

You can run the Access interactive command tool to figure out how to do things in 2007 that you know how to do in older versions:
Interactive Command Reference - For 2007

I don't know how to do things with previous versions either lol, but I know what you mean so I'll take a loko at that and see if I can't suss it out. Thanks for the heads up. At least I feel I'm looking in the right areas now for the answer.

 

[Blitzer]

Ok, I must be completely stupid. I have looked at numerous samples of cascading combo boxes that all seem to do what I require,but no matter how hard I try I can't get my own to work. Am I so stupid that I can't use this wonderfully powerful piece of software to perform this simplest of tasks? I keep searching the forum for cascading combo boxes and keep come back to thread I've read already or replies that say search the forum. This is bugging the hell out of me. please help at this rate I can see my copy of Access ending up in the bin!

 

[Alansidman]

Blitzer;
Here is another link that I have used. Don't give up on this. Once you accomplish it, it will make your life easier. http://www.fontstuff.com/access/acctut10.htm

Alan

 

[Blitzer]

Well I have now managed to replicate an example of cascading combo boxes, which I'm hoping will help me with trying to do the same in my actual database where I need to be working with the data being held in various tables and bringing that data together to create a new record for a new purpose. In doing so I need to use cascading combo boxes to limit the choices available in certain combo boxes depending on the choices selected from other combo boxes. Hmmm.

 

[dkinley]

There you go! Already using a quasi-modeling mode of thinking =]

Think of computing as a linear algorithm, that is, things can only happen one step at a time.

Problem: Data is in various tables.
Goal: Bring various data together to create a new record.
Solution: Use cascading combo boxes.

1. Use a combo box to get data from one table.
2. Use another combo box that get's its data from another table (optional: limit the data in combo box based on 1st combo box).
3. Write all of the combo boxes to a new record in a 3rd table.
.... so on an so forth.

Once you are done 'psuedo-coding' then it's the translation to Access terms to get it to work. There are still functions I am discovering already built in that do the things I need and didn't know existed.

The beauty of examples is that substitution is a wonderful mechanism to achieve one's goals, e.g., replace their table name with your table name, their field name with your field name, so on and so forth.

Achieving understanding is another issue and it seems you are getting a good handle on it by actually replicating another example instead of just throwing your hands up. Stay with it - you're actually overachieving by tackling cascading combo-boxes as your first project.

-dK