Subform Combobox To Only List Specific Values

[dapoole]

Hi there,



I have an Employee's Development Objective main form , which has a subform that contains a combobox that lists all 'Job Titles' and 'Job Objectives' within the company, say such as:

IT Manager, Create Spreadsheets
IT Manager, Manage Staff
SQL DBA, Create Databases
SQL DBA, Create Spreadsheets
Managing Director, Manage Finance
Managing Director, Manage Staff

How do I get the subform combo box to ONLY display the Job Titles and Job Objectives specific to the Employees known Job Title? The combobox Row Source is presently:

SELECT qryJobObjectiveDetails.jobObjID, qryJobObjectiveDetails.jobTitle, qryJobObjectiveDetails.objective 
FROM qryJobObjectiveDetails 
ORDER BY [jobTitle], [objective];

The main form has a jobID number specific to the Employees job title.

TIA

[fredz]

1. Add a "where" clause to your rowsource query.
2. Add a combobox.requery to the OnCurrent event of your form.
Fred

[dapoole]

Thanks but how do I write the query to include the jobID from the main form? And if my combobox is called cboxJobList how do I write the combobox requery?

[fredz]

You can make both changes in the Subform OnCurrent Event with something like (caution for linewrap):
(Assuming jobObjID is a numeric field)

Me.cboxJobList.RowSource = "SELECT jobObjID, jobTitle, objective FROM qryJobObjectiveDetails WHERE jobObjID=" & Me.Parent.JobID & " ORDER BY [jobTitle], [objective];"

Me.cboxJobList.Requery

Fred

[June7]

Or can put the SQL directly in the combobox RowSource and the VBA is only needed to Requery.

SELECT jobObjID, jobTitle, objective FROM qryJobObjectiveDetails WHERE jobObjID=[Parent].[JobID] ORDER BY [jobTitle], [objective];