Beginners Guide To Cascading Tables Reqd

[richlewt]

Hi
Apologies for starting another thread on the same subject but felt that anyone searching in the future would find this easier and maybe would help them as well.
Its a question that has been asked before, I have tried to understand the answers but have struggled as some answers are very brief like "use vba" which does not mean much at all to a beginner. Also, a lot of answers have been to specific databases with some very complex structures.
I would like to suggest an example using just 3 tables:
i) tblMain
ii) tblAnimal
iii) tblBreed

All selections made from the form need to be written back to tblMain.

I know this is simple but hopefully this will be of use in the future to us beginners.

tblMain fields:
IDmain
animal
breed

tblAnimal fields:
IDanimal
animalType(dog, cat)

tblBreed fields:
IDBreed
breedType(corgi, boxer, tabby, persian)

To produce the form I usually use the form wizard. What I now need is a simple form with the animal to be selected first, so lets say its dog, and then under breed a pull-down to appear with boxer and corgi in it.
This simple example would greatly help us mere beginners if someone could just show how its done.
I do apologise for starting this second thread but the question does appear time and time again but what is needed is a very simple example that people can easily follow.
Thanks.

 

[Mile-O]

Using the example i directed you to on this thread then it is really quite simple.

Although the comboboxes have the Continent and Country in them, you aren't actually storing anything regarding those tables.

You use a foreign key in your table (tblMaster) such as ContinentID and CountryID - in fact, you only need to store CountryID as you can easily get the continent from a DLookup() domain aggregate function.

Just bind the combobox(es) to the underlyng recordsource of your form.

 

[richlewt]

Hi Mile
Thank you for taking the time to respond. i will have to go away and think about this. Sorry, but I am only just starting at this and need to understand "get the continent from a DLookup() domain aggregate function"

 

[Pat Hartman]

Using your specific example:
The Breed table needs an additional column:
tblBreed fields:
IDBreed (autonumber primary key)
IDAnimal (foreign key to tblAnimal)
breedType(corgi, boxer, tabby, persian)

1. The control Name of the first combo should be:
cboAnimal
2. The RowSource for the first combo should be:
Select IDAnimal, AnimalType
From tblAnimal
Order By AnimalType;
3. The Animal combo should have a column count of 2, the bound column should be 1, the column widths should be 0,.5", the controlSource should be empty (this combo will not be bound), the RowSource will be the name of the above query.
4. In the AfterUpdate event of the Animal combo, you must requery the second combo - Me.cboBreed.Requery
5. The control Name of the second combo should be:
cboBreed
6. The RowSource for the breed combo should be:
Select IDBreed, BreedType
From tblBreed
Where IDAnimal = Forms!YourFormName!cboAnimal
Order By BreedType;
7. The Breed combo should have a column count of 2, the bound column should be 1, the column widths should be 0, .5", the ControlSource should be IDBreed (this combo is bound), the RowSource will be the name of the above query.

 

[richlewt]

Hi Pat
May i say a big thanks for taking the time and trouble to reply to my plea for help on the cascading combo saga. That was a great answer and the kind of thing i was looking for. I thought that to save the same question being asked time and time again a simple example with a solution that eveyone could follow would be of benefit to everyone, it stops the same repetitive question, and helps out us beginners.
I have carried out all the things you suggested and think I have done it correctly (well, obvious not or it would be working i guess and I would not be writing to you now for more help !!)
I am getting an error message "MS Access can not find the macro ".ME"
The macro or its group does not exist, or the macro..................."

I would be so grateful if you get 5 mins to just have a quick look at my sample http://www.lewtcentral.pwp.blueyonder.co.uk/index2.htm and if I could get it going could then post the actual database back to the board so others can download it along with your set of instructions and hope learn from it.
Again, thanks for taking the time .

 

[richlewt]

Hi
Just to keep this thread flowing, and let all us beginners informed here was a pm I got back from Pat on the above post.

"You put the code in the wrong place. When someone gives you VBA code, it needs to go into a sub or function in a code module even if it is only a single line as in this case. At the right edge of the event property box is a builder button. If pressing it does not automatically open a code window, choose the option to do so. The builder will create the first and last lines of the event procedure sub, put the line of code between them. "

OK, my fault, i didn't realise but have learnt a bit more now. Only problem is, still dont work. I have (I think) correctly added VBA code but the Breed combo is not updating. Have zipped file down to 31kb and uploaded with this message.

 

[Mile-O]

Hi
Just to keep this thread flowing, and let all us beginners informed here was a pm I got back from Pat on the above post.

"You put the code in the wrong place. When someone gives you VBA code, it needs to go into a sub or function in a code module even if it is only a single line as in this case. At the right edge of the event property box is a builder button. If pressing it does not automatically open a code window, choose the option to do so. The builder will create the first and last lines of the event procedure sub, put the line of code between them. "

OK, my fault, i didn't realise but have learnt a bit more now. Only problem is, still dont work. I have (I think) correctly added VBA code but the Breed combo is not updating. Have zipped file down to 31kb and uploaded with this message.

The place you have put Me.cboBreed.Requery is wrong. Re-read Pat's comments about the Code Builder. Also, you need to look at your datatypes more closely. You are looking to assign numeric values to text fields.

 

[Mile-O]

If you are still having problems maybe my post on this thread can help.

 

[richlewt]

The place you have put Me.cboBreed.Requery is wrong. Re-read Pat's comments about the Code Builder. Also, you need to look at your datatypes more closely.

Ooh! wish you would tell me where? This is sooooooooooooo frustrating. I understand now about data type and have the combo box at least selecting the correct breeds from what you select in animals (lets face it, this is an achievement for me) BUT
then get the error message "control cant be edited: its bound to unknown field IDBreed. I thought from Pats instructions 7. that is should be ?
I also want to know how this little combo will write the selected data back to tblMain? Please put me out of my misery
Here is the latest version of animal.zip

 

[Pat Hartman]

I took the liberty of changing everything. Hope you don't mind.

 

[richlewt]

I took the liberty of changing everything. Hope you don't mind.

Hope I dont mind, you have just eased a major headache of mine !!!! I owe you one Pat.
I see what you are doing now but I have been looking things totally differently. The issue hinges around what you are storing in tblMain. I have been trying to store the actual text in there i.e. "Dog" "Boxer" not the numeric value. I need the text values in there as I want to upload these to a database on the web. So is this easy to achieve?

 

[RayH]

Is it not possible for you to have a query to extract the text data you want?
Like this:

SELECT [tblMain].[MainID], [tblBreed].[breedType], [tblAnimal].[animalType]
FROM tblAnimal INNER JOIN (tblBreed INNER JOIN tblMain ON [tblBreed].[BreedID]=[tblMain].[BreedID]) ON ([tblMain].[AnimalID]=[tblAnimal].[AnimalID]) AND ([tblAnimal].[AnimalID]=[tblBreed].[AnimalID]);

 

[Pat Hartman]

There was one thing that I forgot to do. You need unique indexes on the BreedType in the Breed table and on the AnimalType in the Animal table. This will prevent people from accidentally duplicating the text descriptions.