Cascading Combo Box Problems!

[aqueousdan]

I have made a small database which will house details of engineers and machines over various factories on three different sites of a company.

I would like to use cascading combo boxes to only show the factories on a certain site and also only the machines and engineers that are available on that site.

I have looked at various tutorials on how do to this online and when I test it with my own form it just doesnt work

Could anyone tell me how to do it with my database or give me a great tutorial on how to achieve what I want to do?

my database is located here


Thanks so much,

Dan

[rpeare]

Zip up your database and attach it to the thread using the 'go advanced' button in the lower right of the posting area. I am not able to get to foreign sites.

To answer your question though

Your queries would have to contain a criteria of something like

[forms]![formname]![CASCADING PRECUROR FIELD NAME]

then you would issue a [combobox name].requery any time the parent choice was changed (on exit is what I usually use)

This assumes you have your tables set up appropriately

tblEngineer
E_ID  E_FirstName  E_LastName ---> other engineer specific fields

tblMachine
M_ID  M_Name ---> other machine specific fields

tblSite
S_ID  S_Name ----> other site specific fields

tblSiteMachine
SM_ID  S_ID  M_ID ---> other site/machine specific fields

tblSiteEngineer
SE_ID  S_ID  E_ID ---> other site/engineer specific fields

if you set it up this way you can transfer engineers or machines from one site to another if needed then it's pretty easy to set up the cascading combo boxes

[aqueousdan]

Ok i think ive attached the file

[rpeare]

You have to include the ID field as the first field of all your combo boxes i.e.

SELECT Site.SiteID, Site.SiteName FROM Site GROUP BY Site.SiteID, Site.SiteName; 
SELECT Factory.FactoryID, Factory.FactoryName FROM Factory WHERE (((Factory.SiteID)=[forms]![form1]![combo0]));

site combo box and factory combo box respectively

Make the column count 2 for all combo boxes
Make the first column width 0 so you only see the description you're interested in

remove the AFTER UPDATE event from combo0

add in the ON EXIT property of Combo0: combo2.requery

[aqueousdan]

Sorry im completely new to all of this. I have made the changes you said in this last reply but now I get an error saying Microsoft Access cant find the object Combo2.

[rpeare]

You'll have to change the names for your specific database, I am commenting on the database you attached. The control name (combo box name) has to match whatever you have on your real form.

[aqueousdan]

Yes this is the database i am using.

[rpeare]

you must have changed something then. ON the database you uploaded combo2 is the combo box associated with factoryname, if you change the names from what you uploaded you have to change all the associated queries etc. to match the new object name.

[aqueousdan]

Ok here is what I have. I have put it in a few times and still get the same error.

[ssanfu]

It is a really, really, really bad idea to have a PK field as a Memo type field.

See
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm


I always use an Autonumber type field as a primary key field (PK)
And the foreign key fields (FK) are Long Integers.


I would advise you to work through these two tutorials: http://www.rogersaccesslibrary.com/forum/forum46.html
Really work through them, don't just read them..... It will help a lot.