Datasheet based on combo box

[Guest]

I have a combo box (Combo8) containing a list of names on a main form
(fSwitchboard) that also has a subform in datasheet view (fJobListSubform)
that lists jobs assigned to each named person in the combo box. The
following is code related to making it work - the problem is it will only
show jobs for just one person in the list. Can someone please tell me what
is wrong? Any help is greatly appreciated!

Private Sub Form_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[JobNumber] = " &
str(Nz(Forms!Form1!qJobListSubform.Form!JobNumber, 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo8_AfterUpdate()
Me.qJobListSubform.Requery
End Sub

Thanks, Phisaw

 

[Guest]

Nothing is wrong. That is the way it works. Sub forms are meant to show
records in one table that are related to a single record in the main form.

 

[Guest]

I'm sorry - maybe I wasn't clear in explaining the problem I was having. I
have six people in the list. It would only populate the subdatasheet for
only one person when selected. If I selected someone else, nothing would
fill in and each of the six had several entries each. But, eventually I
found something that would work.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "Select* From qJobList Where [JobAssignment] = '" & Me![Combo8]
& "'"
Me.RecordSource = strSQL
End Sub

Thanks for the reply.
Phisaw

Nothing is wrong. That is the way it works. Sub forms are meant to show
records in one table that are related to a single record in the main form.

I have a combo box (Combo8) containing a list of names on a main form
(fSwitchboard) that also has a subform in datasheet view (fJobListSubform)
that lists jobs assigned to each named person in the combo box. The
following is code related to making it work - the problem is it will only
show jobs for just one person in the list. Can someone please tell me what
is wrong? Any help is greatly appreciated!

Private Sub Form_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[JobNumber] = " &
str(Nz(Forms!Form1!qJobListSubform.Form!JobNumber, 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo8_AfterUpdate()
Me.qJobListSubform.Requery
End Sub

Thanks, Phisaw