Combo Box Problem

I have placed the logic below in the After Update of the
first combo, but the data in the second combo still does
not update when the data in the first combo is changed.

Private Sub cboOwner_AfterUpdate()
Me.cboContact.Requery
End Sub

There are quite a few subroutines in the VBA window (I did
not add them, they were already there) -- could one these
be causing my problems? I do have your SQL statement in
the row source of my second combo.


-----Original Message-----
If the row source for the second combo is a SQL statement
like the one I provided, then you need to put a 'requery'
instruction in the after update of the first combo. This
will then cause the change to happen correctly. Check my
answer to your question number 4.

And I'm glad to know that you do know how to build
tables - sorry for misunderstanding you.

--
rpw


"Mr. T." wrote:

Actually, I now have this working. Except, if I pick
Joyce, I get the 10 task in second combo box that
relate
to her. If I then Select Linda from the first combo
box, I
still show Joyce's 10 tasks. Do I need to add something
to
the "On Change" field of the second combo box?


-----Original Message-----
Oops, I think I can add a little more.....
--
rpw


"rpw" wrote:

Comments in-line below.....
--
rpw


"Mr. T" wrote:

We have a few more questions, please:
1. how do we generate the table
named "tblTaskAssignments", and where does the
field "AssignID" come from?

From the databse window, select 'Create table in
Design
view' or 'Create table by using wizard'. The field is
an
autonumber primary key for the table. (This question
is
sorta scary - you don't know how to build a table? If
not, I'm afraid I couldn't detail all of the steps very
well. Besides, there are several books available that
would do a better job than I could do. And don't
forget
about the Access help off-line and on-line resources.)


2. You've advised us that "..the record source for
the
second combo should be a query that is filtered by
the
value in the first combo. Since we have 3
different
NameID
options (Joyce, Linda, Susan), how do we use one
query to
filter based on the selection of one of these --
my
thoughts are that we need 3 queries, one for each
name
where we enter the name in our criteria row of the
query.

If your table is set up correctly, then Joyce =
NameID
1, Linda = 2, Susan = 3. When the first combo
selects "Joyce", the ID is 1. The second combo
uses '1'
as the filter criteria. There is no need for three
queries when you use the code provided.

Not code exactly, but SQL. In the row source property
of
the second combo, paste the following:

SELECT tblTaskAssignments.AssignID,
tblTaskAssignments.TaskID, tblTaskAssignments.NameID
FROM tblTaskAssignments
WHERE (((tblTaskAssignments.NameID)=[Forms]!
[frmMyForm]!
[cboName]));

(Of course, if your table names, field names, form
names,
control names are different, then you must change this
SQL
to reflect your names.)



3. You've mentioned that we need to update
the "record
source" field in our form -- I don't see a "record
source"
field, I do see a "row source" field. Is this the
same
field?

Yes, "row source" is the correct terminology - sorry
for any confusion it caused you.

4. You've advised us to "...In the After_Update
event
of
the first combo, requery the second combo". What
does
this
mean -- have you detailed this in your initial
response?

Open the Properties for the first combo, click the
Event tab, in the list of events find "After Update",
click in the field and a drop-down arrow appears. From
the drop-down list select [Event Procedure]. Then
click
the elipsis (...) to the right of the field. This will
open VBA and that is where the code goes. Here is the
code:

Private Sub cboName_AfterUpdate()
Me.cboTask.Requery
End Sub


Thanks for the help.

You're welcome.






-----Original Message-----
The record source for the second combo should be
a
query
that is filtered by the value in the first combo.
In
the
After_Update event of the first combo, requery the
second
combo.

tblNames
NameID

tblTasks
TaskID

tblTaskAssignments
AssignID
TaskID
NameID

cboName
record source = tblNames
After Update event code:
Private Sub cboName_AfterUpdate()
Me.cboTask.Requery
End Sub

