Combo box will not select values

[Guest]

Hi All,

I have a combo box which uses the fowlling code.

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 [Ranksel])) ORDER BY Rank.[Rank description];

I would like to feed the variable RankSel with the following:

Private Sub AdvSel_GotFocus()
Dim RenkSel
RankSel = Forms!AdvancementMaster!Rank
MsgBox "rank sel = " & RankSel < I get the correct value here!
End Sub

But when the query Asks for a value for RankSel, why dosen't teh value i get
from VB fill this field in the Query?

Help!

Dick

 

[Ken Snell [MVP]]

Queries cannot see VBA variables. Either have the query read the value from
the combo box on the form, or write a public function that returns the value
of the variable to the query, and use that function in the query. I
recommend the first method.

 

[Guest]

Hi Ken,

I did what you suggested and it worked, however, the query now retains that
value until i exit and reenter the form. Is there a way to clear that value
and requery for the next selection.

Here is my current code:
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)=Forms!AdvancementMaster!Rank)) ORDER BY Rank.[Rank
description];


I also added VB statement on lossfocus

Private Sub AdvSel_LostFocus()
DoCmd.ReQuery
End Sub


Perhaps you could help me with this.

Many, many thanks,

Dick

Queries cannot see VBA variables. Either have the query read the value from
the combo box on the form, or write a public function that returns the value
of the variable to the query, and use that function in the query. I
recommend the first method.

--

Ken Snell
<MS ACCESS MVP>

Hi All,

I have a combo box which uses the fowlling code.

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 [Ranksel])) ORDER BY Rank.[Rank description];

I would like to feed the variable RankSel with the following:

Private Sub AdvSel_GotFocus()
Dim RenkSel
RankSel = Forms!AdvancementMaster!Rank
MsgBox "rank sel = " & RankSel < I get the correct value here!
End Sub

But when the query Asks for a value for RankSel, why dosen't teh value i
get
from VB fill this field in the Query?

Help!

Dick

 

[Ken Snell [MVP]]

The query will continue to return the same result so long as the value in
the Rank control on the form AdvancementMaster stays the same. So, you
probably should set the value of Rank control to Null in your requery code:

Private Sub AdvSel_LostFocus()
Me!Rank.Value = Null
DoCmd.ReQuery
End Sub


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I did what you suggested and it worked, however, the query now retains
that
value until i exit and reenter the form. Is there a way to clear that
value
and requery for the next selection.

Here is my current code:
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)=Forms!AdvancementMaster!Rank)) ORDER BY Rank.[Rank
description];


I also added VB statement on lossfocus

Private Sub AdvSel_LostFocus()
DoCmd.ReQuery
End Sub


Perhaps you could help me with this.

Many, many thanks,

Dick

Queries cannot see VBA variables. Either have the query read the value
from
the combo box on the form, or write a public function that returns the
value
of the variable to the query, and use that function in the query. I
recommend the first method.

--

Ken Snell
<MS ACCESS MVP>

in
message news:5B43E24D-8ABC-46B5-8A82-8328B08A7ACB@microsoft.com...

Hi All,

I have a combo box which uses the fowlling code.

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 [Ranksel])) ORDER BY Rank.[Rank description];

I would like to feed the variable RankSel with the following:

Private Sub AdvSel_GotFocus()
Dim RenkSel
RankSel = Forms!AdvancementMaster!Rank
MsgBox "rank sel = " & RankSel < I get the correct value here!
End Sub

But when the query Asks for a value for RankSel, why dosen't teh value
i
get
from VB fill this field in the Query?

Help!

Dick

 

[Guest]

Thanks Ken,

Here is my code and NBA says can't find field 'form'

Private Sub AdvSel_LostFocus()
Me!Form!Advancementmaster!Rank.Value = Null
DoCmd.ReQuery
End Sub

The field rank is on the main form as shown in the query.

Any ideas?

The query will continue to return the same result so long as the value in
the Rank control on the form AdvancementMaster stays the same. So, you
probably should set the value of Rank control to Null in your requery code:

