How to pass parameters to a subform

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
 
J

Jeff Boyce

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>
 
J

Jerry Crosby

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
 
J

Jeff Boyce

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>
 
J

Jerry Crosby

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
 
J

Jeff Boyce

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>
 
J

Jerry Crosby

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
 
J

Jeff Boyce

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>
 
Top