cboTask
record source =
SELECT tblTaskAssignments.AssignID,
tblTaskAssignments.TaskID,
tblTaskAssignments.NameID
FROM tblTaskAssignments
WHERE (((tblTaskAssignments.NameID)=[Forms]!
[frmMyForm]!
[cboName]));

If this isn't enough information/direction to
help
you,
then you'll need to post specifics about the
related
tables, fields, and control names.
--
rpw


"Mr. T" wrote:

I have a form that contains two combo boxes.
My
plan
is
that the first combo box will select a name,
and
then
the
second combo box would only contain the
specific
tasks
that correspond with the name selected in box
one
(it
currently contains all the tasks for all the
names
in
combo box 1). I think I have to adjust either
the
control
source or the after update field, but I am
unsure
how
to
accomplish the goal. Any suggestions?

.


.


.

Have you modified the SQL to reflect your table, form, and control names?
--
rpw


"Mr T." wrote:

I have placed the logic below in the After Update of the
first combo, but the data in the second combo still does
not update when the data in the first combo is changed.

Private Sub cboOwner_AfterUpdate()
Me.cboContact.Requery
End Sub

There are quite a few subroutines in the VBA window (I did
not add them, they were already there) -- could one these
be causing my problems? I do have your SQL statement in
the row source of my second combo.


-----Original Message-----
If the row source for the second combo is a SQL statement
like the one I provided, then you need to put a 'requery'
instruction in the after update of the first combo. This
will then cause the change to happen correctly. Check my
answer to your question number 4.

And I'm glad to know that you do know how to build
tables - sorry for misunderstanding you.

--
rpw


"Mr. T." wrote:

Actually, I now have this working. Except, if I pick
Joyce, I get the 10 task in second combo box that
relate
to her. If I then Select Linda from the first combo
box, I
still show Joyce's 10 tasks. Do I need to add something
to
the "On Change" field of the second combo box?


-----Original Message-----
Oops, I think I can add a little more.....
--
rpw


"rpw" wrote:

Comments in-line below.....
--
rpw


"Mr. T" wrote:

We have a few more questions, please:
1. how do we generate the table
named "tblTaskAssignments", and where does the
field "AssignID" come from?

From the databse window, select 'Create table in
Design
view' or 'Create table by using wizard'. The field is
an
autonumber primary key for the table. (This question
is
sorta scary - you don't know how to build a table? If
not, I'm afraid I couldn't detail all of the steps very
well. Besides, there are several books available that
would do a better job than I could do. And don't
forget
about the Access help off-line and on-line resources.)


2. You've advised us that "..the record source for
the
second combo should be a query that is filtered by
the
value in the first combo. Since we have 3
different
NameID
options (Joyce, Linda, Susan), how do we use one
query to
filter based on the selection of one of these --
my
thoughts are that we need 3 queries, one for each
name
where we enter the name in our criteria row of the
query.

If your table is set up correctly, then Joyce =
NameID
1, Linda = 2, Susan = 3. When the first combo
selects "Joyce", the ID is 1. The second combo
uses '1'
as the filter criteria. There is no need for three
queries when you use the code provided.

Not code exactly, but SQL. In the row source property
of
the second combo, paste the following:

SELECT tblTaskAssignments.AssignID,
tblTaskAssignments.TaskID, tblTaskAssignments.NameID
FROM tblTaskAssignments
WHERE (((tblTaskAssignments.NameID)=[Forms]!
[frmMyForm]!
[cboName]));

(Of course, if your table names, field names, form
names,
control names are different, then you must change this
SQL
to reflect your names.)



3. You've mentioned that we need to update
the "record
source" field in our form -- I don't see a "record
source"
field, I do see a "row source" field. Is this the
same
field?

Yes, "row source" is the correct terminology - sorry
for any confusion it caused you.

4. You've advised us to "...In the After_Update
event
of
the first combo, requery the second combo". What
does
this
mean -- have you detailed this in your initial
response?