Private Sub AdvSel_LostFocus()
Me!Rank.Value = Null
DoCmd.ReQuery
End Sub


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I did what you suggested and it worked, however, the query now retains
that
value until i exit and reenter the form. Is there a way to clear that
value
and requery for the next selection.

Here is my current code:
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)=Forms!AdvancementMaster!Rank)) ORDER BY Rank.[Rank
description];


I also added VB statement on lossfocus

Private Sub AdvSel_LostFocus()
DoCmd.ReQuery
End Sub


Perhaps you could help me with this.

Many, many thanks,

Dick

Queries cannot see VBA variables. Either have the query read the value
from
the combo box on the form, or write a public function that returns the
value
of the variable to the query, and use that function in the query. I
recommend the first method.

--

Ken Snell
<MS ACCESS MVP>

in
message Hi All,

I have a combo box which uses the fowlling code.

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 [Ranksel])) ORDER BY Rank.[Rank description];

I would like to feed the variable RankSel with the following:

Private Sub AdvSel_GotFocus()
Dim RenkSel
RankSel = Forms!AdvancementMaster!Rank
MsgBox "rank sel = " & RankSel < I get the correct value here!
End Sub

But when the query Asks for a value for RankSel, why dosen't teh value
i
get
from VB fill this field in the Query?

Help!

Dick

 

[Ken Snell [MVP]]

This is what I posted:
Me!Rank.Value = Null

This is what you used:
Me!Form!Advancementmaster!Rank.Value = Null

Use what I posted.

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken,

Here is my code and NBA says can't find field 'form'

Private Sub AdvSel_LostFocus()
Me!Form!Advancementmaster!Rank.Value = Null
DoCmd.ReQuery
End Sub

The field rank is on the main form as shown in the query.

Any ideas?

The query will continue to return the same result so long as the value in
the Rank control on the form AdvancementMaster stays the same. So, you
probably should set the value of Rank control to Null in your requery
code:

Private Sub AdvSel_LostFocus()
Me!Rank.Value = Null
DoCmd.ReQuery
End Sub


--

Ken Snell
<MS ACCESS MVP>

in
message news:07F6C9FB-D1FB-4E2C-854A-D3A488A4E907@microsoft.com...

Hi Ken,

I did what you suggested and it worked, however, the query now retains
that
value until i exit and reenter the form. Is there a way to clear that
value
and requery for the next selection.

Here is my current code:
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)=Forms!AdvancementMaster!Rank)) ORDER BY Rank.[Rank
description];


I also added VB statement on lossfocus

Private Sub AdvSel_LostFocus()
DoCmd.ReQuery
End Sub


Perhaps you could help me with this.

Many, many thanks,

Dick

:

Queries cannot see VBA variables. Either have the query read the value
from
the combo box on the form, or write a public function that returns the
value
of the variable to the query, and use that function in the query. I
recommend the first method.

--

Ken Snell
<MS ACCESS MVP>

"(e-mail address removed)" <Rpattoncpsbizcom@discussions.microsoft.com>
wrote
in
message Hi All,

I have a combo box which uses the fowlling code.

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 [Ranksel])) ORDER BY Rank.[Rank description];

I would like to feed the variable RankSel with the following:

Private Sub AdvSel_GotFocus()
Dim RenkSel
RankSel = Forms!AdvancementMaster!Rank
MsgBox "rank sel = " & RankSel < I get the correct value here!
End Sub

But when the query Asks for a value for RankSel, why dosen't teh
value
i
get
from VB fill this field in the Query?

Help!

Dick

 

[Guest]

Sorry Ken,

I tryed what you suggested and it did not work. I get an error can't find
variable 'rank', so i tried to identify the form, e.g.
'forms!AdvancmentMaster!Rank', and i still receive as error, Can't find
variable 'forms'

Got any other ideas?

Dick

This is what I posted:
Me!Rank.Value = Null

