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