One combo box to control another 2

[Mayhem9]

Essentially I would like to use my current tool type combo box to control the options available in a second combo box, which will then contain sub types (I do not have this yet).

Currently, I have a tool log table, which is populated by my main form. There are several combo boxes, that reference other tables (each of which contain a single entry per recort). One of these is the tool type table (Type). This table simply contains the various tool types. The Tool_Log table and Type table structures are below:

Tool_Log
ToolID dbLong PrimaryKey Indexed
ManID dbText
Serial dbText
TypeID dbText
Size dbText
Description dbText
Set dbText
Year dbText
RRP dbCurrency
PurID dbText
Date dbDate
LocID dbText
ImagePath dbText
ImagePath2 dbText
TechDoc dbText
TechDocPath dbMemo
TechDoc2 dbText
TechDocPath2 dbMemo
TechDoc3 dbText
TechDocPath3 dbMemo

Type
Type dbText PrimaryKey Indexed

So currently if I select "wrench", it includes adjustable wrenches, pipe wrenches, box wrenches, combination wrenches etc. What I wold like to do is select "Wrench" from the Type combo box (cmbType) and then based on this selection have only the wrench sub types appear in the Sub Type combo box (cmbSubType [not yet present]).

I played with the code posted in the post but I was unsure as to whether the Sub Type would be in its own table or in the Type table.

Please let me know if I have not made this clear or I need to provide additional information.

Thanks,
Darren

 

[MajP]

If this is a continous form, cascading combos can be very problematic. Although in a continous form it appears as if there is a combo for every record, in fact there is only one control. So when you change the rowsource of the combo it changes every "instance" that you see. So if you go to record 2 and change the rowsource, and the new rowsource does not contain the value selected in record 1 there is no way to display record 1's data. So it will appear as if record 1 no longer has a value. Not sure if that is your problem. If that is the problem, a trick you can use it to put a textbox laying on top of the combo box covering everything but the pull down arrow. Both the textbox and the combo are bound to the same field.

 

[MajP]

after rereading the thread it appears that it is not a continous form. So please show all code controlling the combo.
The code for the requery needs to be in the OnEnter event of the second combo, not the after update of the first. Think about it when you are going back to edit, not add a new record. In that case you would skip the first combo and thus the requery would never take place if you only had it in the after update. But if it is in the second combos on enter it will always happen when you are working with the second combo.

 

[Mayhem9]

Thanks MajP,

I don't think it is a continuous form - it is a garden variety column form with only one record being displayed at one time. If you can recall my db from when you helped me with, it is the main form in that db.

That aside - I was just thinking about the FAYT feature and realized that I hadn't tried editing out the code for the Dependant combo box (the problematic one). But now that this list is reduced by the preceding one, I don't need that feature.

Thanks to both of you - Duane for the code and MajP for jogging my mind. Pink stars all round!

 

[Mayhem9]

Hi MajP,

The code for the requery needs to be in the OnEnter event of the second combo, not the after update of the first.

I didn't see this post when I posted my last one. I have the requery for the second combo box in the on current event for the form, which appears to be working at the moment for both new records and edits.

As I am now focusing on the sub form, I will file your suggestion and see how I go when I actually really start hitting the data. I need to focus on one thing at a time otherwise I know I will stuff something!

Hi Duane

Access is a relational database system which suggests master and child tables. The typical interface for this is main forms with sub forms. Access makes this quite easy with the Link Master/Child properties.

You were right - I was surprised at how simple it was to place the sub form into the main form and have it work first time. Now I just need to set the controls and format it etc but this will actually have to wait until the weekend.

Out of curiosity, before I set the properties of the sub form to continuous the default setting actually made the sub form look like a table, even though in design view it looked like a form. Is this normal or have I screwed something up?

 

[dhookom]

If the subform looks like a table view, this is the datasheet view of the subform. Some times I use this for very simple subforms.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Thanks Duane,

That is good to know. I can see the advantages of having a sub form look like a table.