combo boxes and queries

[Evert]

I have created 2 combo boxes in a form. the first combo box runs a query that
gives me the choices for the second combo box.
The query that I have have is as follows:

SELECT TblDepartment.Department, [Tbl Job Positions].[Job Position]
FROM TblDepartment INNER JOIN [Tbl Job Positions] ON
TblDepartment.[Department ID] = [Tbl Job Positions].[Department ID]
WHERE (((TblDepartment.Department)=[Forms]![Personnel MIV]![Department ID]));

My dilema is that, when I select a department for the first time after
opening the form, it works as advertised. However, if I need to change the
department due to a transfer or what have you, the second combo box will not
give the appropriate Job Positions.
As a matter of info the tables that are driving the query have a one to many
relationship.
I need to have the Job Position combo box update every time I change the
selection on the department combo Box wihtout having to go in and out of the
form.

please help

Evert

 

[Mr B]

Evert,

You do not indicate if you have any VBA code that is being run after you
change the selection in the Department combo box.

You need to have code in the AfterUpdate event of the Department combo box
that will refresh the "Job Positions" combo box.

If you will place the following line in the AfterUpdate event of your
Department Combo box that shoud solve the problem:

Me.NameOfJobPositionComboBox.Requery

Be sure to change the "NameOfJobPositionComboBox" to the actual name of your
second combo box.

If you are not sure how to use that line of code, post back here and I or
someone else will be more that glad to help.

 

[Evert]

Thank you so Mr. B
This really worked!!

Evert,

You do not indicate if you have any VBA code that is being run after you
change the selection in the Department combo box.

You need to have code in the AfterUpdate event of the Department combo box
that will refresh the "Job Positions" combo box.

If you will place the following line in the AfterUpdate event of your
Department Combo box that shoud solve the problem:

Me.NameOfJobPositionComboBox.Requery

Be sure to change the "NameOfJobPositionComboBox" to the actual name of your
second combo box.

If you are not sure how to use that line of code, post back here and I or
someone else will be more that glad to help.

--
HTH

Mr B
askdoctoraccess dot com

I have created 2 combo boxes in a form. the first combo box runs a query that
gives me the choices for the second combo box.
The query that I have have is as follows:

SELECT TblDepartment.Department, [Tbl Job Positions].[Job Position]
FROM TblDepartment INNER JOIN [Tbl Job Positions] ON
TblDepartment.[Department ID] = [Tbl Job Positions].[Department ID]
WHERE (((TblDepartment.Department)=[Forms]![Personnel MIV]![Department ID]));

My dilema is that, when I select a department for the first time after
opening the form, it works as advertised. However, if I need to change the
department due to a transfer or what have you, the second combo box will not
give the appropriate Job Positions.
As a matter of info the tables that are driving the query have a one to many
relationship.
I need to have the Job Position combo box update every time I change the
selection on the department combo Box wihtout having to go in and out of the
form.

please help

Evert

 

[Mr B]

Very glad to help.

--
HTH

Mr B
askdoctoraccess dot com

Thank you so Mr. B
This really worked!!

Evert,

You do not indicate if you have any VBA code that is being run after you
change the selection in the Department combo box.

You need to have code in the AfterUpdate event of the Department combo box
that will refresh the "Job Positions" combo box.

If you will place the following line in the AfterUpdate event of your
Department Combo box that shoud solve the problem:

Me.NameOfJobPositionComboBox.Requery

Be sure to change the "NameOfJobPositionComboBox" to the actual name of your
second combo box.

If you are not sure how to use that line of code, post back here and I or
someone else will be more that glad to help.

--
HTH

Mr B
askdoctoraccess dot com

I have created 2 combo boxes in a form. the first combo box runs a query that
gives me the choices for the second combo box.
The query that I have have is as follows:

SELECT TblDepartment.Department, [Tbl Job Positions].[Job Position]
FROM TblDepartment INNER JOIN [Tbl Job Positions] ON
TblDepartment.[Department ID] = [Tbl Job Positions].[Department ID]
WHERE (((TblDepartment.Department)=[Forms]![Personnel MIV]![Department ID]));

My dilema is that, when I select a department for the first time after
opening the form, it works as advertised. However, if I need to change the
department due to a transfer or what have you, the second combo box will not
give the appropriate Job Positions.
As a matter of info the tables that are driving the query have a one to many
relationship.
I need to have the Job Position combo box update every time I change the
selection on the department combo Box wihtout having to go in and out of the
form.

please help

Evert