Thanks very much for the reply, Tom.
Apparently this Access/VBA newbie (that's me) quite underestimated the
complexity of this task. I had assumed that generating a report on a
current recordset would be a simple task in Access, perhaps taking at
most a few minutes to implement.
I'll look into both of your suggestions.
The "Replace" idea I had already implemented for the string and
boolean/bit fields, but I recognized that I'll probably run into
problems with strings containing apostrophes or quotation marks, and
then there are possibly other filter types to investigate like
less-than/greater-than (which Access seems to support when users are
field-filtering), and probably other constructs that I'm not even aware
of yet (again, I am fairly new at this!). Too bad Access doesn't seem
to make such a conversion function available to VBA code -- or maybe it
does and we just haven't found it.
I understand your alternative idea at a high level, but I have much
learning to do before I could implement such a technique. I won't
bother you by asking for more technical details on your idea, as I can
research these myself.
I would like to ask you these higher-level questions about your
"temporary table" idea though: would Access create this table natively
in Jet format on the local machine, or would it create it on the
SQLServer back-end? If on the back-end, would this be incredibly slow
(one sql insert per record)? Also, if on the back-end, could the
temp-table be private to the current user (I need to support
multiple-users).
Thanks again for taking the time to respond.
-melnhed
Tom van Stiphout <no.spam.tom7744@cox.netwrote in
news:du34k259mjo9kr5tr5sn2uonl9r3qg9nma@4ax.com:
Quote:
On 27 Oct 2006 04:04:25 GMT, melnhed <x@y.zwrote:
>
It's still Access applying the filter, that's why it's in that format.
But it seems you already know what to replace, so why don't you use
the Replace function to do so.
>
One alternative is to iterate over the RecordsetClone, collect all
primary key values, save them to a "temporary" table (say tblTempPK),
and use that table to innerjoin with:
select * from Customers
inner join tblTempPK on Customers.CustomerID = tblTempPK.PrimaryKey
This would restrict all customers to the selected ones.
Great trick for when a really complex filter has been applied using
FilterByForm.
>
-Tom.
>
>
Quote:
>>---Report the current filtered records from a Form---
>>
>>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 reportbutton 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,
>>
>>melnhed
>