This is what you used:
Me!Form!Advancementmaster!Rank.Value = Null

Use what I posted.

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken,

Here is my code and NBA says can't find field 'form'

Private Sub AdvSel_LostFocus()
Me!Form!Advancementmaster!Rank.Value = Null
DoCmd.ReQuery
End Sub

The field rank is on the main form as shown in the query.

Any ideas?

The query will continue to return the same result so long as the value in
the Rank control on the form AdvancementMaster stays the same. So, you
probably should set the value of Rank control to Null in your requery
code:

Private Sub AdvSel_LostFocus()
Me!Rank.Value = Null
DoCmd.ReQuery
End Sub


--

Ken Snell
<MS ACCESS MVP>

in
message Hi Ken,

I did what you suggested and it worked, however, the query now retains
that
value until i exit and reenter the form. Is there a way to clear that
value
and requery for the next selection.

Here is my current code:
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)=Forms!AdvancementMaster!Rank)) ORDER BY Rank.[Rank
description];


I also added VB statement on lossfocus

Private Sub AdvSel_LostFocus()
DoCmd.ReQuery
End Sub


Perhaps you could help me with this.

Many, many thanks,

Dick

:

Queries cannot see VBA variables. Either have the query read the value
from
the combo box on the form, or write a public function that returns the
value
of the variable to the query, and use that function in the query. I
recommend the first method.

--

Ken Snell
<MS ACCESS MVP>

"(e-mail address removed)" <Rpattoncpsbizcom@discussions.microsoft.com>
wrote
in
message Hi All,

I have a combo box which uses the fowlling code.

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 [Ranksel])) ORDER BY Rank.[Rank description];

I would like to feed the variable RankSel with the following:

Private Sub AdvSel_GotFocus()
Dim RenkSel
RankSel = Forms!AdvancementMaster!Rank
MsgBox "rank sel = " & RankSel < I get the correct value here!
End Sub

But when the query Asks for a value for RankSel, why dosen't teh
value
i
get
from VB fill this field in the Query?

Help!

Dick

 

[Ken Snell [MVP]]

I am assuming (based on your earlier post "Forms!AdvancementMaster!Rank")
that the control on your form is named Rank. If that is incorrect, then use
the real name of the control instead of Rank:
Me!RealNameOfControl.Value = Null

Or do you have more than one form open at the same time, and the one in
which you want to run the requery does not have a control named Rank?

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,

I tryed what you suggested and it did not work. I get an error can't find
variable 'rank', so i tried to identify the form, e.g.
'forms!AdvancmentMaster!Rank', and i still receive as error, Can't find
variable 'forms'

Got any other ideas?

Dick

This is what I posted:
Me!Rank.Value = Null

This is what you used:
Me!Form!Advancementmaster!Rank.Value = Null

Use what I posted.

--

Ken Snell
<MS ACCESS MVP>

in
message news:4F333AE3-D475-4EE7-8A5A-2B331466C335@microsoft.com...

Thanks Ken,

Here is my code and NBA says can't find field 'form'

Private Sub AdvSel_LostFocus()
Me!Form!Advancementmaster!Rank.Value = Null
DoCmd.ReQuery
End Sub

The field rank is on the main form as shown in the query.

Any ideas?

:

The query will continue to return the same result so long as the value
in
the Rank control on the form AdvancementMaster stays the same. So, you
probably should set the value of Rank control to Null in your requery
code:

Private Sub AdvSel_LostFocus()
Me!Rank.Value = Null
DoCmd.ReQuery
End Sub


--

Ken Snell
<MS ACCESS MVP>

"(e-mail address removed)" <Rpattoncpsbizcom@discussions.microsoft.com>
wrote
in
message Hi Ken,

I did what you suggested and it worked, however, the query now
retains
that
value until i exit and reenter the form. Is there a way to clear
that
value
and requery for the next selection.

Here is my current code:
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)=Forms!AdvancementMaster!Rank)) ORDER BY Rank.[Rank
description];


