Change the record source for a form, based on a value in text box

[Guest]

Hello.
I am trying to improve the design of my database. At the moment I have a
form that displays information for a clinic. This form has buttons that
display information for other clinics (the information fields are the same;
patient name, dob, etc) but the records are different. At the moment I
copied the form 8 times for 8 clinics. Changed the record source of each of
the forms to a different query, and put buttons on each form that loads up
whichever form is needed for a selected clinic.

Id like to use one form, and change the record source for that form based on
a value inserted into a text box (via a clinic on the appropriate button).
So if a users clicks "Clinic 2" button, clinic 2 data is displayed in the
form, if user clicks on "Clinic 3" button clinic 3 data is displayed in the
same form. In other words the record source property of the form changes
based on either a button press, or the value of a text box on the form.

I tried the code below in the event on current property of the form but it
did not work.

DoCmd.OpenForm "FrmClinic1"
If Me!Text37.Value = "clinic 2" Then
FrmClinic1.RecordSource.Value = "QryFeedClinic2"
Else
FrmClinic1.RecordSource.Value = "QryFeedClinic1"
End If


Suggestions are most welcome. Thank you Tim

 

[Guest]

Why are you using two record sources, if both queries are based on the same
table but the filter is different, you can bound the form to the table, and
then send a filter to the form using the WhereCondition on the form open
command line

Dim MyCondition As String
If Me!Text37 = "clinic 2" Then
MyCondition = "[Clinic] =2"
else
MyCondition = "[Clinic] =1"
End If
DoCmd.OpenForm "FrmClinic1" , , , MyCondition
====================================
In any case, when you want to assign the value to the RecordSource, try

Forms!FrmClinic1.RecordSource= "QryFeedClinic2"