ComboBox in Form AND SubForm

[Mike112]

Hi everybody,

I'm new here and please forgive me if something similar has been answered previously.

Basically, I have 2 tables (Student and Employee), and a main form containing a sub form as well. I have a combo box in the main form as well as the sub form and what I am trying to do is have the subform's combobox update given a selection has been made in the main combo box.

For example:

Main Form: "frmMain" contains a label 'EmployerID' that gets assigned when a value from combo box 'comboEmployerID' is selected.
frmMain also contains a sub form named 'frmSubForm' which has a combo box named 'comboStudent' that queries for students who have an EmployerID of the selected value of 'comboEmployerID' (or simply EmployerID's label value).



The problem I am having is once I select a 'EmployerID' (the first time works fine by the way, but after that), the options in 'comboStudent' aren't refreshing. I have tried playing around with the AfterUpdate function of 'comboEmployerID' but haven't had any luck. I'm assuming it is something simple, however it is driving me crazy. Hopefully my explanation is clear.

- Mike

[June7]

What is purpose of this form/subform? Is to create records that associate a student with employer? If the student combobox list is restricted to students already assigned to employer, how do you create new associations?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Mike112]

Hi June7,

Thanks for the reply, and sorry for me getting back to you so late.

Basically, In the main form, it will allow the user to type in a new Employer (providing all of the information), as well as allow the user to select a pre-existing Employer from ComboEmployer. There will also be a button on frmMainForm to allow the user to submit (or "create") the new Employer to the tblEmployer.

In the subform, the user will similarly be able to create a new/edit an existing Student in the same fashion as in the frmMainForm, but also can select from ComboStudent (which SHOULD display students only associated with the EmployerID that is selected above).

I'm sorry if this is confusing. It's even confusing myself trying to explain it, haha.

[June7]

I don't use query objects when I don't have to.

RowSource for Student combobox can be SQL statement. The RowSource needs the StudentID field, not EmployerID.

SELECT StudentID, StudentLastName & ", " & StudentFirstName FROM tblStudent WHERE EmployerID=[Forms]![frmMain]![EmployerID] ORDER BY StudentLastName, StudentFirstName;

ColumnCount: 2
ColumnWidths: 0";2.0"

Also need code to requery the student combobox. This can be behind the employer combobox AfterUpdate event or the student combobox GotFocus event.

[Mike112]

Thank you once again for the reply.

I'm pretty new to SQL statements, but can grasp what that's doing. I'm able to just put that into the row source by creating the expression, correct?

And as far as the VBA, I had played around with some suggestions I found online and they mentioned something similar to:

Me.frmSubForm.Form.Requery

I hadn't got it working, but that may be because I didn't have a correct RowSource?

- Mike

[June7]

Requery the form will not requery the combobox RowSource.

The requery must be for the combobox specifically. If you put it in the student combobox GotFocus event, VBA would be:

Me.Combo167.Requery

You will have to figure out the macro equivalent.

[Mike112]

Ahh June, thank you so much!! I managed to figure out the missing pieces to my problem thanks to your help!

I didn't realize that requerying a form wouldn't requery everything inside said form. I also didn't think about the GotFocus event.

- Mike