Change Form Source Recordset Dynamically

[charles.kendricks@charter.net]

I have a form that is used to display patient records. It's default
Recordset is set to a query which shows only current clients. I want
to use an option box on the form to force the form to use the client
table (tblClients) as it's source Recordset, which would display ALL
clients (past and present), not just current clients. How do I
accomplish this?

[Salad]

Re: Change Form Source Recordset Dynamically

charles.kendric ks@charter.net wrote:

I have a form that is used to display patient records. It's default
Recordset is set to a query which shows only current clients. I want
to use an option box on the form to force the form to use the client
table (tblClients) as it's source Recordset, which would display ALL
clients (past and present), not just current clients. How do I
accomplish this?

I might create an OptionGroup with 3 checkbox options; Current,
Archived, All in the form's FormHeader band. Give it a name like
FramePatients. Set the default to 1 which would be current patients.

Your form's recoursource should have no filter; a simple table or query
is all that is needed.

Then create a subroutine similar to the following.
Sub SetFilter()
Dim strF As String

Select Case Me.FramePatient s
Case 1
'use your active status flag
strF = "ActivePati ent = True"
Case 2
strF = "ActivePati ent = False"
End Select

'since 3 is All, StrF will be ""
Me.Filter = strF
Me.FilterOn = (strF "")
End Sub

Now in the form's OnOpen event and the OnClick event of the Option group
enter
SetFilter
You'd open the form like this
Docmd.Openform "FormName"
without a filtering argument.

Pretty Wahine

http://www.youtube.com/watch?v=h9Wx63W_zBA