Simple Cascading Combo Box question

[depawl]

Hello:
I have setup 2 cascading combo boxes as per:
http://support.microsoft.com/kb/289670
This seems to work fine except that it doesn't do anything. What I need is to have the value selected in the second combo box be entered into a table. When I try to bind either of the combo boxes to a table or query the code breaks down and they don't work.
All I need to do is select a category from the first combobox, then select a product from the second combobox, and have that value entered into a table.
Thanks.

 

[RainLover]

There are too many possible problems, so it is difficult to comment.

Could you post a cut down version of your database and I will have a look.

 

[depawl]

Ok
this is a really pared down version but will provide the essence of what I'm trying to accomplish. I have a table (Table 1) with 2 fields (Referral Source and Name) and a Form (Form 1) with 2 combo boxes, Referral Source (combobox 1) and Name (combobox 2). When the user makes a selection from the first combobox, the value of that selection needs to be populated in the Referral Source field of Table 1, and combobox 2 needs to be filtered by the selection in combobox 1. For instance, if the user selects "Agency" in combobox1, the only values appearing in combobox2 should be "NRCIL, WUM, JRC, and NCTLS). When the user selects one of these values, that value should be populated in the "Name" field of Table1.
As I said my actual database is a lot more complex than this, and combobox2 would literally have hundreds of entries if not filtered by combox1.
Thanks so much for your assistance.

 

[jrmask]

See if this helps you understand >
http://msdn.microsoft.com/en-us/library/bb404901.aspx

It's for 2007 however the principle is the same. You can add a button on your form to update the table with your selection.

 

[depawl]

jrmask;
the link you gave basically provides the same information as the link in my original post. I have been able to set that up and get it working correctly, however, those are 2 unbound combo boxes. I run into problems when I try to bind the combo boxes to the fields in my table. In other words, the selection from combobox1 needs to populate field1 in my table, and the selection from combobox2 needs to populate field2. I have not yet been able to get this to work.
Thanks.

 

[RainLover]

I revised your DB just a little.

First of all you should not use "name' as the name of a field as it is a reserverd word and can cause problems. Try MyName instead.

I also created a couple of tables to store your choices instead of using a value list. The reason for doing this is that a value list is hard coding. In other words an end user cannot change the information, By creating a Table you can also create a form for the end user to add new values.

Hope this helps.

 

[depawl]

OK, thanks. I'll take a look at it.
Is there a way to do the same thing by using the value lists instead of tables? I really don't want the end users changing the data, and I have already created the two combo boxes using value lists, all I need to do is set up the two so that the first filters the second.

 

[RainLover]

If you don't want the end users to be able to edit the data then simply don't give them the ability to do so.

End users should never have access to tables, so don't create a form for them to edit.

 

[depawl]

RainLover:
OK I think with some modifications your code will work for what I want to do. The only remaining issue I have is to populate MyTable with the Referral Source and Referrral Name rather than ReferralSourceID and ReferralNameID. Any ideas?

 

[RainLover]

Yes

Use the ID not the name.

I try not to go overboard with Normalisation, but in this case it is required.

Doesn't my sample show you how to do this.

I am a little confused as to your problem.

 

[depawl]

I have several reports that pull data from the table that this form populates. Obviously reporting that, for example, in the month of March 2009 there were 3 referrals from ReferralSourceID 3 doesn't mean anything to agency staff. I have to somehow recorrelate the ID# to the referral source and source name. I guess I can do this indirectly with a query or something, I was just hoping to be able to do it directly.
Thanks for all your assistance.

 

[RainLover]

Have a look at my sample.

Especially look at the Properties of the combo boxes.

In particular "Column Count", "Column Widths" and "Control Source"

These Combo boxes use the ID but display "Text"

Hope this helps.

 

[depawl]

Yep, I think I got it working, at least on my pared down sample. Now on to the real thing.
Thanks again for all your assistance.

 

[RainLover]

Glad to hear you sorted it.

 

[depawl]

Thanks. Couldn't have done it without you. BTW how is the weather down there? It is autumn there, correct? We still have patches of snow on the ground here in Upstate New York State, USA, but spring is finally here.

 

[RainLover]

The weather is always great here in God's Garden. There is never a bad day. It is just that some days are better than others.

The nights are cool 20 degrees Celsius and the days around 30.

What is this thing "Snow'

 

[depawl]

I think I'd like to move down under. Here's what we went through this winter. I had to shovel my roof 4 times.
http://http://www.fourwinds10.com/siterun_data/spiritual/pictures/news.php?q=1204920443