limit content of combo box based upon field value

[Guest]

I have an Employee Evaluation Reports form that lists the employee's personal
data, location, section, and various dates.
For ease of input, I've added a combo box for the employee's Rater and
Reviewer. I'd like to limit the names that appear in the Rater combo box
based upon the location and section of the employee. (e.g. the Rater combo
box for an employee working at CityZ in the HR section should only display
names that are either in CityZ or CityHQ who work in HR or Management.)
I can do this manually. How do I get the form to do this automatically?

 

[Jeff Boyce]

Daiuy

You'd need to have a table somewhere that connects the raters to the
locations/sections. Then you could build a query that gets only the raters
associated with a specific location/section. You'd modify that query to use
the location/section on the form (with something like:
Forms!YourForm!YourField) as a criterion, and you'd use this query as the
source for your "rater" combo box.

Finally, you'd need to add code into the AfterUpdate event of the text or
combo box you use to select the Employee's location/section. This code
would requery the "rater" combo box (with something like:
Me!cboRater.Requery).

 

[Guest]

I've been trying to do as you have suggested, but without success.
I created the query that pulls the records that match the criteria
established in the text box. This query runs fine by itself. When I use the
wizard to insert a combo box on my form, I'm asked WHICH TABLE OR QUERY
SHOULD PROVIDE THE VALUES FOR THE COMBO BOX? When I select the query, I
receive an error message stating NO VALUE GIVEN FOR ONE OR MORE REQUIRED
PARAMETERS.
I also tried to have the query make a new table and then bind the combo box
to the new table. This worked for the first try, but all successive records
showed the same names as the first - it didn't requery.

 

[Jeff Boyce]

See in-line comments...

I've been trying to do as you have suggested, but without success.
I created the query that pulls the records that match the criteria
established in the text box. This query runs fine by itself.

I'm not there, so I can't see your work. Are you saying that you created a
query that refers to the form to get it's criterion, using the Forms!...
expression?

When I use the
wizard to insert a combo box on my form, I'm asked WHICH TABLE OR QUERY
SHOULD PROVIDE THE VALUES FOR THE COMBO BOX? When I select the query, I
receive an error message stating NO VALUE GIVEN FOR ONE OR MORE REQUIRED
PARAMETERS.

The wizards can help, but they can't handle everything.

Try placing a combo box on the form again and clicking <Cancel> on the first
screen. Then open the properties of the combo box and set the Control
Source property to the query you wrote above.

I also tried to have the query make a new table and then bind the combo box
to the new table. This worked for the first try, but all successive records
showed the same names as the first - it didn't requery.

From a relational database design standpoint, you rarely need to create new
tables just to get a list. Try the above suggestions and post back with how
it's going...

Good luck

Jeff Boyce

 

[Jeffrey O'Donnell via AccessMonster.com]

Sorry for the long delay. I got pulled to do something else for a while.
To answer your in-line comments; Yes, I created a query that uses [Forms]!
[frmEER]![SectionName] as the criteria. I named this query "qryRaterCombo."
I inserted a new combo box onto the EER form as you had suggested and set
the Row Source as follows:
SELECT [qryRaterCombo].[RaterSSN], [qryRaterCombo].[RaterName],
[qryRaterCombo].[PostName], [qryRaterCombo].[SectionName] FROM
qryRaterCombo WHERE ((([qryRaterCombo].[SectionName])=[Forms]![frmEER]!
[SectionName])) ORDER BY [qryRaterCombo].[RaterName];
I set the control source to RaterSSN.
I tried to add code to the AfterUpdate event of the SectionName text box on
the form but this wasn't working well. I did add the Me!CboxRater.Requery
code to the FormCurrent event and it's working fine. Admitedly, I'm not
familiar with either of these events, so I'm not sure of any ill effects
that may result by using FormCurrent vs AfterUpdate.
So, things appear to be working fine. However, as it is currently written,
the Rater combo box only shows potential raters who are assigned to the
same section as the employee. For the majority of employees that's fine.
My problem is with Section Heads. Their raters are in different sections.
This problem will arise again as I get to Reviewers. How do I include
additional sections in the query. It's not so clean as to simply add "or
MGT, or EXEC" after the criteria [Forms]![frmEER]![SectionName]. Although
I believe I can probaly make three distinct queries to capture all the
relevant "higher-up" sections. How would I have the application evaluate
the employee's Section text box and say, "If the employee's SectionName =
SectionA, SectionB, or SectionC, run QueryX; If the employee's SectionName
= SectionD, SectionE, or SectionF, run QueryY; If the employee's
SectionName = SectionG, SectionH, or SectionJ, run QueryZ?