setting recordset on form hangs access 2

[1DMF]

Hi,

Can someone explain to me why after applying a filter to a DAO.recordset and then using

Set Me.accounts_search_results.Form.Recordset = rs.Recs

MS Access hangs and has to be killed.

Yet if prior to setting the form record source I include

rs.Recs.MoveLast

It works fine, so What gives?

thanks,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[PHV]

What is rs ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

rs is a DAO.Recordset

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

Well no sorry, it's my class wrapper for a DAO.Recordset.

I've added the MoveLast to the wrapper class and it works fine, just won't work without it?

Public Property Get Recs() As DAO.Recordset

    If cloneRS Is Nothing Then
        MsgBox "Recordset is null"
    End If

    Set Recs = cloneRS
    If (Recs.RecordCount > 0) Then
        Recs.MoveLast
    End If
    
End Property

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[PWise]

let us start looking @ this problem if you set the form recordset to a regualer recordset dose it work ?

set Set Me.accounts_search_results.Form.Recordset = currentdb.openrecordset (Sqlstring)

 

[1DMF]

yes, although painfully slow!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[PWise]

why dont you try an ado recordset

 

[1DMF]

because when I moved away from binded objects to late bindings

I was recommended by the access forum to use DAO and so did a global replace of every line of code in the huge application to use DAO.

what does it matter?

And at the end of the day, just issuing a MoveLast after the filter has been done on the cloned recordset works fine, so I guess if it aint broke!

I just wondered why this bizzarreness was happening, I guess because it's MS access and DAO eh?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[PWise]

if this is a SQL Server table i would use ado

 

[1DMF]

- So i keep hearing.

I'll give it a whirl and see if it makes a difference.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

Now I'm just getting a tonne of bugs/error that seem to want me to rewrite far too much code...

Private mainRS As ADODB.Recordset
Private cloneRS As ADODB.Recordset
Private sQuery As String
Private db As ADODB.Database
Private qd As ADODB.QueryDef

the bottom two lines now won't compile, I have no idea how to achieve the same thing using ADO. I can't spend the rest of the day rewriting everything using methods I have no idea how to use.

How do you manipulate query defs with ADODB ?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[PHV]

How do you manipulate query defs with ADODB ?
Database and QueryDef are DAO objects.

 

[1DMF]

Thanks PHV,

So I would still be using part DAO, got it.

What benefits does a ADO recordset have over DAO?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[MajP]

So I would still be using part DAO, got it

That is not what he is saying. He is just saying those exact objects are not ADODB objects. Things are done differently.

There is a ton of information on the two models. But in general
ADODB: A more robust, flexible model designed to handle all kinds of data sources and used in all kinds of applications

DAO: Designed specifically for JET. Simple and very efficient with JET.

I am not convinced you need to go ADODB just because it is a SQL Server backend although it may give you more flexibility depending on what you are doing. So most people would go ADODB. Again, however, DAO is very efficient and fast.

The fact that it hangs is not an issue of DAO vs ADODB in my opinion. It should not hang in either case and it is unlikely the cause of the problem. I would focus on identifying the problem. Going with ADODB could fix it because you are doing something in a poor way with DAO. But that could be like amputating your leg to cure a broken toe. That may solve the issue, but not necessarily needed.

 

[1DMF]

So what do you suggest Majp?

This is the entire class wrapper and the way I call it, why would this be hanging?

clsRS

Option Compare Database

Private mainRS As DAO.Recordset
Private cloneRS As DAO.Recordset
Private sQuery As String
Private db As DAO.Database
Private qd As DAO.QueryDef

Private Sub Class_Initialize()
    
    Set db = CurrentDb
    Set qd = db.QueryDefs("spPassThroughWeb")
    
End Sub

Public Property Get Recs() As DAO.Recordset

    If cloneRS Is Nothing Then
        MsgBox "Recordset is null"
    End If

    Set Recs = cloneRS
    If (Recs.RecordCount > 0) Then
        Recs.MoveLast
    End If
    
End Property

Public Sub setQuery(spQuery As String)
    
    If Nz(spQuery, "") = "" Then
        MsgBox "Invalid query string! format : ""spName 'param1', 'param2'..."""
    Else
        sQuery = "EXEC " & spQuery
    End If
    
End Sub

Public Sub setQD(spQD As String)
    
    Set qd = db.QueryDefs(spQD)
    
End Sub
Public Sub Run()

    If Nz(sQuery, "") = "" Then
        MsgBox "Invalid query string, process aborted!"
    Else
        qd.SQL = sQuery
        Set mainRS = qd.OpenRecordset
        Set cloneRS = mainRS.Clone
    End If
        
End Sub

Public Sub Filter(sFil As String)

    Set cloneRS = mainRS.Clone
    
    If Nz(sFil, "") <> "" Then
        cloneRS.Filter = sFil
        Set cloneRS = cloneRS.OpenRecordset
    End If
    
End Sub

i then use....

    Private rs As clsRS
    Set rs = New clsRS
    rs.setQuery "spAccountsSearch 'Paid'"
    rs.Run
    Set Me.accounts_search_results.Form.Recordset = rs.Recs

