Cascading Combo Boxes and Automatic Update of a field in another table based on the selection

[craignovice]

Would appreciate some much needed help, I am a novice at Database but have created some simple ones. I am looking to create a Database to track/log orders from clients...



I have a table (tblsalesagent) where I intent to input information like a contacts form....
Fields>>>
SalesAgentName
Address
Email
Etc ......
*Company (E.g Sony)
*Branch (e.g London)

However I would like to have the two fields marked with (*) also, the idea being that when I pick the company ideally from a drop down list. Then the Branch selection would only give me the branches that are related to that company .

i am not sure what the correct way is to setup the tables to store the information for these fields. Plus it will also require some code I imagine...


The the next step is to have an orders table (tblorders) where I will log each order.
Fields>>>
OrderID (bespoke id)
Description
Order Date
**SalesAgentName
*Company
*Branch

Therefore the SalesAgentName will be selected from the (tblsalesagent) via a lookup ... I would like the Company and Branch fields in the (tblorders) to autocomplete based on the information stored against this SalesAgent in the (tblsalesagent) ...


Thanks

[pbaldy]

See if these help:

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

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

[craignovice]

Hi pbaldy, with regards to the auto fill, I think using a query is definitely the right way to go, so therefore to get the Company and Branch to appear in the query, what would my key field be in the orders table ?

[pbaldy]

The field that relates the tables, presumably SalesAgentName. I would use an ID field rather than the name; sooner or later you're going to have 2 people with the same name.

[craignovice]

Ok thanks, therefore i can use the SalesID rather than the name as you are suggesting... If i create a form for inputting information into the orders table, do i need to create a combo box for selecting the SalesAgent, i.e so i can see there name as the ID would not mean anything, what is the correct way to approach this ?

[pbaldy]

You can have the ID column as the bound column of the combo, but be hidden so users just see the name.

[craignovice]

Is there anyway to validate on control selecting one tick box based on another one.... For example each order will have a few status's

Quote
Ordered
Production
Dispatched

So if for example I wanted to ensure that when I changed the status to "Ordered" that the "Quote" tick was automatically removed ?

This information is in a Form Datasheet view with conditional formatting applied, so if it is a quote then the quote ref box will be green...

[pbaldy]

You could use the after update event of the status control to test its contents and set the checkbox value appropriately.