Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rating: Thread Rating: 21 votes, 5.00 average. Display Modes
Old 11-01-2003, 07:29 AM   #1
richlewt
Junior Member
 
Join Date: Apr 2003
Location: Bristol, UK
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
richlewt
Beginners Guide To Cascading Tables Reqd

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.

__________________
cheers
Rich
richlewt is offline   Reply With Quote
Old 11-01-2003, 07:40 AM   #2
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 118 Times in 108 Posts
Mile-O will become famous soon enough
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.
Mile-O is offline   Reply With Quote
Old 11-01-2003, 11:24 AM   #3
richlewt
Junior Member
 
Join Date: Apr 2003
Location: Bristol, UK
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
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"

__________________
cheers
Rich
richlewt is offline   Reply With Quote
Old 11-01-2003, 04:41 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,982
Thanks: 13
Thanked 1,538 Times in 1,463 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-02-2003, 09:18 PM   #5
richlewt
Junior Member
 
Join Date: Apr 2003
Location: Bristol, UK
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
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 .
__________________
cheers
Rich
richlewt is offline   Reply With Quote
Old 11-02-2003, 11:03 PM   #6
richlewt
Junior Member
 
Join Date: Apr 2003
Location: Bristol, UK
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
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.
Attached Files
File Type: zip amimal.zip (30.3 KB, 557 views)
__________________
cheers
Rich
richlewt is offline   Reply With Quote
Old 11-03-2003, 01:16 AM   #7
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 118 Times in 108 Posts
Mile-O will become famous soon enough
Quote:
Originally posted by 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.
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 is offline   Reply With Quote
Old 11-03-2003, 06:55 AM   #8
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 118 Times in 108 Posts
Mile-O will become famous soon enough
If you are still having problems maybe my post on this thread can help.
Mile-O is offline   Reply With Quote
Old 11-03-2003, 11:51 AM   #9
richlewt
Junior Member
 
Join Date: Apr 2003
Location: Bristol, UK
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
richlewt
Quote:
Originally posted by Mile-O-Phile


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
Attached Files
File Type: zip amimal.zip (53.5 KB, 335 views)
__________________
cheers
Rich
richlewt is offline   Reply With Quote
Old 11-03-2003, 01:10 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,982
Thanks: 13
Thanked 1,538 Times in 1,463 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
I took the liberty of changing everything. Hope you don't mind.
Attached Files
File Type: zip amimalnew.zip (13.7 KB, 783 views)
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-03-2003, 01:25 PM   #11
richlewt
Junior Member
 
Join Date: Apr 2003
Location: Bristol, UK
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
richlewt
Quote:
Originally posted by Pat Hartman
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?
__________________
cheers
Rich
richlewt is offline   Reply With Quote
Old 11-03-2003, 03:04 PM   #12
RayH
Newly Registered User
 
RayH's Avatar
 
Join Date: Jun 2003
Location: Oregon
Posts: 132
Thanks: 0
Thanked 5 Times in 4 Posts
RayH is on a distinguished road
Is it not possible for you to have a query to extract the text data you want?
Like this:

Code:
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]);
__________________
Office 2010
RayH is offline   Reply With Quote
Old 11-03-2003, 06:46 PM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,982
Thanks: 13
Thanked 1,538 Times in 1,463 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 12:02 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World