I also added VB statement on lossfocus

Private Sub AdvSel_LostFocus()
DoCmd.ReQuery
End Sub


Perhaps you could help me with this.

Many, many thanks,

Dick

:

Queries cannot see VBA variables. Either have the query read the
value
from
the combo box on the form, or write a public function that returns
the
value
of the variable to the query, and use that function in the query. I
recommend the first method.

--

Ken Snell
<MS ACCESS MVP>

"(e-mail address removed)" <Rpattoncpsbizcom@discussions.microsoft.com>
wrote
in
message Hi All,

I have a combo box which uses the fowlling code.

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 [Ranksel])) ORDER BY Rank.[Rank description];

I would like to feed the variable RankSel with the following:

Private Sub AdvSel_GotFocus()
Dim RenkSel
RankSel = Forms!AdvancementMaster!Rank
MsgBox "rank sel = " & RankSel < I get the correct value here!
End Sub

But when the query Asks for a value for RankSel, why dosen't teh
value
i
get
from VB fill this field in the Query?

Help!

Dick

 

[Guest]

Sorry for all of this trouble, and I realy appreciate your help here.

Yes, there is a a subform open with a combo box in it. The variable rank
needs to be cleared in the combo box prior to requery.

here are some details (I sure hope I am making it clearer not mudding the
water, any way.)

The Control name and field name on AdvancementMaster is rank. There is a
sub form (which is used to create records, Advancement records) and in the
sub form is a combo box containing the field rank as one of it's fields.

What I'm trying to do is select a participant from the AdvancementMaster
form (the Participant record contains the Cub Scouts rank). Then using that
rank choose only advancement appropriate to that rank. i.e. (if you were a
scout you will probably remember) John is a Bear and i want only advancement
for bears to show up in the combo box.

If you would be kind enough to send me your e-mail i will send a copy of the
Schema and the form in action. If you think that would help.

Again Many,Many thanks
Central Florida Council, BSA and I appreciate your time.

Dick

I am assuming (based on your earlier post "Forms!AdvancementMaster!Rank")
that the control on your form is named Rank. If that is incorrect, then use
the real name of the control instead of Rank:
Me!RealNameOfControl.Value = Null

Or do you have more than one form open at the same time, and the one in
which you want to run the requery does not have a control named Rank?

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,

I tryed what you suggested and it did not work. I get an error can't find
variable 'rank', so i tried to identify the form, e.g.
'forms!AdvancmentMaster!Rank', and i still receive as error, Can't find
variable 'forms'

Got any other ideas?

Dick

This is what I posted:
Me!Rank.Value = Null

This is what you used:
Me!Form!Advancementmaster!Rank.Value = Null

Use what I posted.

--

Ken Snell
<MS ACCESS MVP>

in
message Thanks Ken,

Here is my code and NBA says can't find field 'form'

Private Sub AdvSel_LostFocus()
Me!Form!Advancementmaster!Rank.Value = Null
DoCmd.ReQuery
End Sub

The field rank is on the main form as shown in the query.

Any ideas?

:

The query will continue to return the same result so long as the value
in
the Rank control on the form AdvancementMaster stays the same. So, you
probably should set the value of Rank control to Null in your requery
code:

Private Sub AdvSel_LostFocus()
Me!Rank.Value = Null
DoCmd.ReQuery
End Sub


--

Ken Snell
<MS ACCESS MVP>

"(e-mail address removed)" <Rpattoncpsbizcom@discussions.microsoft.com>
wrote
in
message Hi Ken,

I did what you suggested and it worked, however, the query now
retains
that
value until i exit and reenter the form. Is there a way to clear
that
value
and requery for the next selection.

Here is my current code:
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)=Forms!AdvancementMaster!Rank)) ORDER BY Rank.[Rank
description];


I also added VB statement on lossfocus

Private Sub AdvSel_LostFocus()
DoCmd.ReQuery
End Sub


Perhaps you could help me with this.

Many, many thanks,

Dick

:

