Setting subform recordset 2

[wokkie]

Hi,

I am using Access 2003 with a SQL Server 2000 backend

I am having problems setting a subform's ADODB recordset in VBA.

For a normal form I have used
'Set Forms("frmSearchPerson").Recordset = rsSearchResults

But I can't figure out the syntax when it is a subform.

I have tried the following:
'Set Forms("frmTestVolume!frmVolumeSubscribedJournals").Recordset = rsJournals
'Set Forms("frmTestVolume.frmVolumeSubscribedJournals").Recordset = rsJournals
'Set Forms("frmTestVolume"!"frmVolumeSubscribedJournals".Form).Recordset = rsJournals

'set (forms!frmTestVolume!frmVolumeSubscribedJournals.form).recordset = rsJournals
Set Forms("frmTestVolume").Form("frmVolumeSubscribedJournals").Recordset = rsJournals

But none of them work.

How do I set a subform's recordset?

Thanks

 

[RoyVidar]

Hi!

The general syntax might be something like this:

[tt]Set Forms("frmTestVolume")("frmVolumeSubscribedJournals").Form.Recordset = rsJournals[/tt]

or from the main form:

[tt]Set Me("frmVolumeSubscribedJournals").Form.Recordset = rsJournals[/tt]


- but, be sure you have the correct subform control nave, which might differ from the name of the subform as viewed in the database window (hint - use the expression builder in a controlsource to double click thru forms, loaded forms, main form, subform and a control on the subform to obtain the correct reference)

Roy-Vidar

 

[LittleSmudge]

I use this subroutine which is stored in a Module

Public Sub SetSubFormRecordSource(frmSubForm As Form _
                                , strSubTable As String _
                                , strFKName As String _
                                , lngFKValue As Long)
' Simple form. Single Field and value in Where clause. No Order By
On Error GoTo Err_SetSubFormRecordSource

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient

rst.Open "SELECT * " _
       & "FROM " & strSubTable & " " _
       & "WHERE " & strFKName & " = " & lngFKValue, MyConn
Set frmSubForm.Recordset = rst
rst.Close
Set rst = Nothing

Exit_SetSubFormRecordSource:
Exit Sub
Err_SetSubFormRecordSource:
If Err.Number = 3709 Then   ' MyConn = nothing
    Call SetMyConn
    Resume
Else
    MsgBox Err.Description, , Err.Number
    Resume Exit_SetSubFormRecordSource
End If

End Sub

I call it using

Set frm = Forms!frmFormABC!subfrmFormA.Form
Set frm.Recordset = Nothing
Call SetSubFormRecordSource(frm, "tblFormA", "FormABCRef", FormABCID)

( The middle line Set .. .. = Nothing is only needed when updating an existing. If it is in the main form's On_Load call you don't need it. )


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[wokkie]

Cheers guys,

I have got it working now, RoyVidar's advice about checking the name was very useful.

I changed the name of a form after putting it as a subform, but the name change wasn't relected. It wasn't until I checked the name I realisd the problem.

Thanks again

Ian