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
