Auto Populate Drop Down Box on Form

[jpe2007]

Hi all, I hope I ask this question properly I working with MSAccess 2007.

QUESTION: HOW DO I AUTOPOPULATE A DROP DOWN BOX ON A FORM BASED ON WHAT IS SELECTED FROM THE PREVIOUS DROP DOWN BOX?

I have the following three tables:

1. tbl_Channel: this table represents the channel sales are made in. There are two fields:
channelid
channelname

Lets assume for this example there are two channel names PHONE and RETAIL.

2. tbl_Source: this table represents the source within the channel above. The fields in this table are:

sourceid
sourcename
channelname (this is a lookup to the table above)

Lets assume for the example there are four sources
PHONE Location1
PHONE Location2
RETAIL Retail1
RETAIL Retail2

3. tbl_salesman: this table represents the sales ids of people making the sales. The fields in this table are:

salesid
salesman_name
sourcename (this is a lookup to tbl_source)
channelname (this is a lookup to tbl_channel)

Lets assume the following salesman for the example,

John Location1 PHONE
Sue Location2 PHONE
Dave Retail2 RETAIL
Frank Location2 PHONE
Henry Retail1 RETAIL

So now that I have set the scenario here is my question???

IF I have a form with the following fields>
CHANNEL (drop down selection)
SOURCE (drop down selection)
SALESNAME (drop down selection)
SALES ID id of salesname

> How can I have SOURCE autopopulate with only fields based on channel chosen. For example if I choose PHONE then in the Source Drop Down I would only see Location1, Location2 as options.

> Then after I select Location1 or Location2 I would then only see in the salesman drop down the available names for source chosen

> Then in the final form field, salesid I just want to represent the associated sales ID for the person chosen.

So I am looking to autopopulate the the drop downs as each previous option is selected?

Sorry for the length but I didn't know a shorter way to ask the question. I hope I have asked properly>>>>

THE OPPOSITE AND MAYBE EASIER WAY WOULD BE TO BE ABLE TO SELECT A SALESNAME IN THE FIRST DROP DOWN AND THEN ALL THE OTHER FIELDS WOULD AUTOMATICALLY FILL IN BASED ON THAT SELECTION...IS THIS AN EASIER APPROACH.

I would be pulling queries to report on sales by channel, channelsource and by saleman.

Thanks in advance

 

[PHV]

Did you search the FAQ area of this forum ?

http://www.tek-tips.com/faq.cfm?pid=702

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886