show data from a table based upon combo box

[Guest]

I'm trying to automate some input on a form.
The purpose is to track employee evaluation reports. Currently, all
information must be typed into the form. The fields include the employee's
personal data (e.g. LastName, FirstName, MiddleInitial, etc) their Location,
Department, Section, and various dates.
We must also type in the Rater’s Name, the Rater’s Department and Section,
and the Reviewer’s Name.
I was successful in creating combo boxes on the form for Location,
Department, and Section as these are standard. I also created a
make-new-table query that combines LastName, FirstName, and MiddleInitial
into one field (FullName) and collects Location, Department and Section from
the employee database. This new table is named tblRaters
The FullName field from tblRaters then populates a combo box on the form
under the title RATER.
This is where my questions start.
Obviously, I don’t need the entire company's roster to appear in the RATER
list for an employee who works in the Financial Management Department at
Headquarters. I need the list to show only those employees at Headquarters
who are either in the Financial Management or Executive Departments, as only
someone from these sections will rate an employee in the Financial Management
Department.
So, I’d like to limit the combo box to show only the full names of people
assigned to certain sections and who are at certain locations based upon the
Department and Location of the employee.
Next, once I select the appropriate name from the RATER combo box, I’d like
the Rater’s Department and the Rater’s Section to be filled automatically and
immediately from tblRaters.
My experience level is very low, please keep it simple.

 

[Ed Robichaud]

Add a [location] and/or [dept] field to your tblEmployees. For the RATER
combo box, use a query as its control source that is based on the
tblEmployees with the criteria in the Dept column set to "Finance" or
"executive".
-Ed

 

[Guest]

Ed,
Let me start by saying that I really appreciate your quick response.
For the past few minutes, I've been doing and testing that which you had
suggested. Again, my experience level is low and slow.
All seems to be working fine, but its not quite where I want to be yet.
Can Access automatically capture the Location and Department for a specific
employee and plug it into the query that generates the combo box?
What I mean is: On my form, I'm inputing the data for a particular
employee. I get to the combo box that allows me to select the Location where
this particular employee works and the next combo box allows me to select the
Department where this particular employee works. The next combo box is the
Rater Combo Box which is generated from the query as you had suggested. I
don't want to exit the form, open the query, type in this particular
employee's Location and Department in the criteria, then return to the form.
Is there someway to do this automatically?
Also, I still can't seem to figure out how to display the Rater's Location
and Department once I select the correct rater from the combo box.
Thank you,
Daiuy

Add a [location] and/or [dept] field to your tblEmployees. For the RATER
combo box, use a query as its control source that is based on the
tblEmployees with the criteria in the Dept column set to "Finance" or
"executive".
-Ed

I'm trying to automate some input on a form.
The purpose is to track employee evaluation reports. Currently, all
information must be typed into the form. The fields include the
employee's
personal data (e.g. LastName, FirstName, MiddleInitial, etc) their
Location,
Department, Section, and various dates.
We must also type in the Rater's Name, the Rater's Department and Section,
and the Reviewer's Name.
I was successful in creating combo boxes on the form for Location,
Department, and Section as these are standard. I also created a
make-new-table query that combines LastName, FirstName, and MiddleInitial
into one field (FullName) and collects Location, Department and Section
from
the employee database. This new table is named tblRaters
The FullName field from tblRaters then populates a combo box on the form
under the title RATER.
This is where my questions start.
Obviously, I don't need the entire company's roster to appear in the RATER
list for an employee who works in the Financial Management Department at
Headquarters. I need the list to show only those employees at
Headquarters
who are either in the Financial Management or Executive Departments, as
only
someone from these sections will rate an employee in the Financial
Management
Department.
So, I'd like to limit the combo box to show only the full names of people
assigned to certain sections and who are at certain locations based upon
the
Department and Location of the employee.
Next, once I select the appropriate name from the RATER combo box, I'd
like
the Rater's Department and the Rater's Section to be filled automatically
and
immediately from tblRaters.
My experience level is very low, please keep it simple.

 

[Ed Robichaud]

If I understand what you want, the control source of your RATER combo box is
now a query that includes the fields [empName], [empLocation], [empDept],
etc. This query will run automatically every time the drop-down arrow of
the combo box is activated.

If you want to include the employee info of the current form in the RATER
combo box drop-down list, put a line of code in the OnEnter event of that
combo box:
"DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD"

To display the RATER Loc & Dept in the combo box drop down list, include
those columns in your query and order them left to right as you want them to
appear (i.e. empID, empLastName, empLoc, empDept). Set the combo box
properties to show (in the example above) 4 columns, bound column=1, columns
widths something like 0.3,1,1,1 and total width 3.3 You should aslo set
the sort order in the query, probably Asc by empName.

I do hope that you have an employeeID field and are using it for the primary
key in your table. You could also add an autonumber field and let it
generate a unique number for you for each employee. Either one is the only
reliable way of selecting a specific employee.

-Ed

Ed,
Let me start by saying that I really appreciate your quick response.
For the past few minutes, I've been doing and testing that which you had
suggested. Again, my experience level is low and slow.
All seems to be working fine, but its not quite where I want to be yet.
Can Access automatically capture the Location and Department for a
specific
employee and plug it into the query that generates the combo box?
What I mean is: On my form, I'm inputing the data for a particular
employee. I get to the combo box that allows me to select the Location
where
this particular employee works and the next combo box allows me to select
the
Department where this particular employee works. The next combo box is
the
Rater Combo Box which is generated from the query as you had suggested. I
don't want to exit the form, open the query, type in this particular
employee's Location and Department in the criteria, then return to the
form.
Is there someway to do this automatically?
Also, I still can't seem to figure out how to display the Rater's Location
and Department once I select the correct rater from the combo box.
Thank you,
Daiuy

Add a [location] and/or [dept] field to your tblEmployees. For the RATER
combo box, use a query as its control source that is based on the
tblEmployees with the criteria in the Dept column set to "Finance" or
"executive".
-Ed

I'm trying to automate some input on a form.
The purpose is to track employee evaluation reports. Currently, all
information must be typed into the form. The fields include the
employee's
personal data (e.g. LastName, FirstName, MiddleInitial, etc) their
Location,
Department, Section, and various dates.
We must also type in the Rater's Name, the Rater's Department and
Section,
and the Reviewer's Name.
I was successful in creating combo boxes on the form for Location,
Department, and Section as these are standard. I also created a
make-new-table query that combines LastName, FirstName, and
MiddleInitial
into one field (FullName) and collects Location, Department and Section
from
the employee database. This new table is named tblRaters
The FullName field from tblRaters then populates a combo box on the
form
under the title RATER.
This is where my questions start.
Obviously, I don't need the entire company's roster to appear in the
RATER
list for an employee who works in the Financial Management Department
at
Headquarters. I need the list to show only those employees at
Headquarters
who are either in the Financial Management or Executive Departments, as
only
someone from these sections will rate an employee in the Financial
Management
Department.
So, I'd like to limit the combo box to show only the full names of
people
assigned to certain sections and who are at certain locations based
upon
the
Department and Location of the employee.
Next, once I select the appropriate name from the RATER combo box, I'd
like
the Rater's Department and the Rater's Section to be filled
automatically
and
immediately from tblRaters.
My experience level is very low, please keep it simple.