Queries cannot see VBA variables. Either have the query read the
value
from
the combo box on the form, or write a public function that returns
the
value
of the variable to the query, and use that function in the query. I
recommend the first method.

--

Ken Snell
<MS ACCESS MVP>

"(e-mail address removed)" <Rpattoncpsbizcom@discussions.microsoft.com>
wrote
in
message Hi All,

I have a combo box which uses the fowlling code.

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 [Ranksel])) ORDER BY Rank.[Rank description];

I would like to feed the variable RankSel with the following:

Private Sub AdvSel_GotFocus()
Dim RenkSel
RankSel = Forms!AdvancementMaster!Rank
MsgBox "rank sel = " & RankSel < I get the correct value here!
End Sub

But when the query Asks for a value for RankSel, why dosen't teh
value
i
get
from VB fill this field in the Query?

Help!

Dick

 

[Ken Snell [MVP]]

OK - let's see if I'm understanding.

AdvancementMaster is the name of the main form.

This main form contains a subform whose source object is named Advancement.

You have a control named Rank on the main form; this control is a combo box
that allows you to select a specific "rank". From this selection, you wish
to filter a combo box on the subform so that the subform's combo box will
contain only the "advancement" values that are appropriate to the selected
"rank".

You want to requery the combo box on the subform whenever a selection is
made in the main form's combo box.


Have I stated correctly what you have?

Assuming that I have, I will need the following information from you so that
we can put together the appropriate code for the main form's combo box (by
the way, that code will likely use the AfterUpdate event of the main form's
combo box, not a LostFocus event):

