How to pass parameters to a subform

  • Thread starter Thread starter Jerry Crosby
  • Start date Start date
J

Jerry Crosby

Situation: Form with a subform. Two command buttons above the subform, set
to change the source object of the subform "on click"
That much works. Now I need to tweak it.
On each of the buttons, I need to add some parameters to the subform it
activates. I want one to only show records whose [ArrivalDate] field is
greater than today. The other button needs to open the same subform, but
have it ready for a new record to be entered (i.e., a blank form).

Here's what I have for the on click event right now that merely sets the
source object:

Me![frmReservationInfo].SourceObject = "frmReservationInfo"
Me![frmReservationInfo].LinkChildFields = "GuestID"
Me![frmReservationInfo].LinkMasterFields = "GuestID"

I suspect I need to add some "WHERE" language, but I don't know how or
where. I know I could put it in the actual subform, but since I'm using the
same subform in both instances, I want to set the parameters at the button
level.

Hope I haven't thoroughly confused you. Can someone help?

Jerry
 
Jerry

If you are using a standard main form/subform construction, you don't have
to "pass" any parameters. Setting up the subform on the main form involves
indicating which fields are the matching (parent-to-child) fields.

Regards

Jeff Boyce
<Access MVP>
 
Well, I am using the standard main form/subform construction. You'll see
from the snippet of code I showed, the forms are linked by the GuestID
field.

But there is a one-to-many relationship between the two tables, meaning the
normal construction will show ALL the records in the subform where the
GuestID equals the GuestID in the main form.

But I want to show (for example) records in the subform where the
[ArrivalDate] field is greater than today's date AND (of course) the Guest
ID equals the GuestID in the main form.

More clear? or worse?

Jerry
 
Jerry

One way to approach this is to build a query as a source for the subform
records. Include a parameter in the query that has [ArrivalDate] > Date(),
and include the GuestID.

Now, if I recall correctly, the main form/subform will find records in the
subform that match on GuestID AND (because of the query) have an
[ArrivalDate] > Date().

Regards

Jeff Boyce
<Access MVP>
 
I appreciate your help on a holiday, Jeff.

I'm not sure I'm tracking with you yet. If I am "to build a query as a
source for the subform" are you meaning to build that query on the subform
itself, or as an entirely separate "stand along" query?

If the former, wouldn't I would need to have two copies of the form, since I
want to view it in two different ways (as a blank for to add a new record,
and as a form with data, filtered on the ArrivalDate field)?

If the latter, I'm not sure how/where in invoke the query since I'm using a
command button's on click event to set the source of the subform.

I'm slow, and a novice, but I'll get it eventually!

Jerry
 
Jerry

If you were only going to populate your subform (I guess it wouldn't be a
"sub"form then), you could build a query. To what dataset do you bind your
subform now? (I'm guessing you bind it directly to a table -- I'm proposing
to bind it to a query instead.)

In the query you build to "fill" your subform, include the date-related
criterion.

Now use the query as the source (i.e., "bind it") for the subform.

Now use the main form/subform construction ... unless I'm losing it (?!?),
any records showing in the subform will have already been filtered/selected
based on the date-related criterion. And your main form/subform
construction will limit the subform to displaying only the appropriate
GuestID records.

Or am I still missing something...

Regards

Jeff Boyce
<Access MVP>
 
Mornin', Jeff,

Don't worry, you're not losing it, but you may have missed something in my
description of my situation.

I have created a form, bound to a table, that I want to use as a subform.
However, sometimes I want to "call up," if you will, the subform showing
only those records in the table whose StartDate field meets certain
paremeters.

Other times I want to "call up" that same subform to where it is blank,
ready for a new record to be entered.

(In both instances, the tables are linked on the GuestID field.)

I understand your idea of binding the subform to a query, but that will only
solve one-half of my problem.

Maybe I'm the one losing it! It would probably be simplest to merely create
two identical looking forms, but bound to different queries/tables.

If this doesn't help clear anything up, I'll go ahead and do that.

Appreciate your patience.

Jerry
 
Jerry

I think you'll find you have much more control over your forms (?subforms?)
and what they display if you create a query and bind the form to the query
instead of directly to the table.

Try creating the 'date-criterion' query I suggested earlier in this thread.
Run the query. Try adding a new record to the query -- you may need to
include the underlying table's primary key column to make the query
updateable.

I suspect you'll only need one query and one form to do what you're
describing.

Regards

Jeff Boyce
<Access MVP>
 
Back
Top