Open the Properties for the first combo, click the
Event tab, in the list of events find "After Update",
click in the field and a drop-down arrow appears. From
the drop-down list select [Event Procedure]. Then
click
the elipsis (...) to the right of the field. This will
open VBA and that is where the code goes. Here is the
code:

Private Sub cboName_AfterUpdate()
Me.cboTask.Requery
End Sub


Thanks for the help.

You're welcome.






-----Original Message-----
The record source for the second combo should be
a
query
that is filtered by the value in the first combo.
In
the
After_Update event of the first combo, requery the
second
combo.

tblNames
NameID

tblTasks
TaskID

tblTaskAssignments
AssignID
TaskID
NameID

cboName
record source = tblNames
After Update event code:
Private Sub cboName_AfterUpdate()
Me.cboTask.Requery
End Sub

cboTask
record source =
SELECT tblTaskAssignments.AssignID,
tblTaskAssignments.TaskID,
tblTaskAssignments.NameID
FROM tblTaskAssignments
WHERE (((tblTaskAssignments.NameID)=[Forms]!
[frmMyForm]!
[cboName]));

If this isn't enough information/direction to
help
you,
then you'll need to post specifics about the
related
tables, fields, and control names.
--
rpw


"Mr. T" wrote:

I have a form that contains two combo boxes.
My
plan
is
that the first combo box will select a name,
and
then
the
second combo box would only contain the
specific
tasks
that correspond with the name selected in box
one
(it
currently contains all the tasks for all the
names
in
combo box 1). I think I have to adjust either
the
control
source or the after update field, but I am
unsure
how
to
accomplish the goal. Any suggestions?

.


.


.

I believe so. Here is my SQL:

SELECT [Contact_linked].[VendorName] FROM Contact_linked
WHERE ((([Contact_linked].[OwnerName])=[Forms]![Menu]!
[Owner])) ORDER BY [Contact_linked].[VendorName];

My two combo boxes are named "Owner" and "Contact". My
table is named "Contact_linked" and my form is
named "Menu".

What does the "Me." do in the "Me.cboContact.Requery" line?

-----Original Message-----
Have you modified the SQL to reflect your table, form,
and control names?
--
rpw


"Mr T." wrote:

I have placed the logic below in the After Update of
the
first combo, but the data in the second combo still
does
not update when the data in the first combo is changed.

Private Sub cboOwner_AfterUpdate()
Me.cboContact.Requery
End Sub

There are quite a few subroutines in the VBA window (I
did
not add them, they were already there) -- could one
these
be causing my problems? I do have your SQL statement
in
the row source of my second combo.


-----Original Message-----
If the row source for the second combo is a SQL
statement
like the one I provided, then you need to put
a 'requery'
instruction in the after update of the first combo.
This
will then cause the change to happen correctly. Check
my
answer to your question number 4.

And I'm glad to know that you do know how to build
tables - sorry for misunderstanding you.

--
rpw


"Mr. T." wrote:

Actually, I now have this working. Except, if I pick
Joyce, I get the 10 task in second combo box that
relate
to her. If I then Select Linda from the first combo
box, I
still show Joyce's 10 tasks. Do I need to add
something
to
the "On Change" field of the second combo box?


-----Original Message-----
Oops, I think I can add a little more.....
--
rpw


"rpw" wrote:

Comments in-line below.....
--
rpw


"Mr. T" wrote:

We have a few more questions, please:
1. how do we generate the table
named "tblTaskAssignments", and where does the
field "AssignID" come from?

From the databse window, select 'Create table in
Design
view' or 'Create table by using wizard'. The field
is
an
autonumber primary key for the table. (This
question
is
sorta scary - you don't know how to build a table?
If
not, I'm afraid I couldn't detail all of the steps
very
well. Besides, there are several books available
that
would do a better job than I could do. And don't
forget
about the Access help off-line and on-line
resources.)


