subForms, buttons, and doCmd?

  • Thread starter Thread starter Janet
  • Start date Start date
J

Janet

I have three forms. FormA is a list of salads with two
buttons that open the same form (FormB). FormB contains
the ingredients for the various salads. FormB has a
datasheet subform (FormBsub) allowing you to add or
delete ingredients.

FormA: tblSalad, saladID, saladName
child/master link of saladID=saladID between FormA/B
FormB: saladID, ingredientID
FormBsub: ingredientID, ingredientTypeID

What I'd like to do is be able to click Button A on FormA
and have FormB appear but only have items listed in
FormBsub with an ingredientTypeID=2 OR if I click Button
B on FormA, have items listed in FormBsub where
ingredientTypeID<>2.

I've been using the doCmd.OpenForm with where clause for
immediate forms, but can you use it for the buttons with
subforms? Maybe set the filter using code on the
subform? Any Ideas?

Many thanks,
Janet
 
Janet said:
I have three forms. FormA is a list of salads with two
buttons that open the same form (FormB). FormB contains
the ingredients for the various salads. FormB has a
datasheet subform (FormBsub) allowing you to add or
delete ingredients.

FormA: tblSalad, saladID, saladName
child/master link of saladID=saladID between FormA/B
FormB: saladID, ingredientID
FormBsub: ingredientID, ingredientTypeID

What I'd like to do is be able to click Button A on FormA
and have FormB appear but only have items listed in
FormBsub with an ingredientTypeID=2 OR if I click Button
B on FormA, have items listed in FormBsub where
ingredientTypeID<>2.

I've been using the doCmd.OpenForm with where clause for
immediate forms, but can you use it for the buttons with
subforms? Maybe set the filter using code on the
subform?
Click to expand...


No, you can not use OpenForm with subreports. Yes, you can
(usually?) use the subform's Filter property:

Me.subform.Form.Filter = "TypeId = 2"
or
Me.subform.Form.Filter = "TypeId <> 2"

You'll have to make sure the subform's FilterOn property is
also set:

Me.subform.Form.FilterOn = True
 
Marsh,
Many thanks, I'll keep that in my file. BUT - I screwed
up in my description. What I actually needed was to
limit to =2 or <>2 is a combo box control on the FormBsub
from the buttons. (versus what I said/typed.)

Any good ones in that direction? I have tried a few
things, but to no avail.

Janet
 
Janet said:
Marsh,
Many thanks, I'll keep that in my file. BUT - I screwed
up in my description. What I actually needed was to
limit to =2 or <>2 is a combo box control on the FormBsub
from the buttons. (versus what I said/typed.)
Click to expand...

It hink you lost me here. I think you're now saying that
FormBsub is a subform on FormA, the buttons are in FormA and
the combo box is in FormB. If that's right, then set the
combo box's RowSource property to the desired SQL statement
this way:

Me.FormBsub.Form.thecombo.RowSource = _
"SELECT somefields FROM thetable " _
"WHERE TypeId = 2"
--
Marsh
MVP [MS Access]

 
Back
Top