Report the current filtered records from a Form (in an ADP project)

[radio1]

---Report the current filtered records from a Form (in an ADP project)---

Hello All,

I've seen this topic discussed before, but the solution described then doesn't work in my particular case.

My Config: Access 2002 front-end using SQL Server 2000 (MSDE actually) via ADP/ADE Access Data Project.

I have a form (containing about 80 fields) on which I allow the user to apply field/form filters -- this works fine.

The form has a <print report> button which should trigger a report based on that filtered recordset -- this does not work.

I've tried the following VBA code in the button's click event handler, which I've read should work in plain Access, but fails for me because I'm using SQL Server at the back-end.

        Dim strWhere As String
        strWhere = ""
        If Me.Dirty Then
            Me.Dirty = False
        End If
        If Me.FilterOn Then
            strWhere = Me.Filter
        End If
        DoCmd.OpenReport "myreport", acPreview, , strWhere
 

The above code passes the form's current filter as a parameter to OpenReport, which fails with various SQL syntax errors because Me.Filter contains a SQL "where" clause (without the word "where), but it is in Access/Jet SQL format, not SQLServer's SQL format. It contains quotes instead of apostrophes around strings, uses "=True" for YES/NO fields instead of "=1" or "=-1" for SQLServer's BIT type fields, etc. This is strange because Access knows that it's using a SQLServer backend (it's a .ADP project).

Ideally I'd like to know how to pass the actual recordset to the report (instead of the above attempt at having the report re-query the db), but this doesn't seem possible?

A second-best solution I guess would be a way to retrieve the Me.Filter value in SQLServer's format, or call a function which does that for me.

Perhaps there are completely different approaches/solutions?

ANY help would be VERY MUCH appreciated! Please don't assume any idea is too obvious to suggest -- while I'm a 20-year coding veteran, I'm VERY new to Access and VBA.

A happy bonus to a solution would be to be able to pass the current sort-ordering of the form to the report as well.

Thank you very much,

-radio1

[MMcCarthy]

In a reports on open event you can reset the recordsource

 
Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "Some SQL string"
End Sub 
 
 

[MMcCarthy]

To get the sql string you could create a global variable of a string and populate it elsewhere.

[radio1]

Thanks very much for the reply, mmccarthy, but (and please forgive me if I'm incorrect!) I don't think your suggestion addresses my core problem.

In my ADP project, I'm trying to Report the data that is current in a Form -- by current I mean including any field/form filtering the user has done.

The code example I've shown tries to determine what the current user filters are via the Me.Filter variable from the Form.

The problem is that the Me.Filter string (essentially a sql WHERE clause without the word "WHERE") is in Access/Jet's sql syntax, but apparently needs to be in SQLServer's syntax to be used by the Report, and I think that would still be the case utilizing your suggested technique.

Sorry for the "think" part of this reply, but I'm not at the right computer to try out your idea today or probably tomorrow, and I wanted to at least acknowledge your reply as soon as I could.

By the way, some nice things are said about you in this other thread I was involved in: http://www.thescripts.com/forum/thread555184.html

Thank you very much again for your input,

-radio1

[NeoPa]

SQL Server will tend to conform more closely to the ANSI standards of Structured Query Language.
This is configurable in SQL server and the 'standard' type strings, using single-quotes ('), should also work in Access.
I suggest that you use these string terminators in all your SQL, especially if you plan to cross between any two.

Also, and I believe this holds true for Transact-SQL or T-SQL - (SQL Servers native SQL), booleans may be checked simply by referencing them. Unlike other values, they need not be compared to anything to return a logical result. They are already a logical result so don't need anything more. This should help to avoid differences between the two types.

EG.

WHERE (blnMyBoolean)
instead of
WHERE (blnMyBoolean = True)

[MMcCarthy]

Thanks very much for the reply, mmccarthy, but (and please forgive me if I'm incorrect!) I don't think your suggestion addresses my core problem.

You're forgiven. <grin> Flattered as I am by some of the appreciate comments I make plenty of mistakes just don't tell any of my clients.

I've learned the hard way just like most programmers and you'll pick up plenty of coding tips and tricks if you stay at this game long enough.

Now back to your question. ADP is not my area of expertise so forgive me if you can't access this.

Have you tried to use ..

Me.RecordsetClone

This should give you all data current on the form.

[NeoPa]

Ooh!
That's not one I was aware of - and may prove VERY useful.
I'll look into that in more detail on Monday.
Thanks for that M.

[radio1]

Re:

Have you tried to use ..
Me.RecordsetClone
This should give you all data current on the form.

Thanks for the suggestion, mmccarthy. I've been playing with it, hoping to be able to loop through the form's filtered recordset and add the records to a temporary table, and then report from that table (I don't know if this would have worked anyway -- I didn't get that far).
For me, the following TEST-ONLY code in my button click handler displays the # of records in the original recordset, not the user-filtered subset. Any thoughts on how to make this RecordSetClone reflect the filtered records only?
Also, the RS.Filter shows as simply the digit "0".

    If Me.FilterOn Then
        Dim n As Integer
        Dim RS As Recordset
        Set RS = Me.RecordsetClone
        RS.MoveLast
        n = RS.RecordCount
 
        MsgBox "# of records: " & CStr(n)
        MsgBox RS.Filter
    End If
 

Thanks for any input!

[OneBad347]

Has anyone figured out this problem?

I am having the same problem with the ADP (SQL Server Backend) and Filters.

[radio1]

Has anyone figured out this problem?

I am having the same problem with the ADP (SQL Server Backend) and Filters.

It's not a GOOD solution, but I have implemented something that will work in my application almost all of the time. I ended up doing a very-crude conversion of the WHERE clause from Jet's syntax to T-SQL's syntax.

This code (mostly) works for the data types I'm using. It should fail if the user enters a filter including the # or " characters (possible), or any of the other strings in the Replace function calls (much less likely). By "fail", I mean that the data in the report will not be the same as on the filtered form.

    Dim stDocName As String
    Dim strWhere As String
    strWhere = ""
    If Me.Dirty Then
        Me.Dirty = False
    End If
    If Me.FilterOn Then
        strWhere = Me.Filter
        strWhere = Replace(strWhere, "'", "''")         ' handle embedded apostrophes
        strWhere = Replace(strWhere, Chr(34), "'")      ' convert " to '
        strWhere = Replace(strWhere, "=-1)", "=1)")     ' form-filter checkboxes
        strWhere = Replace(strWhere, "=True", "=1")     ' field-filter checkboxes
        strWhere = Replace(strWhere, "=False", "=0")    ' field-filter checkboxes
        strWhere = Replace(strWhere, "#", "'")          ' dates
        strWhere = Replace(strWhere, " ALike ", " Like ")
        MsgBox Me.Filter + ", " + strWhere
    End If
 
    stDocName = "subjects"
    DoCmd.OpenReport stDocName, acPreview, , strWhere
 

If anyone has more complete conversion code PLEASE tell us about it.
ANY comment re this code is very welcome.
There REALLY must be a better solution to this.

Thanks,

-radio1

[ozinm]

Hi all,
just saw this thread while browsing.

Don't you want to use the me.serverfilter property instead of me.filter?
I'm pretty sure the ADP filter by form updates this value (but I'd need to double check to be sure).

[NeoPa]

While this thread is probably a little old now, any contributions, especially if they improve on the answer(s) are to be welcomed for later browsers. Feel free to check it out and post what you find :)

[painter]

Dim strWhereClause As String
        strWhereClause = Me.Filter
 
        'Convert filter syntax to SQL Where Clause syntax
        strWhereClause = Replace(strWhereClause, "Households.", "")
        strWhereClause = Replace(strWhereClause, """", "'")
        strWhereClause = Replace(strWhereClause, "True", "1")
        strWhereClause = Replace(strWhereClause, "#", "'")
        strWhereClause = Replace(strWhereClause, ")", "")
        strWhereClause = Replace(strWhereClause, "(", "")
        strWhereClause = Replace(strWhereClause, "-1", "1")
 
        DoCmd.OpenReport Me.cboHouseholdReports, acViewPreview, , strWhereClause

[Jim Doherty]

Hi all,
just saw this thread while browsing.

Don't you want to use the me.serverfilter property instead of me.filter?
I'm pretty sure the ADP filter by form updates this value (but I'd need to double check to be sure).

I use ADP files all the time never have problems with filtration and am subscribing to this but can't deal with it just now have a meeting will monitor it and come back.


Regards

Jim :)

[NeoPa]

Jim, I wouldn't worry unduly. Check out the date of post #12 ;)

PS. Although, any extra accurate information is always a bonus so go ahead if you'd like.

[Jim Doherty]

Jim, I wouldn't worry unduly. Check out the date of post #12 ;)

PS. Although, any extra accurate information is always a bonus so go ahead if you'd like.

Oh my goodness..I didn't check that, thanks Neo I might have entered into a long schpeel...... with the original poster never to be seen again. I'll leave it at that you have saved my fingers there

Jim :)

[NeoPa]

No probs.
This sort of thread resuscitation should probably be displayed with mummie's bandages as they did in the old films, but alas - probably too complicated and KUB365 has a busy schedule already ;)