How to update a combo box depending on previous selection

[Celor]

I'm making a database for a local school, and they need a form which lets them keep track of students assignments, and in turn the grades for that assignment.

So i have a form with all the basic controls on, what I want to know is, can I auto populate a combo box depending on what a user has already selected on a previous combo box.

So for example, I have a combo box called "Course", this is just a course present in the school, Computing for example.

Under that is a combo box called "Module", this is a part of the course. First i want that to only contain modules that are present on the already selected course (Programming for Computing, for example)

AND then (:-)) is another combo box under the module called "Assignment ", I want that to contain only assignments relating to the Module selected previously.

Finally, there is a student combo box, which i want to only contain the students actually on the course previously selected.

[Megalog]

The concept you're looking for is called Cascading combo boxes.
There's a good article here just for that: Cascading Combo/List Boxes

[Celor]

Exactly what i wanted :-)

Thank's a lot

[Celor]

Thought i'd post here instead of starting a new topic.

I've got the first two boxes done. My module combo box updates properly depending on the course selected.

But my assignment's dont. Im using pretty much the same code from the link posted by Megalog, but I try to test it and i get...

"Syntax Error (missing operator) in query expression '[Module] = Programming Fundamentals'"

Any idea what this means?

Thanks

[Megalog]

Please post the after update code for the Module combo box.

[Celor]

The code is:

Private Sub Module_AfterUpdate()
  With Me![Assignment]
    If IsNull(Me!Module) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [AssignmentName] " & _
                   "FROM Assignment " & _
                   "WHERE [Module]=" & Me!Module
    End If
    Call .Requery
  End With
End Sub

[Megalog]

Is the Module field in AssignmentName numeric or text? If it's text, you need to change line 8 to be:

                   "WHERE [Module] = '" & Me!Module & "'"

[Celor]

Just tried your suggestion, but they are both numeric so I got the data mistype error.

[Celor]

EDIT: Deleted because I've solved it. :-)

Made it search for the ModuleID and the ModuleName, bound the assignment box to column 1 (the ID) then made it only display the actual ModuleName. :-)

[NeoPa]

A bit late now, but I expect Example Filtering on a Form would have helped you with this. It covers the formats of numeric, string and date literals.