Assign recordset to subform

I have main form M that includes subform S. S is linked to M through field F. That bound is defined in S properties window.
Each of M and S have no Sourdce defined because I want to assign them ADO recordsets
Each of M and S have an open event like this:
Private Sub Form_Open(Cancel As Integer)
    Dim SQL As String
    Dim rst As New ADODB.Recordset
        SQL = "Select for that form"
        rst.CursorLocation = adUseClient
        rst.Open SQL, cnRech, adOpenKeyset, adLockOptimistic
        Set Me.Recordset = rst
End Sub

It works OK except for one thing:
-All recors of S as displayed for any record of M. So, the binding has no effect.

Anybody knows how to preserve the binding?
GiteAsked:
Who is Participating?
 
Leigh PurvisDatabase DeveloperCommented:
You filter the recordset - and then apply it.

rst.Filter = "F=" Me.F
Set Me.S.Form.Recordset = rst

Of course in your above example - you'd open a limited Select statement instead...
SQL = "Select for that form " & " WHERE F=" Me.F

If you open a full recordset and keep it open - only filtering it when you navigate from parent to parent record - then you'll likely see better performance responce as you navigate.
But you're doing so at the expense of a higher data request and memory usage.
0
 
Leigh PurvisDatabase DeveloperCommented:
If you're referring to Master/Child link fields then they will indeed have no effect in recordset binding.
You'll need to open your child recordset either limited or filtered to the appropriate records. (Which isn't difficult obviously - you just need to bear in mind what data you're asking for and holding open).
0
 
GiteAuthor Commented:
I tried this in the current event routine of my main form:
    Me.S.Form.Filter = "F=" Me.F
    Me.S.Form.FilterOn = True
I got runtime error 2467 "The expression references an object that is closed or deleted"

How would you go about filtering the subform?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
GiteAuthor Commented:
The solution worked for a while. Now, it works only when I trace the code (step-by-step). Normal execution gives run-time error 2455 (An expression reference to property Form/Report is not valid). I repaet: it works step by-step, but not otherwise!!! -WITH SAME DATA!!!

I tried compacting the adp project, but it changed nothing. I tried converting to Access 2002 (from Access 2000) - same thing. I tried a db into an empty one -no change!. The only thing I can see is that this morning I tried to open an ACCESS 2002 version of that ADP with Access 2007; Access reconfigured itself... However, the problem occurs even if I use 2000 or 2002. It also occurs in Access 2007 at the same place, though the error message is a bit different (error 30006 Methot Recordset for object _Form_frmSourcesActes" has failed

Here is the real thing. Form frmActes contains 2 subforms frmSourcesActes and frmTémoins. Here are the events procedures for each of them. frmActes also has a combo box (NoLieu) I also control through a recordset.
**********************Global variables
Public rsSourcesActes As New ADODB.Recordset
Public rsTemoins As New ADODB.Recordset
Public rsLieuActe As New ADODB.Recordset
Public cnRech as New ADODB.connection (opened before frmActes is opened)
 **********************frmActes
Private Sub Form_Current()
    Dim SQL As String
    Dim rst As New ADODB.Recordset
    rsSourcesActes.Filter = "NoDoc=" & Me!NoDoc & " AND NoActe=" & Me!NoActe
    Set Me.frmSourcesActes.Form.Recordset = rsSourcesActes (****** INTERMITTENT ERROR HERE***)
    rsTemoins.Filter = "NoDoc=" & Me!NoDoc & " AND NoActe=" & Me!NoActe
    Set Me.frmTémoins.Form.Recordset = rsTemoins
    ...
end sub
Private Sub Form_Open(Cancel As Integer)
    Dim rc As Integer
    Dim SQL As String
    Dim rst As New ADODB.Recordset
        SQL = "SELECT ...
        rst.CursorLocation = adUseClient
        rst.Open SQL, cnRech, adOpenKeyset, adLockOptimistic
        Set Me.Recordset = rst
        SQL = "SELECT ..."
        rsLieuActe.CursorLocation = adUseClient
        rsLieuActe.Open SQL, cnRech, adOpenKeyset, adLockOptimistic
        Set Me!NoLieu.Recordset = rsLieuActe
        ...
End Sub
 **********************frmSourcesActes
Private Sub Form_Open(Cancel As Integer)
    Dim SQL As String
    SQL = "SELECT ..."    
    rsSourcesActes.CursorLocation = adUseClient
    rsSourcesActes.Open SQL, cnRech, adOpenKeyset, adLockOptimistic
    Set Me.Recordset = rsSourcesActes
End Sub
 **********************frmTémoins
Private Sub Form_Open(Cancel As Integer)
    Dim SQL As String
    SQL = "SELECT ...
    rsTemoins.CursorLocation = adUseClient
    rsTemoins.Open SQL, cnRech, adOpenKeyset, adLockOptimistic
    Set Me.Recordset = rsTemoins
End Sub

ANY IDEA HOW TO GET OUT OF THAT VICIOUS CIRCLE?
0
 
GiteAuthor Commented:
I don't know how, but I managed to get n adp working again.
However, when I converted it to Access 2000, neither the converted file NOR THE ORIGINALwould work again. run-time error 2455 (An expression reference to property Form/Report is not valid) always at same place.
However, the copy I made of the original before the conversion still works.
WHY? ANYTHING TO DO?
0
 
GiteAuthor Commented:
LPurvis:
I have some toher news. Since thsi question has been closed, I'il open a new one for this new problem. That will allow you to get thes other points.
0
 
Leigh PurvisDatabase DeveloperCommented:
Hello.
The lack of response from me thus far was simply due to my not being around here ;-)
No need to start another question unless a) it's a separate issue (in which case EE requires you to keep them separate) or b) if the answered question might be nearing it's lock time (questions lock a certain time after being answered - it used to be a week, but I'm not sure now).

I'll look in the new thread now you've created it. :-S
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.