Changing the Record source in a subform

[ibethea]

I'm not able to reference a subform to change the recordset.
here is some code:

...
rst.CursorLocation = adUseClient
strSQL = "SELECT t_projectStaff.[JDE Proj Num] AS [JDE Number], [firstname] + ' ' + [lastname] AS FullName," & _
" t_codes.Description AS Title, t_projectStaff.startDate AS [Start Date]," & _
" t_projectStaff.endDate, t_projectStaff.STAFFID,[firstname] + ' ' + [lastname] AS Name" & _
" FROM (t_codes INNER JOIN t_projectStaff ON t_codes.Code = t_projectStaff.staffRole)" & _
" INNER JOIN t_employeeMast ON t_projectStaff.[JDE Emp Num] = t_employeeMast.EmpId" & _
" WHERE (((t_projectStaff.[JDE Proj Num]) Like '" & projNum & "') AND" & _
" ((t_projectStaff.startDate) Is Not Null) AND" & _
" ((t_projectStaff.endDate) Is Null) AND" & _
" ((t_codes.Entity)='title'))" & _
" ORDER BY t_projectStaff.[JDE Proj Num]"
rst.Open strSQL, cn, adOpenStatic, adLockBatchOptimistic
Forms!f_ProjectStaff!f_subProjectStaff.SetFocus

variation 1 does not work:
Set Forms![f_ProjectStaff]![f_subProjectStaff].Recordset = rst

variation 2 does not work:
Forms![f_ProjectStaff]![f_subProjectStaff].RecordSource = strSQL

I know that the rst and or srtsql constains 10 records.

I have tried variation of the set command and variations without the set command.
It does not recognize the recordset or recordsource property.

 

[cmmrfrds]

A subform does not have a recordset or recordsource object there needs to be a link to the main Form.

 

[chrissie1]

it does support recordsource

try this

Forms.("f_ProjectStaff&quot.form("f_subProjectStaff&quot.RecordSource = strSQL
Forms.("f_ProjectStaff&quot.form("f_subProjectStaff&quot.requery

where "f_subProjectStaff" is the name of the subform object not the name of the form that is behind it (they can be the same)

subform and mainform can be independent but then dont forget to set the linkchilfield and linkmasterfields to nothing (empty)

Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[cmmrfrds]

Christiaan, thank you for correcting me, the subform does indeed have a recordsource and recordset object - brain lapse on my part. I did a test on using the recordset, but I did need to create an Instance of the Form to get it to work.

My test in the OnOpen Event of the subform.
Dim sql As String
sql = "SELECT [dbo_orderdetail].[ordnum], " & _
"[dbo_orderdetail].[prodnum], " & _
"[dbo_orderdetail].[shipdate] FROM dbo_orderdetail "

''''''Me.RecordSource = sql

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
Set cn = CurrentProject.Connection

rs.Open sql, cn, adOpenStatic, adLockOptimistic

Dim yy As Access.Form
Set yy = Forms![dbo_product]![dbo_orderdetail subform].Form
'--
Set yy.Recordset = rs

The above worked for me.