applying filter from form on report

[k]

Hi
I have a form which the user can apply filters, and push a button to
preview a report. I cannot use me.filter=forms!myform.filter, because
some of the filtered fields are not present in the report. What I would
like is to obtain the ID field from the filtered form, and show only
those ID's in the report.

K

[MGFoster]

k wrote:

Hi
I have a form which the user can apply filters, and push a button to
preview a report. I cannot use me.filter=forms!myform.filter, because
some of the filtered fields are not present in the report. What I would
like is to obtain the ID field from the filtered form, and show only
those ID's in the report.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to get a comma delimited list of the IDs from the form's
Recordset and pass them to the report in the WhereCondition parameter.
E.g. (this assumes you're using DAO):

' get the list of IDs from the form's Recordset
Dim strIDs As String
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Do While Not rs.EOF
strIDs = strIDs & rs!ID & ","
rs.MoveNext
Loop
' get rid of trailing comma
If Len(strIDs) > 0 Then strIDs = Left$(strIDs, Len(strIDs)-1)
' Open report w/ IDs
DoCmd.OpenReport "myReport", WhereCondition:="ID In (" & strIDs & ")"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi9U0YechKqOuFEgEQKJswCgpGUlG9IFSqnhTjsFSena94 eXhtgAoPjm
XO8PhqGPV2bk2ZKqkHmRQbpS
=jTnu
-----END PGP SIGNATURE-----

[k]

Thanks, that does the trick.

But only the first time I push my button, the second time I push the
button. The variable strIDs is empty, and the condition becomes "ID in
()"

If I change the filter, the button works again, once.

K

[MGFoster]

k wrote:

Thanks, that does the trick.

But only the first time I push my button, the second time I push the
button. The variable strIDs is empty, and the condition becomes "ID in
()"

If I change the filter, the button works again, once.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In place of this:

Set rs = Me.RecordsetClone
Do While Not rs.EOF

put this:

Set rs = Me.RecordsetClone
on error resume next
rs.movefirst
on error goto err_handler
Do While Not rs.EOF

Put an error handler routine in your Sub to handle the error of "no such
record" when MoveFirst tries to move to a non-existant record (when the
recordset is empty).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi99n4echKqOuFEgEQJDigCePQ5XiPqQuik/TwGEinKyicuLTJ8An0sl
nHtPTx/bD0Y1rJoip4pjlxZK
=QJzx
-----END PGP SIGNATURE-----

[k]

Great! Thanks!

K

[jimfortune]

k wrote:

Great! Thanks!

K

Alternatively you could replace

DoCmd.OpenReport "myReport", WhereCondition:="ID In (" & strIDs & ")"

in MGFoster's first solution with

If Len(strIDs) > 0 Then
DoCmd.OpenReport "myReport", WhereCondition:="ID In (" & strIDs & ")"
Else
MsgBox("No such record.")
End If

and let the On Error's deal with sterner stuff :-). Note: If you do
this, it would also be good to assign strIDs = "" before the Do While
loop so that strIDs will be assigned a string whether or not the loop
executes.

James A. Fortune