It's a Combo Box Question Again!

[WarriorMCB]

I have been looking around this site to find the answer I seek, but to no avail.
This is my dilema. I have three (3) combo boxes, [Product],[Paper Grade], and [Print Color]. Each choice displays another value in a corrisponding list (i.e. Product Name displays: Product and Layout Fee, for the particular product). This I have been able to do, however I also need cbo 2 to query from cbo 1, and cbo 3 to query from cbo 2, while still displaying the (column 2) in another text box.

To simplify: each combo box needs to do two (2) things. one: display column another field in another textbox AND query the next cbo.

Can this be done, if so How?

Many thanks

 

[Mile-O]

The phrase you want is Cascading Combobox - there are numerous examples and you'll find them now that you have the best search term.

 

[Len Boorman]

Try searching Cascading Combo Boxes

This should give you at least part answer

L

 

[WarriorMCB]

I have searched all and still nada. I had searched casading cbo b4, but confused on how to implement them into the VB, it keeps coming up with errors.
This is what I have right now:Option Compare Database

Private Sub PaperID_AfterUpdate()
Me![$PerSheet] = Me![PaperID].Column(2)
End Sub

Private Sub PrintColorID_AfterUpdate()
Me![$PerCopy] = Me![PrintColorID].Column(2)
End Sub

Private Sub ProductID_AfterUpdate()
Me![LayoutFee] = Me![ProductID].Column(2)

End Sub

Like I said, I also need for these to query (casade) but how!

 

[Mile-O]

Have you seen this?

In total, for the proposed cascading combox, you should only need two lines of VBA code.

 

[WarriorMCB]

Yes. But when I added the example, errors came up!

 

[Len Boorman]

Cascading Combos

First Combo box (cbo_1) has a Row source of say

Select Table_1.Field_1, Table_1.Field_2
From Table_1

Okay

Second Combo Box (cbo_2) has a Row source of

Select Table_2.Field_1, Table_2.Field_2
From Table_2

and then you add the magical

WHERE Table_2.Field_1=[cbo_1)

Not sure of exact syntax

What you must do in with the On Click Property of cbo_1 have an event procedure saying

cbo_2.requery

Thats the basis behind a cascading combo.

I normally switch the visible property to True with the on click event of cbo_1 as well

HTH

Len

 

[Mile-O]

So:

• What tables do you have?
• What's their structure?
• What's your form called?
• What are your comboboxes called?

 

[WarriorMCB]

I must be doing something wrong. I've been trying to work on this prob. for weeks, I typed in the codes etc. but it's asking to parimeters aaargh!

Please help. Is there A VB for dummies!!!

 

[WarriorMCB]

Tables: Product;Print Color; Paper Grade; Paper Color; Order

Forms: As Above

All the above relate one way or another

In table view, all subsheets work fine.

The answer is probably simple. But I'm truely stuck. I'm not an access avid. I know the basics, but this has stumped me. I could send to file but it's 1.3MB's.

 

[Len Boorman]

Go back to basics

Use the wizard to create the first combo

Use the wizard to create the second combo

Go into properties of second combo, go to source, click on thingy at end of line to display the actual query and use the builder to add the criteria

L

 

[WarriorMCB]

Combo Boxes: ProductID, Print ColorID; Paper Grade ID

 

[WarriorMCB]

Ok I'll try that. By the way the ROWSOURCE says:SELECT Product.ProductID, Product.ProductName, Product.LayoutFee FROM Product;

 

[Mile-O]

Firstly, it would help to give your objects a proper naming convention. If you have queries the same name as your tables, for example, Access won't know which one to look at and pick the default (which I can't recall Tables or Queries)

So, tables prefixed with tbl, forms with frm, etc.

Okay, you've told me the tables, but what's their design? And what's the cascade order?

 

[Mile-O]

I have a funny feeling that these aren't cascading comboboxes but three specific comboboxes with different attributes of a product.

 

[WarriorMCB]

OrderID OrderID
CustomerID CustomerID
ProductName ProductID (Cbo)
LayoutFee LayoutFee (priced determined by ProdID choice)
PaperGrade PaperID (Cbo)
$PerSheet $PerSheet (priced determined by PaperID choice)
PaperColor Paper Color
PrintColor PrintColorID (Cbo)
$PerCopy $PerCopy (priced determined by PrintColorID choice)

 

[Mile-O]

So there's no cascade at all.

In each combobox, put the relevant SQL

ie..

SELECT ExampleID, Example, ExampleFee
FROM tblExample
ORDER BY Example;

Set each combo's ColumnCount property to 3
Set each combo's ColumnWidths property to 0;2;0

In the textbox you want to display the fee,

set its ControlSource to:

=[MyCombo]

Replace, of course, all instances of Example and Combo with the relevant names.

 

[WarriorMCB]

Thanks, I will give this a shot.

 

[WarriorMCB]

that suggestion didn't work

Each selection already displays column 2 the following textbox.

I need the combo box to do two things. One: display selection in the cbo & display the fees in the following texbox and TWO: query the 2nd combo box

 

[WarriorMCB]

Can access do this or shall i seek another db program?