UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Filter Adorecordset Was Disconnect, Access 2003    
 
   
mr.siro
post Oct 11 2018, 09:44 PM
Post#1



Posts: 130
Joined: 27-January 18



hello guys.
I have function to connect SQL server and get recordset:
CODE
Public Function AdoRecordset(StoreProd As String, Contrl As Object, LockTyp As ADODB.LockTypeEnum, CursorTyp As ADODB.CursorTypeEnum) As Boolean
Call openconnect 'this was open connect, open ADOconn

    Dim ADOrec As New ADODB.Recordset
    Set ADOrec.ActiveConnection = ADOConn
        ADOrec.LockType = LockTyp
        ADOrec.Source = StoreProd
        ADOrec.CursorType = CursorTyp
        ADOrec.CursorLocation = adUseClient
        ADOrec.Open
    Set Contrl.Recordset = ADOrec
        ADOrec.Close
    Set ADOrec = Nothing

call closeconnect 'this was close connect, close ADOconn
End Function


on form load event i call: Call AdoRecordset("exec nameStoreProd", Me.Form, adLockReadOnly, adOpenStatic)

Everything work fine, form has recordset.
On afterupdate txtFilter, i have :
CODE
me.filter = "namefield like '*" & me.txtFilter & "*'"
me.filterOn = true


But it's not work, somebody can help me out of problem.
This post has been edited by mr.siro: Oct 11 2018, 09:46 PM
Go to the top of the page
 
Doug Steele
post Oct 11 2018, 10:11 PM
Post#2


UtterAccess VIP
Posts: 22,011
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I believe ADO uses % as the wildcard, not *.

Try

CODE
me.filter = "namefield like '%" & me.txtFilter & "%'"
me.filterOn = true



--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
mr.siro
post Oct 11 2018, 10:24 PM
Post#3



Posts: 130
Joined: 27-January 18



hello, i change to what you said.
When i filter first time it's still the same, not filter recordset, however in navigation bar show record 1 of 999 (filtered).
When i filter the secondtime i get err "application-defined or object-defined error" at the line :
CODE
me.filter = "namefield like '%" & me.txtFilter & "%'"

Go to the top of the page
 
cheekybuddha
post Oct 12 2018, 06:34 AM
Post#4


UtterAccess VIP
Posts: 10,658
Joined: 6-December 03
From: Telegraph Hill


Hi,

Since you are using an ADO recordset bound to your form, try applying the filter directly to the recordset rather than via the form's filter mechanism.
CODE
  Dim strFilter As String

' Set filter
  strFilter = "namefield like '%" & me.txtFilter & "%'"
  Me .Recordset.Filter = strFilter
' no need for FilterOn = True - it doesn't exist for an ADODB.Recordset

'Clear filter
  strFilter = vbNullString
  Me.Recordset.Filter = strFilter


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mr.siro
post Oct 12 2018, 07:26 AM
Post#5



Posts: 130
Joined: 27-January 18



hello cheekybuddha.
it's not work.
Go to the top of the page
 
cheekybuddha
post Oct 12 2018, 07:50 AM
Post#6


UtterAccess VIP
Posts: 10,658
Joined: 6-December 03
From: Telegraph Hill


What didn't work? More details please. Remember, we can't see your machine!

Did it throw an error?