2. You've advised us that "..the record source
for
the
second combo should be a query that is filtered
by
the
value in the first combo. Since we have 3
different
NameID
options (Joyce, Linda, Susan), how do we use
one
query to
filter based on the selection of one of these --

my
thoughts are that we need 3 queries, one for
each
name
where we enter the name in our criteria row of
the
query.

If your table is set up correctly, then Joyce =
NameID
1, Linda = 2, Susan = 3. When the first combo
selects "Joyce", the ID is 1. The second combo
uses '1'
as the filter criteria. There is no need for three
queries when you use the code provided.

Not code exactly, but SQL. In the row source
property
of
the second combo, paste the following:

SELECT tblTaskAssignments.AssignID,
tblTaskAssignments.TaskID, tblTaskAssignments.NameID
FROM tblTaskAssignments
WHERE (((tblTaskAssignments.NameID)=[Forms]!
[frmMyForm]!
[cboName]));

(Of course, if your table names, field names, form
names,
control names are different, then you must change
this
SQL
to reflect your names.)



3. You've mentioned that we need to update
the "record
source" field in our form -- I don't see
a "record
source"
field, I do see a "row source" field. Is this
the
same
field?

Yes, "row source" is the correct terminology -
sorry
for any confusion it caused you.

4. You've advised us to "...In the After_Update
event
of
the first combo, requery the second combo".
What
does
this
mean -- have you detailed this in your initial
response?

Open the Properties for the first combo, click
the
Event tab, in the list of events find "After
Update",
click in the field and a drop-down arrow appears.
From
the drop-down list select [Event Procedure]. Then
click
the elipsis (...) to the right of the field. This
will
open VBA and that is where the code goes. Here is
the
code:

Private Sub cboName_AfterUpdate()
Me.cboTask.Requery
End Sub


Thanks for the help.

You're welcome.






-----Original Message-----
The record source for the second combo should
be
a
query
that is filtered by the value in the first
combo.
In
the
After_Update event of the first combo, requery
the
second
combo.

tblNames
NameID

tblTasks
TaskID

tblTaskAssignments
AssignID
TaskID
NameID

cboName
record source = tblNames
After Update event code:
Private Sub cboName_AfterUpdate()
Me.cboTask.Requery
End Sub

cboTask
record source =
SELECT tblTaskAssignments.AssignID,
tblTaskAssignments.TaskID,
tblTaskAssignments.NameID
FROM tblTaskAssignments
WHERE (((tblTaskAssignments.NameID)=[Forms]!
[frmMyForm]!
[cboName]));

If this isn't enough information/direction to
help
you,
then you'll need to post specifics about the
related
tables, fields, and control names.
--
rpw


"Mr. T" wrote:

I have a form that contains two combo
boxes.
My
plan
is
that the first combo box will select a name,
and
then
the
second combo box would only contain the
specific
tasks
that correspond with the name selected in
box
one
(it
currently contains all the tasks for all the
names
in
combo box 1). I think I have to adjust
either
the
control
source or the after update field, but I am
unsure
how
to
accomplish the goal. Any suggestions?

.


.


.


.

"Mr. T." wrote:


I believe so. Here is my SQL:

SELECT [Contact_linked].[VendorName] FROM Contact_linked
WHERE ((([Contact_linked].[OwnerName])=[Forms]![Menu]!
[Owner])) ORDER BY [Contact_linked].[VendorName];

My two combo boxes are named "Owner" and "Contact". My
table is named "Contact_linked" and my form is
named "Menu".

What does the "Me." do in the "Me.cboContact.Requery" line?


I think I see the problem - you don't have a control named cboContact, it's named Contact. So the after update code should read:

Me.Contact.Requery

The "Me" is a feature in Access that allows the code to refer to the form without having to type in "[Forms]![Menu]!" in front of the control name.

This did not solve the problem. Here is the new SQL:

