combo box based on data entered

[Fred]

How do you create a combo box which limits the choices based on a previously
entered value.

ie. One combo box chooses the salesman. Dependent combo box limits choices
to territories covered by that salesman.

TIA

 

[Wayne Phillips]

Hi Fred,

Ok I'm assuming you've already set up 3 tables for this, e.g.
TblSalesPeople, TblTerritories and TblSalesPeoplesTerritories...

for the purpose of this, i will assume the following fields are set up for
these tables;

TblSalesPeople
----------------
lngID Type: Number (AutoNumber if you wish) PrimaryKey
SalesPersonName Type: Text

TblTerritories
---------------
lngID Type: Number (AutoNumber if you wish) PrimaryKey
TerritoryName Type: Text

TblSalesPeoplesTerritories
----------------------------
lngSalesPersonID Type: Number Composite Primary Key
lngTerritoryID Type: Number Composite Primary Key

Create a new query called QrySelectedSalesPersonsTerritories with the
following SQL

SELECT TblTerritories.TerritoryName
FROM (TblSalesPeople INNER JOIN TblSalesPeoplesTerritories ON
TblSalesPeople.lngID=TblSalesPeoplesTerritories.lngSalesPersonID INNER JOIN
TblTerritories ON
TblSalesPeoplesTerritories.lngTerritoryID=TblTerritories.lngID
WHERE (((TblSalesPeople.lngID)=Forms!FormNameHere!Combo1NameHere))

Note: you will need to replace 'FormNameHere' and 'Combo1NameHere' from the
above with whatever names you've called them in your database.

For the two combo boxes on the form;
----------------------------------------

Combo 1: RowSource = TblSalesPeople

Combo 2: RowSource = QrySelectedSalesPersonsTerritories

Now after the user selects an item from Combo1, you will need to refresh
Combo2, there are a few ways of doing this, I would probably use the
'OnChange' event for the combo1 and enter the following VBA statement;

Me.Combo2NameHere.Requery

Hope this helps,

Wayne Phillips
http://www.everythingaccess.com

 

[Wayne Phillips]

Slight amendment...

For Combo box 1, set the Column Count property to 2 and Column Widths to 0cm

Wayne Phillips
http://www.everythingaccess.com

 

[Fred]

Wayne,

I'm fairly new to Access and I have worked with queries using the query
design where I choose tables and fields.
I'm kinda lost with the SQL you asked me to create. Where do I enter this
information?
Can you please direct me to where I can find this out.

Much appreciated,
Fred

 

[Wayne Phillips]

Fred,

Sorry, I should've explained better. To enter the direct SQL into a query,
do the following;

1. Create new query in design mode
2. At the 'Show Table' prompt select 'Close'
3. Enter SQL mode by right-clicking on the Window title bar (where it says
'Query1: Select Query') and choose 'SQL View' from the drop down menu.
4. Enter the SQL statement

Then just close as normal and enter the query name.

Regards,

Wayne Phillips
http://www.everythingaccess.com

 

[Fred]

Wayne,

I actually created a database like the example you setup for a test and the
program won't accept the SQL statement as you have it. I noticed that one
")" was missing to close the

FROM (......

but I'm not sure if that's it.
The error I get is "syntax error in Join operation"

Note that I created relationships between the tables. I also created a
frmMain to put the test combo boxes in, to which I created a relationship to
the salespersons ID.

Thanks again,
Fred

 

[Wayne Phillips]

Try...

SELECT TblTerritories.TerritoryName FROM (TblSalesPeople INNER JOIN
TblSalesPeoplesTerritories ON
TblSalesPeople.lngID=TblSalesPeoplesTerritories.lngSalesPersonID) INNER JOIN
TblTerritories ON
TblSalesPeoplesTerritories.lngTerritoryID=TblTerritories.lngID WHERE
(((TblSalesPeople.lngID)=Forms!FormNameHere!Combo1NameHere));

Sorry!