Did the code compile? (I suspect not, if you just copied and pasted - I just spotted a typo, an extra space between Me and .Recordset.

And obviously, if you run all the code I listed together, you won't see anything because it sets the filter and then immediately unsets it!

Show the (exact) code you used.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mr.siro
post Oct 12 2018, 08:03 AM
Post#7



Posts: 130
Joined: 27-January 18



yes, i know that, it's me.recordset.filter.
But it's not work, don't get error, just nothing happens.
After update txtfilter i write: me.recordset.filter = "namefield like '%" & me.txtfilter & "%'"
Function connect:
CODE
Public Function OpenConnect() As Boolean
    Dim DataServer, DataName, DataUser, DataPass As String
    DataServer = "localhost"
    DataName = "test"
    DataUser = "sa"
    DataPass = "123456789"
    
    On Error Resume Next
    With ADOConn
        .ConnectionString = "Provider = SQLOLEDB;" _
                          & "Data Source = '" & DataServer & "';" _
                          & "Initial Catalog = '" & DataName & "';" _
                          & "user id = '" & DataUser & "';" _
                          & "password = '" & DataPass & "';"
        .Open
    End With
    
    ' Check connection state
    If ADOConn.state = 0 Then
        OpenConnect = False
    Else
        OpenConnect = True
    End If

End Function


function close connect:
CODE
Public Function CloseConnect()
    ADOConn.Close
    Set ADOConn = Nothing
End Function

This post has been edited by mr.siro: Oct 12 2018, 08:10 AM
Go to the top of the page
 
cheekybuddha
post Oct 12 2018, 08:46 AM
Post#8


UtterAccess VIP
Posts: 10,658
Joined: 6-December 03
From: Telegraph Hill


OK, I'm hoping others will jump in with more suggestions as I don't have Access on this machine to test.

What happens if you issue a Me.Requery after setting the filter?

Also, what values are you passing as LockTyp and CursorTyp to function AdoRecordset()?

--------------------


Regards,

David Marten
Go to the top of the page
 
mr.siro
post Oct 12 2018, 08:54 AM
Post#9



Posts: 130
Joined: 27-January 18



adLockReadOnly and adOpenStatic.
I do with me.requery and me.refresh, don't work.
This post has been edited by mr.siro: Oct 12 2018, 08:55 AM
Go to the top of the page
 
cheekybuddha
post Oct 12 2018, 09:02 AM
Post#10


UtterAccess VIP
Posts: 10,658
Joined: 6-December 03
From: Telegraph Hill


Change the LockTyp to adBatchOptimistic

--------------------


Regards,

David Marten
Go to the top of the page
 
mr.siro
post Oct 12 2018, 09:40 AM
Post#11



Posts: 130
Joined: 27-January 18



not work, frown.gif(
Go to the top of the page
 
mr.siro
post Oct 13 2018, 09:01 AM
Post#12



Posts: 130
Joined: 27-January 18



hello, somebody can help me ?
Go to the top of the page
 
cheekybuddha
post Oct 13 2018, 09:31 AM
Post#13


UtterAccess VIP
Posts: 10,658
Joined: 6-December 03
From: Telegraph Hill


mr.siro,

Does your code work before you try to filter it?

i.e. Does the form display the whole recordset returned by the SP?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 13 2018, 09:53 AM
Post#14


UtterAccess VIP
Posts: 10,658
Joined: 6-December 03
From: Telegraph Hill


Your connect string looks totally wrong

--------------------


Regards,

David Marten
Go to the top of the page
 
Doug Steele
post Oct 13 2018, 10:56 AM
Post#15


UtterAccess VIP
Posts: 22,011
Joined: 8-January 07
From: St. Catharines, ON (Canada)


What's your objection to the connection string, David? It looks correct for the Microsoft OLE DB Provider for SQL Server

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
cheekybuddha
post Oct 13 2018, 11:07 AM
Post#16


UtterAccess VIP
Posts: 10,658
Joined: 6-December 03
From: Telegraph Hill


The single quotes around the passed values - I didn't think they were needed in a connection string

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 13 2018, 11:09 AM
Post#17


UtterAccess VIP
Posts: 10,658
Joined: 6-December 03
From: Telegraph Hill


'totally' might have been a tad harsh! blush.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
Doug Steele
post Oct 13 2018, 11:15 AM
Post#18


UtterAccess VIP
Posts: 22,011
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Mr.siro: since you've never bothered explaining what you mean by "doesn't work", we're (well, David is) really groping in the dark.

See whether changing function OpenConnect to the following helps:

CODE
Public Function OpenConnect() As Boolean
    Dim DataServer As String
    Dim DataName As String
    Dim DataUser As String
    Dim DataPass As String
  
    DataServer = "localhost"
    DataName = "test"
    DataUser = "sa"
    DataPass = "123456789"
    
    On Error Resume Next
    With ADOConn
        .ConnectionString = "Provider = SQLOLEDB;" _
                          & "Data Source = " & DataServer & ";" _
                          & "Initial Catalog = " & DataName & ";" _
                          & "user id = " & DataUser & ";" _
                          & "password = " & DataPass & ";"
        .Open
    End With
    
    ' Check connection state
    If ADOConn.state = 0 Then
        OpenConnect = False
    Else
        OpenConnect = True
    End If

End Function


--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
mr.siro
post Oct 13 2018, 08:40 PM
Post#19



Posts: 130
Joined: 27-January 18



ok this is all the way i get recordset to form.
First, store procedure is selec field inner join 3 table on sqlserver, have no parameter. Mainfrom and subform, mainform have no connect to recordset, subform is.
I have function openconnect and closeconnect and Adorecordset which i show in that posts.

On event form load subform i call Adorecordset, it's work fine, subform have recordset. On event after update txtfilter i write code to filter like your said, but nothing happen, the recordset not filter, it's the same
Code to get recordset:
CODE
Call AdoRecordset("exec listname", Me.Form, adLockBatchOptimistic, adOpenStatic)

Code txtfilter:
CODE
Me.Recordset.Filter = "fieldname like '%" & Me.txtfilter & "%'"
Go to the top of the page
 
mr.siro
post Oct 14 2018, 10:20 PM
Post#20



Posts: 130
Joined: 27-January 18



hello guys, i find out the way to sloved this problem.
On after update txtfilter i writer code which call new connection and get new recordset have filter:
CODE
Call OpenConnect
Dim ADOrec As New ADODB.Recordset
    Set ADOrec.ActiveConnection = ADOConn
        ADOrec.LockType = adLockReadOnly
        ADOrec.Source = "exec listname"
        ADOrec.CursorType = adOpenStatic
        ADOrec.CursorLocation = adUseClient
        ADOrec.Open
        ADOrec.Filter = "namefield like '*" & Me.txtfilter & "*'"
        Set Me.Recordset = ADOrec
        ADOrec.Close
    Set ADOrec = Nothing
Call CloseConnect

This code work fine, recordset have filter.
But, if use this way, then after change value of txtfilter that open connect and get recordset, it's do again and again, too many times connect and getcordset. I think this way not to perfect.
So, i find to some way:
On load form:
+ Get recordset that is not have filter yet. And declare variable rs as adodb.recordset.
CODE
Option Compare Database
Private rs As ADODB.Recordset

CODE
Onload_form
Call openconnect
    Dim ADOrec As New ADODB.Recordset
    Set ADOrec.ActiveConnection = ADOConn
        ADOrec.LockType = adLockReadOnly
        ADOrec.Source = "exec listname"
        ADOrec.CursorType = adOpenStatic
        ADOrec.CursorLocation = adUseClient
        ADOrec.Open
        Set me.recordset = ADOrec <- have not filter yet
        Set rs = ADOrec <- have not filter yet
        ADOrec.Close
    Set ADOrec = Nothing
Call closeconnect

After update event of txtfilter:
CODE
rs.filter = "fieldname like '*" & Me.txtfilter & "*'" <- set filter for rs
set me.recordset = rs <- get recordset has filter for form

But i get error: "method 'recordset' of object 'form_name' failed" on the line
CODE
set me.recordset = rs

I think i very close to sloved my situation if i pass this thing. So sombody can help me?
This post has been edited by mr.siro: Oct 14 2018, 10:26 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th December 2018 - 06:11 PM