Well, it's the name game, Hmm.... It now works, here is what i did, no
more
errors like that!!!
I deleted the SubForm from the Master and saved the result.
I opened the subform and added an on click,
=Forms!AdvancementMaster!txtRank
I then saved the subform. Then i opened the master, and recreated the
form
with the name AdvSubCont with the link on Participant ID, added your an
the
on current you suggested: Me![AdvSubCont]!AdvSel.ReQuery
Now here is what i found, when you open the Master form:
If a White space appears where the form should be you can get all the
info
you need to make it work. However, some times it shows the actual
subform
with the fields, in that mode you can get the control name you just see
"Advancmenet Subform" and so that lead to the problem we were haveing
the
whole time.
You were right, you nailed it the first time, Get the control name not
the
form name!
Thank you so much for your help, i and the Cub Scouts thank you!
Dick
:
OK we're close I think.
Comments inline...
--
Ken Snell
<MS ACCESS MVP>
"(e-mail address removed)" <
[email protected]>
wrote
in
message Hi Ken,
Once again i am in your debt. By the way I fully agreee with your
philosophy "you always learn better when you get your hand dirty".
1. there is no combo box on the AdvancmentMaster form, is just a
control
which contains the scouts current rank. Each time you select a new
boy
that
control, "rank" and now control name = "cboRank" changes to reflect
his
rank.
Just as an FYI, The Participant Table (source of scouts rank, key is
an
auto
number field called "Participant ID"), the rest of teh stuff in that
table
is
name, address, age, DOB etc.
(answer to 1.)
So this control that is bound to the field named "Rank" is a
textbox. It won't make any difference in the overall scheme, but to
have
a
consistent naming convention, change the textbox name from "cboRank"
to
"txtRank". Then modify the SQL statement in the Row Source of the
AdvSel
combo box in the subform to this:
SELECT Advancement_code.[Advancement Code],
Advancement_code.[Advancement Description],
Rank.[Rank description], Rank.Rank FROM Rank
INNER JOIN Advancement_code ON
Rank.Rank=Advancement_code.[Current Rank]
WHERE (((Rank.Rank) Like Forms!AdvancementMaster!txtRank))
ORDER BY Rank.[Rank description];
2. The Sub Form is "Advancement subform" (as you sermized) with the
fields
"Participant ID" (scouts unique ID number), Record Number (Auto
number
in
the
Advancement Table), "Advancmenent code" (a code representing the
test
the
boy has accomplished. The "Advancement code" is stored in in a table
called
"Advancement_code", along with "Advancement description" and
"Current
Rank"
(this is the rank to which the advancmement applies).
(answer to 2.)
Advancement subform appears to be the name of the form that is
being
used as the subform. However, for the Requery code to work, we also
need
to
know the name of the subform control (the control, on the main form,
that
holds the subform), as that is the only name that we can use to "get
to"
the
controls on that subform. As I noted in my earlier reply, you must get
the
name of this subform control by opening the main form in design view,
click
on the very top of the main form's control that holds the subform (do
not
click inside this control!), open the Properties window, click on
Other
tab,
and then read the name in the Name box. That is the name that we need
to
use
in the Requery step.
3. The Combo box on the "Advancement Subform" is used to select
codes
from
the "Advancement_code" table which are appropriate for the rank of
the
Cub
Scout. After selection the Advancment record is saved and the form
displays
another row of information.
4. Two side issues -- which should have nothing to do with the
problem,
there is anoth small table which hangs of the Advancment_code table
called
rank, it has a the discription of each rank and is the source of the
Rank.[Rank Description] in the query. The "advancment Subform" also
displays
all current records for the selected Cub Scout, so you can view and
edit
existing records for each scout.
So here is what happed when i made the changes you suggesed.
A. The query "AdvSel" on the "Advancement sub" form works fine,
but
the
variable Rank (in the query) does not clear when you select another
"Participant ID".
(answer to A.)
I am not understanding your terminology here. There is no
variable
in the SQL statement query for the AdvSel combo box's Row Source.
There
is a
Rank table, and there is a Rank field in the Rank table. What do you
mean
that the "variable Rank (in the query) does not clear when you select
another "Participant ID""? Selecting another Participant ID is done in
the
main form, correct? Or is it being done somewhere else, such as in the
subform?
B. This results a Run-time error '2465" Cub Scout Day Camp can't
find
field "advancement Subform" referred to in your expression.
Private Sub Form_Current()
Me![advancment Subform]!AdvSel.Requery
End Sub
(answer to B.)
See my answer to your 2nd question above. You must replace
"advancement Subform" in this Requery code step with the actual name
of
the
subform control.
:
OK - I think I have a working picture of your form setup in my
mind. I
am
assuming that the name of the subform control is [advancement
Subform].
This
would be the name of the subform control if you open the main form
in
design
view, click on the very top edge of the subform control, open the
Properties
window, and click on the Other tab and read the Name value. If it
has
a
different name, change the code/info below to the correct name.
Do these steps:
(1) Delete the event procedure and code from your AdvSel control's
GotFocus
event.
(2) In some situations, naming a control the same name as the name
that
is
in its Control Source is ok. In this case, I would change the name
of
your
"Rank" combo box on the main form to cboRank, as a field and combo
box
have
different properties and we can better avoid confusion if we name
the
combo
box something else.
(3) Change the Row Source SQL statement on the subform's combo box
to
this:
SELECT Advancement_code.[Advancement Code],
Advancement_code.[Advancement Description],
Rank.[Rank description], Rank.Rank FROM Rank
INNER JOIN Advancement_code ON
Rank.Rank=Advancement_code.[Current Rank]
WHERE (((Rank.Rank) Like Forms!AdvancementMaster!cboRank))
ORDER BY Rank.[Rank description];
(4) Use this code on the AfterUpdate event of the cboRank combo box
(this
will cause the combo box on the subform to requery whenever you
make a
change to the combo box named Rank):
Private Sub cboRank_AfterUpdate()
Me![advancment Subform]!AdvSel.Requery
End Sub
(5) Use this code on the Current event of the main form(this will
cause
the
combo box on the subform to requery whenever you move from one
record
to
another in the main form):
Private Sub Form_Current()
Me![advancment Subform]!AdvSel.Requery
End Sub
This should do what you seek.
By the way, I'm not ignoring your comments regarding your
willingness
to
email me a copy of your database. That usually is not necessary for
most
newsgroup questions, and if we get to the point where I feel I must
see
the
database to figure out what is happening, I'll indicate that I'd
like
to
see
it. By staying within the newsgroup to explain/suggest things helps
people
learn how to explain what they're doing, and for others (me
included)
to
learn how to "see" what a person is explaining. I am a firm
believer
that
people learning ACCESS (or becoming more proficient at it) need to
understand what they have in a form or report, and how it is/is not
working,
and to be able to explain that in a meaningful way, in order to
gain
significant "hands-on" capability with the software. Having someone
"edit" a
database and return it with the fixes may be a final outcome, but
it
usually
doesn't help both parties progress in their abilities and
understanding.
(I
say this as a person completely self-taught in ACCESS -- a person
who
eschewed the wizards for most features after the first few weeks
because
it
was too difficult to figure out how they created what they created
and
how
they worked.)
Let me know if the above changes are what you seek! < g >
