Solved Form with ADO recordset not showing correct filter (1 Viewer)

Adelina_RO

Member
Local time
Today, 19:30
Joined
Apr 9, 2021
Messages
41
Hi again :)
Here goes:
I have a continuous form to which i set the Form.Recordset property at runtime based on a ADO recordset which runs a saved procedure on a MariaDB server like so:
Code:
On Error GoTo Eroare

SQL_SVN_OC 'function that checks if the public ADODB.Connection is running and restores it if it's not

Set BaseRs = New ADODB.Recordset

BaseRs.CursorLocation = adUseClient

BaseRs.Open "CALL `Base_Baza`('%', 'DataPrimire DESC, IdBaza DESC')", SQL_SVN, adOpenKeyset, adLockOptimistic

Set Me.Form.Recordset = BaseRs

The problem is that when i filter either the form or the underlying recordset on a DATE field, using ([Field]>=#01/01/2021# ) AND ([Field]<=#01/01/2022# ) the resulting BaseRs.RecordCount = 50,
Frm.Form.Recordset.RecordCount = 50,
the displayed number of records is 300 !?!?

Code:
Function ADO_Filter(Optional Flt As String = "") As Long
Dim Frm As Access.Form

If IsLoaded("Base") Then
    Select Case Forms!base!SelTab
        Case "nvB1"
            Set Frm = Forms!base!nvSF!locBaza.Form
        Case "nvB2"
            Set Frm = Forms!base!nvSF!locDosar.Form
    End Select
Else
    Set Frm = Forms!baza1.Form
End If

Application.Echo False

Flt = "(DataPrimire>=#01/06/2021#) AND (DataPrimire<=#30/06/2021#)"

If Flt = "" Then
    BaseRs.Filter = adFilterNone
Else
    BaseRs.Filter = adFilterNone
    BaseRs.Filter = Flt
End If

Application.Echo True

Set Frm.Recordset = BaseRs

ADO_Filter = CLng(BaseRs.RecordCount)

CountValues

Set Frm = Nothing
End Function

What the hell is going on?!

BONUS: If i click on any extra rows on the form and get the AbsolutePosition of the recordset it says -3 (EOF) ?!?!
 

Isaac

Lifelong Learner
Local time
Today, 09:30
Joined
Mar 14, 2017
Messages
5,914
Why are you setting the Form's Recordset? Did you mean to to be setting its Recordsource? Recordsource is what I normally would do/see people doing.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:30
Joined
Oct 29, 2018
Messages
17,154
Hi. Where is BaseRs declared? Is it a global variable? I don't know about ADO, but with DAO, to filter a recordset, you'll have to open a new one from the original rs.
 

MarkK

bit cruncher
Local time
Today, 09:30
Joined
Mar 17, 2004
Messages
7,851
If you set the Recordset.Filter property, I believe you have to open another recordset using Recordset.OpenRecordset to apply the filter. Try instead setting the Access.Form.Filter and Access.Form.FilterOn properties, and see if that works.
 

Adelina_RO

Member
Local time
Today, 19:30
Joined
Apr 9, 2021
Messages
41
Hi. Where is BaseRs declared? Is it a global variable? I don't know about ADO, but with DAO, to filter a recordset, you'll have to open a new one from the original rs.
BaseRS: It's a global ADODB.Recordset variable. The thing is, the filter works for exact dates, and the BaseRS actually gets filtered for fixed values. The only issue it has it's with the interval...
 

Adelina_RO

Member
Local time
Today, 19:30
Joined
Apr 9, 2021
Messages
41
SO... i'm super dumb. The problem wasn't in the logic - that worked fine. What fucked up everything were the "##". I thought that they were needed to define date values, but it appears that ADO filter doesn't care about that. It only wants a ' (apostrophe). So the code works if the pound sign is replaced with apostrophe...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:30
Joined
Oct 29, 2018
Messages
17,154
SO... i'm super dumb. The problem wasn't in the logic - that worked fine. What fucked up everything were the "##". I thought that they were needed to define date values, but it appears that ADO filter doesn't care about that. It only wants a ' (apostrophe). So the code works if the pound sign is replaced with apostrophe...
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom