Dependent Combo Boxes

[GBalcom]

What I want to do is have dependent combo boxes in a form that enters information back into a table. For instance, I have a Log form that enters records into the log table. I have two combo boxes, Topic and Sub-Topic. I would like the Sub-Topic combo box to be dependent upon the choice entered in the Topic Combo Box.

The trouble I'm seeming to have is I believe these have to be bound fields (to input back to the table). The examples I'm seeing are using unbound fields.

Is there something I'm missing?

ps. I'd like to do this without any VBA....I want to learn what is capable of a Web Database.

Thanks in advance for any help.

 

[Mr. B]

Having your combo boxes bound our unbound is not relative to making the second combo box display values based on the selection made in the first combo box.

You specify the field to which you combo box is bound using the "Control Source" property of the combo box.

Where you are wanting to do is something that is done all the time, even with bound forms. This is referred to as cascading combo boxes. Here is a link that should provide some help:
http://www.utteraccess.com/forum/Cascading-Combo-Boxes-M-t1959043.html

Once you have the sql created for the "Row Source" of your two combo boxes, the only UI macro you will need to make the second combo box display records based on the selection from the first should be created in the After Update event of the first combo box. All you need in this macro is the "Requery" statement and then provide the name of your second combo box.

Hope this helps.

 

[Beetle]

First, Mr. B is right in that it doesn't make a difference whether the combo boxes are bound or unbound as far as making them dependent. However, there are definitely differences in how you handle this in a web application. Most of the information you will find regarding setting up dependent ("cascading") combo boxes will suggest to use a form reference like -

Forms!SomeForm!YourFirstCombo

- in the criteria of the SQL for the second combo box. This is misleading when it comes to the new web applications because it will work, even in a web form, as long as you use the form in the Access client. However, if the goal is to run the application through Sharepoint and be able to run (at least some of) your forms in a web browser (which, presumably, would normally be the goal for a web app), that is the point at which the form reference method will fail. A web browser will not be able to translate a form reference.

The correct way to do dependent combo boxes in a web form is to use TempVars. In the After Update event of the first combo box you use an embedded macro like the following (this assumes your first combo box is named cboTopic and your second is named cboSubTopic);

[B]SetTempVar[/B]
    [B]Name[/B] TopicID
    [B]Expression[/B] =[cboTopic]

[B]Requery[/B]
    [B]Control Name[/B] [cboSubTopic]

You would want to repeat this macro in the Current event of the form.

Then, in the SQL for the second combo box put -

[TempVars]![TopicID]

- in the criteria row for the TopicID column.

 

[GBalcom]

Thanks guys for trying to help me.....but I'm still having some trouble. Attached is the database. Please take a look and see what I'm doing wrong if you can.

It is the Topic and Subtopic combo boxes on the form "Log Sheet"

Thanks!

 

[Mr. B]

What you have will work if you add the "Key" field to the "SubTopic Qry" (which is the record source for the "cboSubTopic" combo box).

You currently have the "cboSubTopic" combo box defined as Column Count = 2, Column Widths = 0"; 2" which would be fine when you actually have 2 columns in the query. Because you only had one column of data being returned by the query, it was not showing any results in the combo box.

 

[GBalcom]

Thanks guys for your help....I was able to follow through on a blank database and get this to work (still have to apply to my real db). I'm hoping sometime soon I'll be able to post a "flow diagram" of this to help other newbies like myself. (I'll be referencing it in the future I'm sure)...

I want to try and do it both ways so I understand both in the future.

Thanks again.