Table lookup function

[Nitesh9999]

Hi,

I have a table with the following 3 fields (these are the ones i'm having the issue with but there are others).

ID, Country, City

The ID is an auto number.

The Country Field is a lookup with the following lookup properties

Display Control: Combo Box

Row Source Type: Value List

Row Source: "England";"Spain";"France" etc


Now the problem...

I also want a lookup in the City field which changes to reflect what was selected in the Country field.

E.G

If in the Country Field Spain is selected then in the City Field I should have the option of selecting Barcelona, Madrid, Valencia etc.

Is this possible in the lookup of a table???


Thanks.

 

[maxmangion]

this should not be done at table level, but you should apply this cascading feature in your forms.

Take a look at the Access FAQ section of this forum, there is a good example/sample how to work with Cascading Combo Boxes.

 

[Nitesh9999]

Thats what i think i meant. I do have a form to input the data into my table. I'll have a look at your recommended example then get back to you.

Thanks.

 

[maxmangion]

you're welcome. Here is a link to the thread i was refering.

 

[Nitesh9999]

Ok i had a look and these are the new questions regarding the modification of SJ McAbney's example.

In his example he uses a table as the data source for both of his combo boxes. I would like a value list for the first/parent combo box (as stated above) and then use the code "SELECT [tblBooks].[BookID], [tblBooks].[BookTitle] FROM tblBooks WHERE ((([tblBooks].[AuthorID])=[Forms]![frmExample]![cboAuthors])) ORDER BY [tblBooks].[BookTitle];" or equivelent for my second combobox.

So for my example...

I dont think i need to change the record source for the "parent" combo box just the "child". I would think i still need to the alter the after update property of the "parent" box though, but im not sure.

I think my "child" (city) combobox will need the following alterations:

My table is called tblworld
My form is called myform
Parent Combobox is called cbocountry
Child Combobox is called cbocity

I also got rid of the "order by" as it is not needed, this shouldn't create issues should it (I tried deleting the extra bracket)?


SELECT [tblworld].[BookID], [tblworld].[city] FROM tblBooks WHERE (([tblBooks].[AuthorID])=[Forms]![myform]![cbocountry]))

Any help altering the rest of the above code. What it needs to do is find the value in the parent combobox and display the relevant value options (i dont know where these "relevent" values will be stored either in the code or a seperate table, hopefully in the code as i dont really need an extra table).

Hope this isnt too confusing.


Edit: Attached something i have found which is similar to what i need. It has a third combobox which i wouldnt need. And also i would like to use a value list rather than a table as a data source form my parent combobox.