Populate Subform from Combo Box

[Guest]

Hi,

I need to populate subform with data based on choices from a Combo box.

If combo choice is “Admin†then populate subform with records like “A-FAâ€.
If combo choice is “Clinical†then populate subform with records like “C-CLâ€.

Example data follows:

Main Form name: DeptCourses
Combo Box name: cboDept
Subform name: CourseSubform

Department Table:
DeptID Department
------- ---------------------
1 Admin
2 Clinical
3 Lab

Course Table:
CourseID DocNumber
---------- -------------
1 A-FA-01
2 A-FA-02
3 C-CL-01
4 C-CL-02
5 L-CA-01
6 L-SN-01

ect. ect.

Thanks to all who can help!

 

[Guest]

Hello,

I found an example that sort of achieves what I needed, but there's still a
problem. My subform is only displaying a few lines, but after the requery the
new data may be further down in the table so that it does not display unless
I manually navigate to it.

Is there a way to use bookmark so the first record appears in the top line
of the subform? Here's the code I'm using in the combo box AfterUpdate
section:

Private Sub cboDept_AfterUpdate()
Dim strSQL As String

If Len(Nz(Me.cboDept, "")) > 0 Then
Select Case Me.cboDept.Column(1)

Case "Administration"
strSQL = "SELECT * FROM Course WHERE Course.[Document Number] Like
'A*';"

Case "Clinical"
strSQL = "SELECT * FROM Course WHERE Course.[Document Number] Like
'C*';"

End Select

Me.CourseSubform.Form.RecordSource = strSQL
Me.CourseSubform.Requery
End If
End Sub

-Simon