Combo box source

D

DeanT

I have a main form (projects) with 2 subforms (employees) and (comments)

I want the combo box field (author) in the (comments) subform to be
populated with the data from the combo box field (EmployeeName) in the
(employees) subform each time I access/update the main form.

Thanks for any help, Dean
 
K

Ken Snell \(MVP\)

Are the subforms "continuous view" subforms? Does "access/update the main
form" mean that you want the combo box in Comments subform to be updated
whenever you open the main form? whenever you make any type of data change
on the main form's data? What about if you make a change to the Employees
subform -- should the combo box in Comments subform be updated then, too?

It may be appropriate to have combo box in Comments subform, but if you
always want its value to be that from the Employees subform, it may be
better if you rethink the design to either use a "locked" textbox or
something similar?

Give us some more details and then we can assist with the best way to do
what you seek.
 
D

DeanT

The sub-Forms are continuous.
The subform called "comments" has a combo box called "author" . I want the
drop down to list the names that are in the Subform called "Employees" in the
field "employee". Each time a the main form is accessed or the user moves to
the next main form, I want the drop-down box in the "Comments" subform to
list only the names in the sumform "employees" on that specific main form.
 
K

Ken Snell \(MVP\)

Please provide the following information so that we can assist you further:

1) The SQL statement of the main form's RecordSource query.

2) The SQL statement of the Employees subform's RecordSource query.

3) The LinkChildFields and LinkMasterFields values for the subform control
that holds the Employees subform.

4) The SQL statement for the RowSource query of the "author" combo box in
the Comments subform.
 
D

DeanT

1- The main form (frmProjects) record source is tblProjects
2- The employee subform record source is;

SELECT Employees.Project_ID, Employees.EmployeeName, Employees.Title FROM
Employees;

The Comments subform record source is:

SELECT Comments.Project_ID, Comments.Author, Comments.CommentDate,
Comments.Comment FROM Comments;

3-Both of the subforms are linked to the main frmProjects by the Project_ID
field in each.

4- The row source statement in the author field is:

SELECT Employees.EmployeeName FROM Employees;


The problem is: The employee name values are different for each project main
form. When I access the "author" field the drop-down box shows all the
"employeename" values from all projects, NOT just the values from the
frmProject I am on. When I go from project to project, the author drop down
should give me just the employeeNames from the employee subform that is on
the current project.

Thanks for help.
 
K

Ken Snell \(MVP\)

OK. Let's change the RowSource for the Authors combo box in the comments
subform to this:

SELECT Employees.EmployeeName
FROM Employees
WHERE Employees.Project_ID = [Parent].[Project_ID];

This will "tie" the combo box in the comments subform to the Project_ID
value in the main form, which I believe is what you want here? You likely
will need to cause a requery of the authors combo box when the main form is
"moved" to a different record. But I think in your case here, the easier
thing to do will be to requery the combo box in the Enter event of the
subform control that contains the comments subform.


( Note: this next query probably would work, too, as the RowSource, but
might create an "error" at times if the combo box "requeries" before the
subform is fully populated with data, or when the subform has no data:
SELECT Employees.EmployeeName
FROM Employees
WHERE Employees.Project_ID = [Project_ID];
So I recommend that you use the first query that I list above.)


--

Ken Snell
<MS ACCESS MVP>
 
D

DeanT

Almost there. The combo box worked fine but when I "moved" to a different
record the cohices from the previous record appeared in the drop down so , as
you suggested, I need to requery. How and where do I requery. Where do I put
it and what is the syntax.

Thanks for your patience with a novice coder.

Ken Snell (MVP) said:
OK. Let's change the RowSource for the Authors combo box in the comments
subform to this:

SELECT Employees.EmployeeName
FROM Employees
WHERE Employees.Project_ID = [Parent].[Project_ID];

This will "tie" the combo box in the comments subform to the Project_ID
value in the main form, which I believe is what you want here? You likely
will need to cause a requery of the authors combo box when the main form is
"moved" to a different record. But I think in your case here, the easier
thing to do will be to requery the combo box in the Enter event of the
subform control that contains the comments subform.


( Note: this next query probably would work, too, as the RowSource, but
might create an "error" at times if the combo box "requeries" before the
subform is fully populated with data, or when the subform has no data:
SELECT Employees.EmployeeName
FROM Employees
WHERE Employees.Project_ID = [Project_ID];
So I recommend that you use the first query that I list above.)


--

Ken Snell
<MS ACCESS MVP>





