Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 880
  • Last Modified:

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?
0
Gite
Asked:
Gite
  • 5
  • 3
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now