Picklist based on contents of another picklist

[georgezx9]

This is similar to the nwind database products and categories, but cant get
that to work either.

three tables
main
-Countryid
-Countyid

country
-country_id
-countryname

county
-county_id
-Countyname
-countryid

When I select a country it works well (picklist based on the country table),
in the form the data entry field is called countryctl bound to countryid
using:
SELECT DISTINCTROW Bikemeets_country.Countryname, Bikemeets_country.
Country_ID FROM Bikemeets_country ORDER BY Bikemeets_country.Countryname;

I now want to select the county, but only have a subset based on the country
already selected.
I have put the following in the country picklist after update event, as
suggested by this forum

I.e Select England and only the english counties show.

Me.Countyctl.RowSource = "SELECT countyname FROM" & _
" county WHERE county.CountryID = " & Me.countryctl & _
" ORDER BY countyname"
Me.Countyctl = Me.Countyctl.ItemData(0)

The county picklist (countyctl) is not bound, but I have set bound column to
2 as pr nwind, but I dont know why!!, also I dont know what itemdata(0) does
in this case

The countyctl picklist comes up empty

What I want it do do is show the county selected and store it in the county
field

Help

 

[IT Pro Dave]

I had better luck by referencing the control the long way.
Forms![Form1]![Countryctl] where Form1 is the name of the form you are using.

You mentioned that Countyctl was bound to column 2, but Countryctl must also
be bound to the column that Country_ID is in.

County and Country being so close I get confused.

 

[georgezx9]

Thanks dave, I must admit that county and country do confuse me, I think I
will change them to be safe.

I dont understand your comment on "bound".

The problem is I dont understand why the control is bound to column 2, it is
only because Nwind sample does this. In my case I am only selecting one field
"countyname".

Taking your point Countryctl is bound to country id (the related entry to the
country table), this part works fine.

What does the itemdata(0) do, I thought itemdata was set up in a loop (an
array of something).

Thanks

GY

I had better luck by referencing the control the long way.
Forms![Form1]![Countryctl] where Form1 is the name of the form you are using.

You mentioned that Countyctl was bound to column 2, but Countryctl must also
be bound to the column that Country_ID is in.

County and Country being so close I get confused.

This is similar to the nwind database products and categories, but cant get
that to work either.

[quoted text clipped - 41 lines]

Help

 

[IT Pro Dave]

I'm sorry if I don't get this explanation correct, but I will try to explain
it as best I can. The "Bound Column" property in a combo box lets the program
know which column you want the data to come from when a specific row is
selected in a combo box. Now why would you want more then one column? Take
for instance countryctl. You want the user to be able to see the Country Name
(England) but you want to pass the CountryID(01) along because your table
County recognizes CountryID. To do this you set your "rowsource" to contain
both columns. Set your "Column Count" property to equal 2. Your "Bound
Column" property to the column number you want to pass along. In this case
Bikemeets_country.countryid which happens to be column number 2.
Now so your users don't see the second column and call up and ask you why
England is 01 and France is 02 or vica versa, hide the second column from
view using the "column widths" property.

I must confess I am not sure what is being accomplished with Me.Countyctl =
Me.Countyctl.ItemData(0). It looks like it is just setting the Countyctl to
the first line of your rowsource.

Thanks dave, I must admit that county and country do confuse me, I think I
will change them to be safe.

I dont understand your comment on "bound".

The problem is I dont understand why the control is bound to column 2, it is
only because Nwind sample does this. In my case I am only selecting one field
"countyname".

Taking your point Countryctl is bound to country id (the related entry to the
country table), this part works fine.

What does the itemdata(0) do, I thought itemdata was set up in a loop (an
array of something).

Thanks

GY

I had better luck by referencing the control the long way.
Forms![Form1]![Countryctl] where Form1 is the name of the form you are using.

You mentioned that Countyctl was bound to column 2, but Countryctl must also
be bound to the column that Country_ID is in.

County and Country being so close I get confused.

This is similar to the nwind database products and categories, but cant get
that to work either.

[quoted text clipped - 41 lines]

Help