Errors abound - requerying a subform's search combo box ...

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Hi!

I've got a form with three levels of subforms. In the
main, second, and third levels, I used the Wizard to
create some handy-dandy look up boxes for records in that
respective level (the ones that, when you click the
record, it jumps to it).

I put a macro that activates On Current on the SECOND
form, which requery's the SECOND level's box. I have an
identical query for the THIRD level's box that's in the
THIRD level form's On Current action as well. When moving
through records on the main form, both subform search
boxes update w/o a hitch.

However, when I search through records in my SECOND form,
the THIRD form gives me a Macro error that simply
says "Action Failed." I'm clueless as to why the second
level requery works, yet the third level does not.

Please realize I'm a hit & miss self-taught Access user,
so if you see a glaring problem w/something I've done and
have a recommendation for fixin' it (please don't make me
do VB b/c I don't know a darned thing about it), please
let me know!

I appreciate any help.
 
-----Original Message-----
Hi!

I've got a form with three levels of subforms. In the
main, second, and third levels, I used the Wizard to
create some handy-dandy look up boxes for records in that
respective level (the ones that, when you click the
record, it jumps to it).

I put a macro that activates On Current on the SECOND
form, which requery's the SECOND level's box. I have an
identical query for the THIRD level's box that's in the
THIRD level form's On Current action as well. When moving
through records on the main form, both subform search
boxes update w/o a hitch.

However, when I search through records in my SECOND form,
the THIRD form gives me a Macro error that simply
says "Action Failed." I'm clueless as to why the second
level requery works, yet the third level does not.

Please realize I'm a hit & miss self-taught Access user,
so if you see a glaring problem w/something I've done and
have a recommendation for fixin' it (please don't make me
do VB b/c I don't know a darned thing about it), please
let me know!

I appreciate any help.
.
I appreciate your comment about being self-taught and not
Click to expand...
underswtanding VB. However, you might consider learning a
little about it. It will make your questions easier to
understand for others, and you will have more skills at
your disposal.

When I was 5 years old, I didn't know how to ride a bike.
It looked so difficult! But all my friends were learning
how to ride, and I guess I just dove in at some point and
learned it! I hope you appreciate the analogy. Also, I
didn't think I could keep asking my friends to wheel me
around on a trycicle and I NEVER considered asking them to
not expect me to learn anything new! Best of Luck!
 
From some of my testing in other situations, I believe that you can only go
one level deep with a macro action -- meaning you can requery a subform, but
not a sub-subform (which is your "third level").

I'm assuming that you mean the "main form" by "first level", the "subform"
by the "second level", and the sub-subform (the subform inside the subform)
by "third level". If I'm misunderstanding, let me know.

Can you give a bit more info about what you're doing that triggers these
events? And what you're trying to accomplish? Likely there is another way to
get done what you seek to do.
 
Ken thanks for the quick reply...

well the main form's control source my main datasheet...
and of course the subform and sub-subform (or levels 2 and
3) are linked to a subdatasheet and sub-subdatasheet,
respectively.

Because I have multiple sub-datasheets on each level, I've
chosen to use the single-form view for my forms (vs. a
datasheet view, where I'm not even sure if it's an option
to swap between subdatasheets - at least I can't find it
if it is).

So, because I'm using the single-sheet view for all of
these forms, I want to include a self-updating combo-box
in each form that lists all records available. For
instance:

+Table A -> Form A
+Table B -> Form B
+Table C -> Form C

Say Table A has primary key values of 1, 2, and 3. The
values in Table B that belong to record 2 in Table A have
primary keys of 50 and 51. And say the values in Table C
that belong to record 50 in Table B have primary keys of
76 and 77.

So... when I'm in Form A and I select primary key 2, I
should see records 50 and 51 in Form B, and since let's
say I'm sorted by primary key, and so 50 is the default
selection in Form B, primary keys of 76 and 77 should show
in Form C.

This is the easiest way I can think of for the user to
scroll through available records, having them all laid out
for them in truncated form in a combo box on each level.

Thanks again for the quick response, and I appreciate you
getting through my confusing little message here!
 
If I'm understanding you correctly, you want the ability to choose a
specific record in the main form and then to display in the subform the
related records, and to display in a subsubform the records related to the
subform's related records?

If this is correct, then I think your setup is unnecessarily complicated.
You can use the "Link Master Fields" and "Link Child Fields" properties of a
subform to "tie together" the subform and the main form by specifying which
field in the main form (Master Fields) is the linking key field to the
subform (Child Fields), such that, as you move from one record to another on
the main form, the subform automatically moves to the related records. This
also can be done between the subform and the subsubform. No need for a combo
box on each level at all.

I'm not clear as to why you *need* to use the single form view for the main
form, the subform, and the subsubform, though. Usually, the situation that
you describe would use a continuous forms view. As you select a record in
the main form, the related records all display in the subform, etc.

Am I completely off the track here, or does this seem workable for your
setup?
 
Ken thanks for replying again!

Well yes - the master and child fields are linked, and
that is what keeps the form and subforms in sync w/each
other. So yes - there's no problem with that.

The reason for the combo box (which only operates
correctly BECAUSE the master/child relationship already
exists) is that I like to be able to show the user all
records available... the reason I need a single form is
that continuous forms don't support multiple subforms for
a form (for example:

Form A
+Form B
+Form C

I'm showing both Form B and Form C as subforms of Form
A... this is not supported in a continuous forms
environment - only one single subform can be supported by
the continuous forms option - otherwise this would seem
most convenient).

So, to work around this, I have the Combo Boxes to display
all records available in each subform... that way, when in
the sub-subform, which is invoices, the user will have a
list on the left side of that form that lists the invoices
already billed, which is great to go back to for
reference. It's all a matter of making sure the
information is there and ready for the user.

Thanks again Ken. btw I think your note that Macro's only
work up to one subform deep is true... just for the hell
of it I opened up my subform on its own, which of course
the sub-subform nested in it (the one I'm having problems
with). When I did this (rather than being nested in the
main form), the sub-subform's combo box updated w/o a
hitch! So I think your hypothesis that macro's only work
one subform deep could in fact be the problem! If there's
a work-around for the one-subform-deep rule, that might be
my way out!

Thanks again.
 
Ken I'm sorry I mis-spoke in my last reply - continuous
forms aren't limited to one subform as I said in the last
reply, but in fact don't support subforms at all!
Obviously this is not a viable option for me, so the
single form is what I'm using.
 
Ken - thanks for cluing me into the "trick." I think if
that's available I'm going to check that out! Thanks for
the continued support.
 
Back
Top