Combo Box Query Problems

[yanos]

Hi all.
I am a relative beginner using Access and I am still using 2003 I am trying to develop a database to store student details, module details and area of expertise of lecturers
The tables are Students,ModuleName, Instructors and Expertise. I have a students form with a module subform. In this subform I am entering the following data, Module Name; Area of Expertise and Lecturer. The idea being to select the area of expertise (cboExpertise) and this filters the lecturers listed in the following combo box (cboInstructor).
I have looked at and tried to use the various examples but I am doing something stupid.

Query in RowSource of cboInstructors
SELECT [Expertise].[Expertise], [Expertise].[AreasID] FROM Expertise;
Query in RowSource of cboExpertise
SELECT [Instructors].[ InstructorsID], [Instructors].[AreasID], [Instructors].[ Instructor] FROM Instructors;

Private Sub cboInstructors_AfterUpdate()
Dim sInstructorSource As String
sInstructorSource = "SELECT [Instructors].[InstructorsID], [Instructors].[AreasID], [Instructors].[Instructor] " & _
"FROM Instructors " & _
"WHERE [AreasID] = " & Me.cboInstructors.Value
Me.cboExpertise.RowSource = sInstructorSource
Me.cboExpertise.Requery
End Sub

Help please

 

[ajetrumpet]

First thing I'd like to know is why you are filtering the first box with the code when you're trying to perform the filter on the second box??? I would drop code all together here, and just write rowsource statements in both boxes' properties. For expertise, it would be:

select distinct expertise.areasid from expertise

...and then for the instructors it would be:

select instructors.instructor from instructors
   where instructors.areasID = forms!formName!cboexpertise

AfterUpdate event of cboexpertise is then a one-liner:

me.cboinstructors.requery

 

[yanos]

We Continue with combo box Query Problems

Hi,
Thanks for the help. Seems to have helped a lot, I can select the Area of Expertise but in the second combo box I get the following error message:

Enter Parameter Value
Forms!Students! Module_Subform!cbo Expertise


I submitted in your code the main form being students and the subform is Module_Subform I hope I was right to do so
After trial and error it seems to be asking for the AreasID

Help please

Iainhttp://www.access-programmers.co.uk/forums/images/smilies/confused.gif

 

[ajetrumpet]

first of all, NICE TITLE.


And now for what counts...

You didn't tell me that the second box was on a subform. You need correct syntax for this. First look at Bob's syntax page to see if you can gain insight:
http://www.access-programmers.co.uk/forums/showthread.php?t=127981

 

[yanos]

Combo box Problems

Cheers big man I shall have a wee look.

Ta
Iain

 

[ajetrumpet]

You may find image tags useful too...

'http://www.access-programmers.co.uk/forums/images/smilies/confused.gif =