DeanT said:
1- The main form (frmProjects) record source is tblProjects
2- The employee subform record source is;

SELECT Employees.Project_ID, Employees.EmployeeName, Employees.Title FROM
Employees;

The Comments subform record source is:

SELECT Comments.Project_ID, Comments.Author, Comments.CommentDate,
Comments.Comment FROM Comments;

3-Both of the subforms are linked to the main frmProjects by the
Project_ID
field in each.

4- The row source statement in the author field is:

SELECT Employees.EmployeeName FROM Employees;


The problem is: The employee name values are different for each project
main
form. When I access the "author" field the drop-down box shows all the
"employeename" values from all projects, NOT just the values from the
frmProject I am on. When I go from project to project, the author drop
down
should give me just the employeeNames from the employee subform that is on
the current project.

Thanks for help.
 
K

Ken Snell \(MVP\)

By "moved", I assume you mean you moved the record in the main form. If this
is correct, then use the main form's Current event to cause the requery of
the authors combo box (replace my generic names --
NameOfSubformControlHoldingCommentsSubform and NameOfAuthorsComboBox -- with
the real names):

Private Sub Form_Current()
Me.NameOfSubformControlHoldingCommentsSubform.Form.NameOfAuthorsComboBox.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>



DeanT said:
Almost there. The combo box worked fine but when I "moved" to a different
record the cohices from the previous record appeared in the drop down so ,
as
you suggested, I need to requery. How and where do I requery. Where do I
put
it and what is the syntax.

Thanks for your patience with a novice coder.

Ken Snell (MVP) said:
OK. Let's change the RowSource for the Authors combo box in the comments
subform to this:

SELECT Employees.EmployeeName
FROM Employees
WHERE Employees.Project_ID = [Parent].[Project_ID];

This will "tie" the combo box in the comments subform to the Project_ID
value in the main form, which I believe is what you want here? You likely
will need to cause a requery of the authors combo box when the main form
is
"moved" to a different record. But I think in your case here, the easier
thing to do will be to requery the combo box in the Enter event of the
subform control that contains the comments subform.


( Note: this next query probably would work, too, as the RowSource, but
might create an "error" at times if the combo box "requeries" before the
subform is fully populated with data, or when the subform has no data:
SELECT Employees.EmployeeName
FROM Employees
WHERE Employees.Project_ID = [Project_ID];
So I recommend that you use the first query that I list above.)


--

Ken Snell
<MS ACCESS MVP>





DeanT said:
1- The main form (frmProjects) record source is tblProjects
2- The employee subform record source is;

SELECT Employees.Project_ID, Employees.EmployeeName, Employees.Title
FROM
Employees;

The Comments subform record source is:

SELECT Comments.Project_ID, Comments.Author, Comments.CommentDate,
Comments.Comment FROM Comments;

3-Both of the subforms are linked to the main frmProjects by the
Project_ID
field in each.

4- The row source statement in the author field is:

SELECT Employees.EmployeeName FROM Employees;


The problem is: The employee name values are different for each project
main
form. When I access the "author" field the drop-down box shows all the
"employeename" values from all projects, NOT just the values from the
frmProject I am on. When I go from project to project, the author drop
down
should give me just the employeeNames from the employee subform that is
on
the current project.

Thanks for help.

:

Please provide the following information so that we can assist you
further:

1) The SQL statement of the main form's RecordSource query.

2) The SQL statement of the Employees subform's RecordSource query.

3) The LinkChildFields and LinkMasterFields values for the subform
control
that holds the Employees subform.

4) The SQL statement for the RowSource query of the "author" combo box
in
the Comments subform.

--

Ken Snell
<MS ACCESS MVP>



The sub-Forms are continuous.
The subform called "comments" has a combo box called "author" . I
want
the
drop down to list the names that are in the Subform called
"Employees"
in
the
field "employee". Each time a the main form is accessed or the user
moves
to
the next main form, I want the drop-down box in the "Comments"
subform
to
list only the names in the sumform "employees" on that specific main
form.

:

Are the subforms "continuous view" subforms? Does "access/update
the
main
form" mean that you want the combo box in Comments subform to be
updated
whenever you open the main form? whenever you make any type of data
change
on the main form's data? What about if you make a change to the
Employees
subform -- should the combo box in Comments subform be updated
then,
too?

It may be appropriate to have combo box in Comments subform, but if
you
always want its value to be that from the Employees subform, it may
be
better if you rethink the design to either use a "locked" textbox
or
something similar?

