combo box based on data entered

  • Thread starter Thread starter Fred
  • Start date Start date
F

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
 
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,

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
 
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
 
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
 
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!
 
Back
Top