So far so good, all works fine... but then when the search box is used and the filter is applied

    Set Forms!Accounts_Search!accounts_search_results.Form.Recordset = Nothing

    DoEvents

    'Company Name
    If var = "Company" Then
        If Nz(Me.Company.Text, "") <> "" Then
                srch = "CompanyName LIKE '*" & Replace(Me.Company.Text, "'", "''") & "*'"
        End If
        
        If Not IsNull(Me.Company.Text) Then
            Me.Company.value = Me.Company.Text
            Me.Company.SetFocus
            Me.Company.SelStart = Len(Me.Company.value)
        End If
        
    Else
        If Nz(Me.Company, "") <> "" Then
                srch = "CompanyName LIKE '*" & Replace(Me.Company, "'", "''") & "*'"
        End If
    End If

    rs.Filter srch
    Set Me.accounts_search_results.Form.Recordset = rs.Recs

it hangs when resetting the form's recordset!

Only if you perform a 'MoveLast' against the record set prior to altering the form's recordset does it work.

(hence adding it as part of the clsRS class)

I put debug statements throughout each operation within the class and through out the other code so I could track after each line of code was executed and prior to its execution.

Everything worked swimingly until trying to re-alter the form's recordset, then it just hung.

The 'MoveLast' fixes the problem.

Any ideas?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[MajP]

Your solution is correct and needed, I believe. Because the clone does not provide a current record. This could cause the form to hang.

Creates a duplicate Recordset object that references the original Recordset object. The original and duplicate recordsets can have different current records. (Note, though, that after the cloning operation, the duplicate recordset initially has no current record.) Using the Clone method allows you to share bookmarks between Recordset objects, since their bookmarks are interchangeable

So the move first move last would solve that problem ensuring a current record. You can move that into you Filter method.


However, maybe something like this would fix. The filter property works on subsequent recordsets. So I think you can skip the clone. Untried.

Public Sub Filter(sFil As String)
    mainRs.filter = sFil
    Set cloneRS = mainRS.openrecordset
    if not (cloneRs.eof and cloneRS.bof) then
     rs.movelast ' ensure proper count
     rs.movefirst
   end if
   mainRs.filter = "" 
End Sub

Other things.

Using the Filter property on a recordset is generally less efficient than just applying a filter to a recordset and opening it in one step, using a SQL statement with a WHERE clause.

How inefficient I do not know. If it is possible to build the sql string instead (which may be difficult) then that may improve it.

DAO also has a Filter property, but it works differently. DAO works against subsequent Recordsets, while ADO works on the current Recordset

So if there is an issue here with this filter, ADO would in fact probably mask that problem

 

[1DMF]

I assume the way you set the filter against the mainRS, does not that affect the records shown in the mainRS hence the mainRS.filer =""

I don't think constantly re-running the SP with a 'where' clause is more efficent than performing filters against recordsets already in memory , do you?

Isn't that dramatically increasing network traffic and load on the SQL server?


-> Well I tried your refactoring of the method Fitler and now nothing works?

What i mean by that is the rs field on the form which is the NEW clsRS, seems to not be a valid reference to anything, as soon as I try to access "rs.Filter srch" from within the search routine, I get runtime error 424 'object required'?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

I decomplied and compact / repaired.

Your filter sub seems to have killed it?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

Also if you use 'MoveFirst' on its own, that hangs the DB also.

Only a MoveLast makes it work, even though when the recordset is displayed on the form the record selector highlights the first record.

Only if I do a 'msgbox rs.Recs.RecordCount' does it then move to the last record?

I'm going round in circles here!



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[MajP]

I got no idea and maybe someone else does. Anyone?

But what you have demonstrated is repeatable. Opening a new recordset on a filtered recordset and binding to a form is problematic. There are some other properties of the form that get set when binding the recordset and it appears to cause a loop.

Dim rs As DAO.Recordset
  Dim filrs As DAO.Recordset
  Set rs = Me.Recordset
  Set filrs = Me.Recordset.Clone
  filrs.Filter = "CustomerID = 'ANTON' OR CustomerID = 'BLAUS'"
  Set filrs = filrs.OpenRecordset(dbOpenDynaset, dbInconsistent)
  If Not (filrs.EOF And filrs.BOF) Then
    filrs.MoveLast
    MsgBox filrs.RecordCount
    MsgBox filrs!CustomerID
  End If
 Set Me.Recordset = filrs

The above code will return a new recordet with two records. You can verify the count and return the properties of the recordset as the message box shows.
When you try to bind this, two records show in the form with "#Name?" in the controls. Usually this is a sign that the control source is invalid. Then you get an error messages 1104. However if you test the control source it is still set.

I have never heard this mentioned but there are similar posts. For both filtering and sorting.

So my guess before you bind it to the form you need to set some property of the form or recordset. The above causes the same problem if you skip the clone and go directly to opening the recordset off of the filtered recordset.

So unless someone can explain this, if I had to filter the current recordset and bind it back to the form, I think I would go with ADO. In this case ADO allows you to apply a filter to the current recordset and not have to open a new recordset. However, I did not test this in ADO, but assumption is that it would be less problematic.

This problem appears specific to a form. If you do the same in a control like a combo there is no issue.