Give us some more details and then we can assist with the best way
to
do
what you seek.
--

Ken Snell
<MS ACCESS MVP>




I have a main form (projects) with 2 subforms (employees) and
(comments)

I want the combo box field (author) in the (comments) subform to
be
populated with the data from the combo box field (EmployeeName)
in
the
(employees) subform each time I access/update the main form.

Thanks for any help, Dean
 
D

DeanT

Works great . Thanks.

Ken Snell (MVP) said:
By "moved", I assume you mean you moved the record in the main form. If this
is correct, then use the main form's Current event to cause the requery of
the authors combo box (replace my generic names --
NameOfSubformControlHoldingCommentsSubform and NameOfAuthorsComboBox -- with
the real names):

Private Sub Form_Current()
Me.NameOfSubformControlHoldingCommentsSubform.Form.NameOfAuthorsComboBox.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>



DeanT said:
Almost there. The combo box worked fine but when I "moved" to a different
record the cohices from the previous record appeared in the drop down so ,
as
you suggested, I need to requery. How and where do I requery. Where do I
put
it and what is the syntax.

Thanks for your patience with a novice coder.

Ken Snell (MVP) said:
OK. Let's change the RowSource for the Authors combo box in the comments
subform to this:

SELECT Employees.EmployeeName
FROM Employees
WHERE Employees.Project_ID = [Parent].[Project_ID];

This will "tie" the combo box in the comments subform to the Project_ID
value in the main form, which I believe is what you want here? You likely
will need to cause a requery of the authors combo box when the main form
is
"moved" to a different record. But I think in your case here, the easier
thing to do will be to requery the combo box in the Enter event of the
subform control that contains the comments subform.


( Note: this next query probably would work, too, as the RowSource, but
might create an "error" at times if the combo box "requeries" before the
subform is fully populated with data, or when the subform has no data:
SELECT Employees.EmployeeName
FROM Employees
WHERE Employees.Project_ID = [Project_ID];
So I recommend that you use the first query that I list above.)


--

Ken Snell
<MS ACCESS MVP>





1- The main form (frmProjects) record source is tblProjects
2- The employee subform record source is;

SELECT Employees.Project_ID, Employees.EmployeeName, Employees.Title
FROM
Employees;

The Comments subform record source is:

SELECT Comments.Project_ID, Comments.Author, Comments.CommentDate,
Comments.Comment FROM Comments;

3-Both of the subforms are linked to the main frmProjects by the
Project_ID
field in each.

4- The row source statement in the author field is:

SELECT Employees.EmployeeName FROM Employees;


The problem is: The employee name values are different for each project
main
form. When I access the "author" field the drop-down box shows all the
"employeename" values from all projects, NOT just the values from the
frmProject I am on. When I go from project to project, the author drop
down
should give me just the employeeNames from the employee subform that is
on
the current project.

Thanks for help.

:

Please provide the following information so that we can assist you
further:

1) The SQL statement of the main form's RecordSource query.

2) The SQL statement of the Employees subform's RecordSource query.

3) The LinkChildFields and LinkMasterFields values for the subform
control
that holds the Employees subform.

4) The SQL statement for the RowSource query of the "author" combo box
in
the Comments subform.

--

Ken Snell
<MS ACCESS MVP>



The sub-Forms are continuous.
The subform called "comments" has a combo box called "author" . I
want
the
drop down to list the names that are in the Subform called
"Employees"
in
the
field "employee". Each time a the main form is accessed or the user
moves
to
the next main form, I want the drop-down box in the "Comments"
subform
to
list only the names in the sumform "employees" on that specific main
form.

:

Are the subforms "continuous view" subforms? Does "access/update
the
main
form" mean that you want the combo box in Comments subform to be
updated
whenever you open the main form? whenever you make any type of data
change
on the main form's data? What about if you make a change to the
Employees
subform -- should the combo box in Comments subform be updated
then,
too?

It may be appropriate to have combo box in Comments subform, but if
you
always want its value to be that from the Employees subform, it may
be
better if you rethink the design to either use a "locked" textbox
or
something similar?

Give us some more details and then we can assist with the best way
to
do
what you seek.
--

Ken Snell
<MS ACCESS MVP>




I have a main form (projects) with 2 subforms (employees) and
(comments)

I want the combo box field (author) in the (comments) subform to
be
populated with the data from the combo box field (EmployeeName)
in
the
(employees) subform each time I access/update the main form.

Thanks for any help, Dean
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top