Cascading Combo Box

[Toys]

Could someone please give me a simple explantion of how to setup a cascading
combo box for the following scenario: I have 3 tables, Customers, Branches,
and Faults. Customers has the field CustomerName; Branches has the fields
CustomerName (which it looks up from the Customer table) and BranchName;
Faults has the relevant field BranchName (which it looks up from the Branches
table). I have a form that has a combo box CustomerName and a subform (for
recording faults) with a combo box for BranchName. When I choose a customer,
I want only the relevant branches to appear.

PS. Please don't tell me to look up "Cascading Combo Box" as I already
have. This seems to be the refrain for simple questions and the more complex
questions have too complex answers. Thanks

 

[Joan Wild]

See if this explanation/example helps.

 

[Joan Wild]

See if this explanation/example helps.
http://www.pacificdb.com.au/MVP/Code/ComboRS.htm

 

[Sprinks]

Toys,

Firstly, if you mean by "lookup" that you have defined Lookup fields in
table design view, you're off to a bad start. See the following link for the
reasons why:

http://www.mvps.org/access/lookupfields.htm

To limit the branches to those associated with the selected customer in the
parent form, set the Branches combo box RowSource to (edit field, control,
and table names as required) in form design view:

SELECT Branches.BranchName FROM Branches
WHERE Branches.CustomerName = Parent.[CustomerNameComboBox]
ORDER By Branches.BranchName;

Then, in the AfterUpdate event procedure of the CustomerName combo box,
requery the subform combo box:

Me!YourSubform.Form.YourBranchesComboBox.Requery

By the way, referring to a helpful suggestion from what are volunteer
professionals as a "refrain" is not likely to promote much goodwill or
motivation to answer your question. Newsgroups

Sprinks

 

[Toys]

Sprinks,

Thanks for your help. My problem was that I was not refering to correct
field in my main form when entering criteria. I had mistakely tried to limit
my list by the lookup field in my branches table (which obviously couldn't
help to limit my list). I just had to refer to the combo box where I
selected a customer. Although I solved it myself, your explanation is right
on!

By the way, I didn't mean to decrease any good will. I just usually find
this site very helpful (much thanks to all you professionals who give you
time to answer others questions that are helpful to me as well). But in this
case, there were so many people saying, "just look up Cacasding Combo Box"
and when I did that there were lots of unanswered questions, and those that
were answered were not helpful. That's actually why I titled mine "Cascading
Combo Box" so that when the next person looks it up (as recommended)
hopefully they can find something that helps them!

Anyway, thanks again.

Toys

Toys,

Firstly, if you mean by "lookup" that you have defined Lookup fields in
table design view, you're off to a bad start. See the following link for the
reasons why:

http://www.mvps.org/access/lookupfields.htm

To limit the branches to those associated with the selected customer in the
parent form, set the Branches combo box RowSource to (edit field, control,
and table names as required) in form design view:

SELECT Branches.BranchName FROM Branches
WHERE Branches.CustomerName = Parent.[CustomerNameComboBox]
ORDER By Branches.BranchName;

Then, in the AfterUpdate event procedure of the CustomerName combo box,
requery the subform combo box:

Me!YourSubform.Form.YourBranchesComboBox.Requery

By the way, referring to a helpful suggestion from what are volunteer
professionals as a "refrain" is not likely to promote much goodwill or
motivation to answer your question. Newsgroups

Sprinks

Could someone please give me a simple explantion of how to setup a cascading
combo box for the following scenario: I have 3 tables, Customers, Branches,
and Faults. Customers has the field CustomerName; Branches has the fields
CustomerName (which it looks up from the Customer table) and BranchName;
Faults has the relevant field BranchName (which it looks up from the Branches
table). I have a form that has a combo box CustomerName and a subform (for
recording faults) with a combo box for BranchName. When I choose a customer,
I want only the relevant branches to appear.

