Dropdown - need help please

[sullyman]

Hi Folks,

Have managed to get two cascading dropdowns working but need three and can't get the third working. (see included file)

The CHistory form will only used to view courses completed by employees. I hope to include a Subform on the CHistory form to show the employees when the third dropdown is chosen.

Please help as this is driving me nuts. Have been all over the web but there does not seem to be any tutorials or sites explaining anything more than 2 cascading dropdowns.

Thanks

 

[pbaldy]

You don't see examples of more than 2 because the technique used would be the same. You have code in the after update event of the first combo to set the second, but you have no code in the after update event of the second to do whatever you want done after that selection.

 

[sullyman]

Thanks for reply. Yes, i have left it blank as i am not sure what to put in. I have tried everything in the afterupdate event but can't get anything working. I have tried something like the following but it is not working. Any direction would be great.

"SELECT Distinct Courses.Course FROM" & _
" Years INNER JOIN Courses INNER JOIN Locations INNER JOIN CHistory ON Locations.LocationID = CHistory.LocationID ON Courses.CourseID = CHistory.CourseID ON Years.YearID = CHistory.YearID WHERE [CHistory].[YearID] = " & _
Me.CboYear & _
" AND [CHistory].[LocationID]" & _
Me.CboLocations & _
"ORDER BY Courses.Course"

 

[pbaldy]

You probably want an equals sign in here:

" AND [CHistory].[LocationID]" & _

and a space at the beginning of:

"ORDER BY Courses.Course"

 

[sullyman]

Thanks Paul - I'm still not having any luck after changing it to below:

Me.CboCourse.RowSource = "SELECT Distinct Courses.Course FROM" & _
" Years INNER JOIN Courses INNER JOIN Locations INNER JOIN CHistory ON Locations.LocationID = CHistory.LocationID ON Courses.CourseID = CHistory.CourseID ON Years.YearID = CHistory.YearID WHERE [CHistory].[YearID] = " & _
Me.CboYear & _
" AND [CHistory].[LocationID] = " & _
Me.CboLocations & _
" ORDER BY Courses.Course"
Me.CboCourse = Me.CboCourse.ItemData(0)

 

[sullyman]

Any further help on this would be appreciated please folks

 

[pbaldy]

Remember we're all volunteers here. A "reminder" after 11 minutes is a little much. For starters, I think your SQL should be based on the CHistory table. Second, I think your SQL fails because of this:

http://www.mvps.org/access/lookupfields.htm

I would get rid of them all.

 

[sullyman]

Hi paul,

Thanks. Sorry about 'reminder' but i am trying to get this working for 2 days now and can't believe there are not more tutorials or a downloadable files online from Microsoft themselves showing how this technique is done. As a novice, it is very fustrating getting so far and then reaching a stumbling block.

Are you referring to the lookup fields in the CHistory Table?

 

[pbaldy]

Like I said, the reason you don't see examples of 3 or more is because the technique would be the same, whether there's 2 or 20. You're running into SQL problems now, not "technique" problems.

I note somebody posted a sample db for you elsewhere, so I'll stand down for now.

 

[sullyman]

Can anyone post a working demo involving 3 combos so i can work this out please

 

[sullyman]

Hi Paul - Yes, unfortunately it was not a correct working example. Any chance you can post one please

 

[sullyman]

Any working demo of a 3 dropdown scenario would be appreciated please folks so us novices can understand same. I am at the point of giving up. Have to admit that filemaker is way easier than this

 

[pbaldy]

Like this?

 

[sullyman]

Paul - Many thanks, that's perfect. i have been messing around with your new file today and have learned a lot. Much appreciated.

 

[pbaldy]

No problem, glad it helped.