Private Sub cboOwner_AfterUpdate()
Me.Contact.Requery
End Sub

Since my first combo box is named "Owner", should the
first line read "Private Sub Owner_AfterUpdate()" withouth
the 'cbo'?

Is a "control" the same as the Name field in combo Other
properties?

Also, when I click on the [Event Procedure] then click
on "...", VBA window opens. How does Access know which
event procedure to execute for this field's [Event
Procedure] -- is it based on the info after the
words "Private Sub"?


-----Original Message-----

"Mr. T." wrote:


I believe so. Here is my SQL:

SELECT [Contact_linked].[VendorName] FROM
Contact_linked
WHERE ((([Contact_linked].[OwnerName])=[Forms]![Menu]!
[Owner])) ORDER BY [Contact_linked].[VendorName];

My two combo boxes are named "Owner" and "Contact". My
table is named "Contact_linked" and my form is
named "Menu".

What does the "Me." do in the "Me.cboContact.Requery"
line?


I think I see the problem - you don't have a control
named cboContact, it's named Contact. So the after update
code should read:

Me.Contact.Requery

The "Me" is a feature in Access that allows the code to
refer to the form without having to type in "[Forms]!
[Menu]!" in front of the control name.
.

"Mr. T." wrote:

This did not solve the problem. Here is the new SQL:

(this is code, not SQL - see notes further below)


Private Sub cboOwner_AfterUpdate()
Me.Contact.Requery
End Sub

Since my first combo box is named "Owner", should the
first line read "Private Sub Owner_AfterUpdate()" withouth
the 'cbo'?

----- Yes this is correct.


Is a "control" the same as the Name field in combo Other
properties?

I'm not sure I understand the question correctly, but I'll try.

"Control" is a generic term used to describe a textbox, combo, listbox, or any other object used to control data. "Name" is the name you give the control. Try doing a google search on Access naming conventions. Typically, there is a prefix that identifies the object. So a table of names is named "tblName", a query is "qryName", a form is "frmName", a text box is "txtName", a combo is "cboName", a listbox is "lboName". Naming conventions articles are several pages long so I'll stop at this, but I hope you get the idea.



Also, when I click on the [Event Procedure] then click
on "...", VBA window opens. How does Access know which
event procedure to execute for this field's [Event
Procedure] -- is it based on the info after the
words "Private Sub"?


Yes - this last statement/question is correct. You select [Event Procedure], then click the elipsis (...) and the VBA window opens. There will automatically be the correct text following the words "Private Sub". On the next line is where you put the code "Me.Contact.Requery".

Actually, just type the word "me" and then a dot "." (don't worry about capitalization - VBA will fix it later). VBA's intellisense should bring up a list of possibilities. Type the letter "co" and the list will move to the 'Co' portion of the list. "Contact" should be in the list so click on it and it will be placed in the code. Type another dot and another list will appear. Type "req" and Requery should be selected in the list. Hit the tab key. Now you're done entering the code.

By the way "SQL" stands for "Standard Query Language" and SQL is the text version of a query - which usually starts with the word SELECT. "Code" usually refers to the text that is displayed in the VBA window.


Hope this helps

-----Original Message-----

"Mr. T." wrote:


I believe so. Here is my SQL:

SELECT [Contact_linked].[VendorName] FROM
Contact_linked
WHERE ((([Contact_linked].[OwnerName])=[Forms]![Menu]!
[Owner])) ORDER BY [Contact_linked].[VendorName];

My two combo boxes are named "Owner" and "Contact". My
table is named "Contact_linked" and my form is
named "Menu".

What does the "Me." do in the "Me.cboContact.Requery"
line?


I think I see the problem - you don't have a control
named cboContact, it's named Contact. So the after update
code should read:

Me.Contact.Requery

The "Me" is a feature in Access that allows the code to
refer to the form without having to type in "[Forms]!
[Menu]!" in front of the control name.
.