PS. Please don't tell me to look up "Cascading Combo Box" as I already
have. This seems to be the refrain for simple questions and the more complex
questions have too complex answers. Thanks

 

[Christopher]

I have two tables:
"Assets" - holds information about each individual asset
"Asset Categories" - predefines a list of assets like printer, computer,
laptop, etc, as the asset category(text) and has a autonumber that is the
"AssetCategoryID"

The fields that have combo boxes are:
"Model" from the Assets table
"AssetCategoryID" from the Assets Category table

On the form "Employees Add Asset Subform" I want to choose an category from
the "AssetCategoryID" combo box and have the "Model" combo box show only
those records that are printers or computers or laptops. And I want only the
assets from the "Asset" table to choose from, I don't want to create any new
records.

 

[Will]

In the rowsource of the Model combo box, in the criteria field under
AssetCatergoryID, enter [Forms]![Your form Name]![Asset Category Combo box
name] which if you have put your actual names should be: [Forms]![Employees
Add Asset Subform]![AssetCategoryID]

 

[Christopher]

Holy crap it works!!! But it only works when I use it in the subform.

In the form "Employees" I link a persons "Username" field to each asset they
own from the table "Assets"(the asset table also holds the field "Model"). I
do this by using the "Employees Add Asset Subform" in the "Employees" form.

Plus when the model is selected from the model combobox I want it to pull
assets from the "Assets" table and not create new ones.

 

[kalexander]

I've been using the code given on the site Joan lists below to base a second
combo box in a form on the results of the first. However, I can only get it
to diplay the ID number for the topic field I wish to select - not the name
in full.

Here is the code I used to make a sub-topic field generate results based on
the selection in a subject field on a form.

Private Sub Subject_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of cboInvoice, based on the
'value selected in cboCustomer.
sSQL = "SELECT Topic, TopicID " _
& " FROM Topics WHERE SubjectID = " & Me.Subject _
& " ORDER BY Topic"

Me.Topic.RowSource = sSQL


Anyone notice where I'm off track? I would prefer to stick to using this
"after update" type of solution rather than creating new queries.

Thank you much!

 

[strive4peace]

my guess is that you want the TopicID to be the first column and Topic
to be the second

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Christopher]

Private Sub Form_Load()
Me.Form.RecordSource = "select * from assets where Username='joe'"
Me.Form.Requery
End Sub

Private Sub AssetCategoryID_AfterUpdate()
Me.Model.Requery
End Sub

Private Sub Model_AfterUpdate()
If Model.Text <> "" Then
myquery = "select * from assets WHERE Username Is Null AND
AssetCategoryID = " & AssetCategoryID.Value & " AND Model= '" & Model.Text &
"' ;"
Me.RecordSource = myquery
End If
End Sub

Private Sub Command91_Enter()
Me.Username = [Forms]![Employees].[Form]![Username]
Me.Form.Requery
Me.Form.RecordSource = "select * from assets where Username='joe'"
Me.Form.Requery
AssetCategoryID.SetFocus
AssetCategoryID.Text = ""
Model.SetFocus
Model.Text = ""
AssetCategoryID.SetFocus

[Forms]![Employees].[Form]![Employees Assets Subform].Requery
[Forms]![Employees].[Form]![Employees Cell Phone Subform].Requery
[Forms]![Employees].[Form]![Employees Desktop Subform].Requery
[Forms]![Employees].[Form]![Employees Laptop Subform].Requery
[Forms]![Employees].[Form]![Employees Monitor Subform].Requery
[Forms]![Employees].[Form]![Employees Other Asset Subform].Requery
[Forms]![Employees].[Form]![Employees Pager Subform].Requery
[Forms]![Employees].[Form]![Employees Phone Subform].Requery
[Forms]![Employees].[Form]![Employees Printer Subform].Requery
[Forms]![Employees].[Form]![Employees Wireless Subform].Requery
End Sub