(1) the name of the subform control (the actual control that holds the
subform; this control's SourceObject apparently is Advancement).

(2) the name of the combo box that is on the subform.

(3) the current Row Source of the combo box that is on the subform.

 

[Guest]

Good Morning Ken,

I will try to answer your questions the best I can.

I think you have it right, but just to be sure i will repeat it back with
the details you requested.

You want to requery the combo box on the subform whenever a selection is
made in the main form's combo box. YES (so that the AdvSel will contain a the rank of the new scout selected)

(1) the name of the subform control (the actual control that holds the

subform; this control's SourceObject apparently is Advancement). YES it's teh Advancment table

(2) the name of the combo box that is on the subform.

the combo is AdvSel

(3) the current Row Source of the combo box that is on the subfo

is a query called

AdvCodeQuery - That the SQL i sent the first time.

More info:

the AdvancementMaster form contains 6 controls, (each is a field in the
participant table) ID (the key), name, Den Number, Rank, Home Unit, and Age.
"Rank" is both the Control Source and Control Name name (should they be
differant, i have always wondered). The user chooes a Scout and the and the
rank control now contains that scouts rank.

The sub form cantains the advancment information (ID, record Number,
Advancment Code, and date (not rank)). This info is in the advancement
table. Each record forms a link between the participant table and the
Advancement code table. (I will e-mail the structure to you)

In the sub form (called advancment Subform, it is in table view) there is a
field called Advancement code. This code is selected based on a combo box
called AdvSel (that is where the query AdvCodeQuery is located). So when the
user selected a participant the sub form which is keyed to ID now contains
all information needed to create an advancment record except the Advancement
code. The user now selects the AdvSel combo box, and is presented with a
list of advancement codes for the rank, derived from the Rank related to that
scout id on the main form (the control name and control source is Rank, but
this time it's the rank from the query AdvCodeQuery ). The user clicks an
advancment code and a record is added to the Advancement table. The user may
then choose another advancement for this scout or go to the main form and
choose another scout.

I hope i have not confued you more, it's so simple whent you see a picture,
again i will try to e-main the structure to you at your address shown on the
site.


This is so good of you to take this time. I am sorry to be a pain, i hope i
answered all of your questions.

Dick

 

[Ken Snell [MVP]]

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

Ken Snell
<MS ACCESS MVP>

 

[Guest]

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.

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).

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".

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

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

Ken Snell
<MS ACCESS MVP>

Good Morning Ken,

I will try to answer your questions the best I can.

I think you have it right, but just to be sure i will repeat it back with
the details you requested.


(1) the name of the subform control (the actual control that holds the

the combo is AdvSel

is a query called

AdvCodeQuery - That the SQL i sent the first time.

More info:

the AdvancementMaster form contains 6 controls, (each is a field in the
participant table) ID (the key), name, Den Number, Rank, Home Unit, and
Age.
"Rank" is both the Control Source and Control Name name (should they be
differant, i have always wondered). The user chooes a Scout and the and
the
rank control now contains that scouts rank.

The sub form cantains the advancment information (ID, record Number,
Advancment Code, and date (not rank)). This info is in the advancement
table. Each record forms a link between the participant table and the
Advancement code table. (I will e-mail the structure to you)

In the sub form (called advancment Subform, it is in table view) there is
a
field called Advancement code. This code is selected based on a combo box
called AdvSel (that is where the query AdvCodeQuery is located). So when
the
user selected a participant the sub form which is keyed to ID now contains
all information needed to create an advancment record except the
Advancement
code. The user now selects the AdvSel combo box, and is presented with a
list of advancement codes for the rank, derived from the Rank related to
that
scout id on the main form (the control name and control source is Rank,
but
this time it's the rank from the query AdvCodeQuery ). The user clicks an
advancment code and a record is added to the Advancement table. The user
may
then choose another advancement for this scout or go to the main form and
choose another scout.

I hope i have not confued you more, it's so simple whent you see a
picture,
again i will try to e-main the structure to you at your address shown on
the
site.


This is so good of you to take this time. I am sorry to be a pain, i hope
i
answered all of your questions.

Dick

 

[Ken Snell [MVP]]

OK we're close I think.

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

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 >

 

[Guest]

Hi Ken,

I have been in the Hospital for Heart Cath for a few days and i am just
returning to see you reply. I will try it today.

Thank you for your patience.

Dick

OK we're close I think.

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

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 >

 

[Guest]

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>

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 >

 

[Ken Snell [MVP]]

Glad to hear you got it! This subform issue is very common for ACCESS
users -- we all have tread that path before gaining our "Subform Merit
Badge".

Good luck!

--

Ken Snell
<MS ACCESS MVP>

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>

in
message news:8CC61AE0-7E85-4016-9AAA-D3E93A95456E@microsoft.com...

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 >

 

[Ken Snell [MVP]]

Hi Ken,

I have been in the Hospital for Heart Cath for a few days and i am just
returning to see you reply.

I trust that all will be well for you!

 

[Guest]

Thanks Ken I will put in for that one, Only after the next time i use the
same process and get it right away.

How do we close this question?

One last item, how does one make an exe file from the code?

Glad to hear you got it! This subform issue is very common for ACCESS
users -- we all have tread that path before gaining our "Subform Merit
Badge".

Good luck!

--

Ken Snell
<MS ACCESS MVP>

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>

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 >

 

[Ken Snell [MVP]]

Closing the question? We just agree that we're done...nothing formal here as
we're all volunteers who don't get paid for "closures"! < g >

As for .exe file, no can do with ACCESS. If you wish to allow users to use
the database when those users don't have a copy of ACCESS software, then you
would need to obtain the developer version of ACCESS (actual name depends
upon which version of ACCESS you're using). The developer version allows you
to distribute a royalty-free "runtime" version of ACCESS that will allow
your users to use your database.

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken I will put in for that one, Only after the next time i use the
same process and get it right away.

How do we close this question?

One last item, how does one make an exe file from the code?

Glad to hear you got it! This subform issue is very common for ACCESS
users -- we all have tread that path before gaining our "Subform Merit
Badge".

Good luck!

--

Ken Snell
<MS ACCESS MVP>

in
message news:2288A32F-27E4-4F49-A410-1AE77836ABB1@microsoft.com...

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)" <Rpattoncpsbizcom@discussions.microsoft.com>
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 >