Cascading Combo Boxes with Satellite Data

[aubaine]

Hello, I am currently trying to have my combo boxes both be cascading and enter data into the control field different from what the user selects. In my example, I have [Country] and [City], and I want these to be cascading filters. Additionally, when someone selected "China" from [Country], I need the foreign key related to "China" to be entered into the control source in the [University] table in the "CountryID" field. I have followed the guides here: http://bytes.com/topic/access/answe...tion-based-satellite-data-displayed-combo-box

and here: http://www.fontstuff.com/access/acctut10.htm

but I can't figure out how to do both at the same time. I'm not sure if this explains my problem throughly enough, so if more information is needed, please let me know what would be useful to provide.

Thanks!

 

[wiklendt]

what you want is certainly possible. if you followed the tutorials but it still doesn't work, then we'll need more of a description of your actual scenario - or better yet, your db posted here (with some advice on which form and combo is the troublesome one).

 

[aubaine]

I can't post the actual database, so I'll create a mock version with test data and post it.

I guess the crux of the problem is that I can get either cascading or satellite entry, but I can't get both to work simultaneously. I think the reason behind this is the SQL statement that I'm using for the Country rowsource:

SELECT DISTINCT tblAll.Country FROM tblAll ORDER BY tblAll.Country;

Because this makes the rowsource only the "Country" field in the tblAll table, there is no second column in the rowsource with the country primary key. So I guess I probably need an SQL statement that also pulls the associated distinct primary key for each country. If this doesn't help enough, I'll work on getting a mock version up here ASAP.

 

[aubaine]

Here is the mock database. As you can see, by using the method in the first tutorial, combined with the SQL statement used to create the cascading effect, nothing shows up in the Country combo box. This can be solved by changing it to 1 column, but this means that the countryID won't be entered into the University table.

 

[dkinley]

I normalized a couple of things and cascaded them to what I think you are going for. Check the relationships, combo box data sources and the code behind the City combo box to see what I did.

HTH,
-dK

 

[dkinley]

I didn't put the country code in the University table because I did not see a need. You can reference the country code utilizing the relations through the city table. However, if this just has to happen then add the field back to the table and in the AfterUpdate event of the country combo box, you can use Me.txtCountryIDControlName = Me.cmbCountry.

-dK

 

[aubaine]

Thanks! However, it says "Control can't be edited; it's bound to unknown field 'CountryID'". Therefore I can't select any country.

Also, I noticed that you took out the hidden autonumber fields from the form. I noticed that if I didn't do this, that the autonumbers wouldn't populate. Is there another way to do this?

 

[dkinley]

Opps .. I didn't finish the job - forgot to set the forms source correctly.


-dK

 

[aubaine]

Ok, I think I'm starting to get it. However, in your db, I can't select the City, and it doesn't give an error message. Any ideas?

edit: Actually, it works once the form is first opened, but after I change the country, the form breaks and you can't select a city anymore. Does this have something to do with the Requery?

 

[dkinley]

Yah .. it is doing something weird - it has been awhile since I actually have set one of these up in this particular fashion.

I know the quick fix (bound the table to the form and add a field), but I am sure I am overlooking something to base the form on the initial query you had specified. I assumed there was a reason so working with that.

-dK

 

[aubaine]

Ok, the problem I'm having now for some reason, is when I apply this to my main database I get the error when selecting a country that "Control can't be edited; it's bound to an Autonumber field". However, in the test database, it is also bound to an autonumber field, but there is no problem. Any idea what I'm doing wrong here?

 

[dkinley]

Here ya go. This is the quick fix - the form is bound to the table, but that is not how you have it specified in your initial cut. I am not entirely satisfied with this because we are duplicating a field. In the databases I have created - I have avoided this issue because of the way I set up the data flow a little differently.

However, I see the issue and know another fix but I am not satisfied with it - I am still working on this.

-dK

 

[dkinley]

Here is correct attachment.

-dK

 

[dkinley]

Will the user's be adding cities to the country's off of this form? Or will they merely be selecting the country/city?

-dK

 

[aubaine]

Well, it would be nice to be able to add cities, but I figured I would try to tackle that after I got these first two things going.

Have you thought of any ideas as to why I am having the autonumber control source problem in ym main database? Why isn't this a problem in the test database?

 

[dkinley]

Yes. What you have is a combo box that is set to the primary key (instead of a foreign key) of a table so this will throw an error because it is an autonumber on a new record.

The last attachment I posted works because it is all set up nice and neat on a table-bound form where you are only manipulating the foreign keys.

For the query driven form, my test db is asking to save the record before you can change the city. As I mentioned before - I haven't seen something like this in a while because of the way I do the data flow in the databases I have created, so I am working through this issue on yours.

I asked about adding Cities at this point just in case it would change the dynamics of the final solution.

-dK

 

[aubaine]

Awesome! Thanks to you, I was able to get it working!!! I just based it on the table with foreign keys instead of on the query, changed the associations, and it all worked!

So, now that that's working...did you have an idea about how to add a city using the same form is it's not already in the list?

Thank you so much for you help!

 

[dkinley]

Other than turning on an 'add to list' in the combo box properties, you have to ensure that the country code is updated along with the added city.

If I don't have a seperate interface or doing control level access rights, I usually refer to this link for adding to a list. I like their implmentation and they have already did all of the heavy lifting.

-dK

 

[aubaine]

That looks like it's going to take some time to add into my db, but I'll try it later. Thanks again for all of your help!

 

[dkinley]

You bet. Good luck.

-dK