Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-05-2004, 08:12 AM   #1
crhodus
Registered User
 
Join Date: Mar 2001
Posts: 257
Thanks: 0
Thanked 0 Times in 0 Posts
crhodus
Pass Value to Subform

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

crhodus is offline   Reply With Quote
Old 04-05-2004, 08:23 AM   #2
dcx693
Registered User
 
dcx693's Avatar
 
Join Date: Apr 2003
Location: Brooklyn
Posts: 3,265
Thanks: 0
Thanked 2 Times in 2 Posts
dcx693 is on a distinguished road
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?
dcx693 is offline   Reply With Quote
Old 04-05-2004, 08:39 AM   #3
crhodus
Registered User
 
Join Date: Mar 2001
Posts: 257
Thanks: 0
Thanked 0 Times in 0 Posts
crhodus
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.

crhodus is offline   Reply With Quote
Old 04-05-2004, 10:24 AM   #4
dcx693
Registered User
 
dcx693's Avatar
 
Join Date: Apr 2003
Location: Brooklyn
Posts: 3,265
Thanks: 0
Thanked 2 Times in 2 Posts
dcx693 is on a distinguished road
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.
dcx693 is offline   Reply With Quote
Old 04-05-2004, 11:08 AM   #5
crhodus
Registered User
 
Join Date: Mar 2001
Posts: 257
Thanks: 0
Thanked 0 Times in 0 Posts
crhodus
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??
crhodus is offline   Reply With Quote
Old 04-05-2004, 12:40 PM   #6
crhodus
Registered User
 
Join Date: Mar 2001
Posts: 257
Thanks: 0
Thanked 0 Times in 0 Posts
crhodus
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!
crhodus is offline   Reply With Quote
Old 04-05-2004, 01:07 PM   #7
dcx693
Registered User
 
dcx693's Avatar
 
Join Date: Apr 2003
Location: Brooklyn
Posts: 3,265
Thanks: 0
Thanked 2 Times in 2 Posts
dcx693 is on a distinguished road
Yes, that is strange. I don't see why it didn't work the way you originally had it. Oh well.


dcx693 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 07:03 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World