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.

Expand|Select|Wrap|Line Numbers

 
        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

Expand|Select|Wrap|Line Numbers

  
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.

Expand|Select|Wrap|Line Numbers

 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".

Expand|Select|Wrap|Line Numbers

 
    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.

Expand|Select|Wrap|Line Numbers

 
    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

Expand|Select|Wrap|Line Numbers

 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 ;)

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

Post your reply

Similar topics