Recordset as Criteria for Form Filter

  • Thread starter Thread starter Doctor
  • Start date Start date
D

Doctor

Is it possible for me to use the ID of each record in a DAO recordset to be
used to filter which records open in a form? If so how?

I have a form that finds records based on the users criteria. I would like
to "capture" all of the ClientID's that appear in the list and then open the
Client main form filtered.

If there is another way to accomplish this, I would love to hear about it.

Also, it is currently set up that the user can double click the client and
it opens up only that client, but just to be sure I'm clear, I want to open
all the records in the Find form up in the Client form.

Thanks a heap.
 
Doctor said:
Is it possible for me to use the ID of each record in a DAO recordset to
be
used to filter which records open in a form? If so how?

I have a form that finds records based on the users criteria. I would like
to "capture" all of the ClientID's that appear in the list and then open
the
Client main form filtered.

If there is another way to accomplish this, I would love to hear about it.

Also, it is currently set up that the user can double click the client and
it opens up only that client, but just to be sure I'm clear, I want to
open
all the records in the Find form up in the Client form.

Thanks a heap.
Click to expand...

How long do you anticipate the list being? If it's potentially quite long,
it may be simplest (and would likely be most efficient) to just use the same
criteria string that was originally specified in opening that recordset, and
apply that to the form as a filter when you open it. For example,

Dim strFilter As String

strFilter = "'<... some criteria ...>"

DoCmd.OpenForm "frmClient", WhereCondition:=strFilter

If all you have is the recordset containing the filtered IDs, though, and
you can't get the filter string from it (maybe by parsing out the
recordset's Name property), AND the list is fairly short, then you might run
through the recordset and build a filter string:

With rsSelected ' your recordset of IDs

Do Until .EOF
strFilter = strFilter & "," & !ClientID
.MoveNext
Loop
.Close ' maybe

End With

If Len(strFilter) > 0 Then
strFilter = "ClientID In (" & Mid(strFilter, 2) & ")"
End If

DoCmd.OpenForm "frmClient", WhereCondition:=strFilter

Something along those lines ought to work.
 
Thanks so much Dirk. Worked great. I went for your second option. I've done
your first option before, just wouldn't work this time. The search form was
based on a query that included fields from six tables. So your second thought
went exactly according to plan.

Nice.
 
Back
Top