Pass Value to Subform (1 Viewer)

How can I pass a value to a subForm?

I have a subForm (subFollowUp) on my main form (frmTickler). I would like to be able to pass a value to my subform so that only certain records are displayed in the Subform.

The default Record Source for subFollowUp is "SELECT * FROM Projects;".
I'd like to be able to have the Record Source be something like "SELECT * FROM Projects WHERE PManager = 'JOHN DOE';" , depending on what the user selects from the previous form.

I know if this was a regular form I could do something like this:

Dim strSQL as String
Dim stDocName as String

stDocname = "frmMyFomr"
strSQL = "PManager = 'JOHN DOE' "
DoCmd.OpenForm stDocName, , , strSQL

But how can I achieve this type of result when using subforms?

Thanks,
crhodus

Why not link your subform to your mainform using the Link Child/Master Fields properties for the subform and eliminate the need to write any code?

I tried doing what you said, but I'm receiving a "Cannot build a link between unbound forms". What I've done is create a main form (frmTickler). On frmTickler, I have 2 subforms (subFollowUp and subNonFollowUp).
Besides the 2 subfomrs, frmTickler only has one other thing on it - a Close button.

I have a global variable (strTicklerPM) that contains all the Project Manager names that the user selected from a previous form. I'd like to use the Proj. Mgr. names that the user selected to pull different records into both the subfoms.

Hmm...I must confess that I've not tried linking unbound forms, but no biggie. To synch the frmtickler main form to the subforms, put some code into the main form's On Current event (the code will execute every time you open the form and when you move from one record to another on the main form). The code can either requery or filter the subforms.

If you want to requery the subforms:
Me.subFollowUp.Form.Recordsource="SELECT * FROM Projects WHERE PManager = '" & Me.txtPManager & "';"

You could also pass the value of Me.txtPManager to the subform, set up a filter for the subform's recordsource, then apply the filter. Requerying is simpler.

Thanks for your help. Instead of using the On Current event, I added code to the On Open event. But I've run into a problem.

I'm receiving the following error:
Run-Time Error '3705'

Syntax error (missing operator) in query expression '(( [project_mgr]='Alan Ray' OR [project_mgr]='Joe Roberts' ) ORDER BY Project.project_mgr, Project.Followup, project.project_name;'.

This is the code that I'm using:
Me.subFollowUp.Form.RecordSource = "Select * from Project Where (" & TicklerPM & " ORDER BY Project.project_mgr, Project.FollowUp, Project.project_name;"

When debugging, I created a varialbe called strTest and gave it the following statement:
strTest = "Select * from Project Where (" & TicklerPM & " ORDER BY Project.project_mgr, Project.FollowUp, Project.project_name;"

Then from my watchwindow, I copied the value that strTest contained. I then went back into my code and copied the value of strTest to the RecordSource like this:
Me.subFollowUp.Form.RecordSource = "Select * from Project WHERE ( [project_mgr]= 'Alan Ray' OR [project_mgr]= 'Joe Roberts' ) ORDER BY Project.project_mgr, Project.FollowUp, Project.project_name;"

This code ran correctly and gave me the desired results in the subform. Do I have something wrong with my quotes in the first Me.subFollowUp.Form.RecordSource statment I listed??

I finally got the statement to work.

This code would not work:

Me.subFollowUp.Form.RecordSource = "Select * from Project WHERE ( [project_mgr]= 'Alan Ray' OR [project_mgr]= 'Joe Roberts' ) ORDER BY Project.project_mgr, Project.FollowUp, Project.project_name;"

But if i do this, it work:

dim tmpStatement as string

tmpStatement = "Select * from Project WHERE ( [project_mgr]= 'Alan Ray' OR [project_mgr]= 'Joe Roberts' ) ORDER BY Project.project_mgr, Project.FollowUp, Project.project_name;"

Me.subFollowUp.Form.RecordSource = tmpStatement

I don't understand why it would not take the first statement because all I did was copy and past the select statement to tmpStatement. Anyway, it works now.

Thanks for your help!

Yes, that is strange. I don't see why it didn't work the way you originally had it. Oh well.