Populate a combo box on one form from a text box on another form

[rebeccabarker]

I have two forms - Plan Form and Employer Form. The plan form has a button that when clicked opens the Employer form for update. From the employer form, the user can change the information on this particular employer (i.e. address, phone number). On the employer form I have a combo box that is linked to my employer table and displays all the possible employers to choose from. (In case the user needs to change the employer associated with this particular plan.)

Here's my problem: If the plan on the plan form currently has an employer associated with it, I want the employer form when opened to display the current employers info. I am using the following code:

'Populate all the fields on the form
Me.cmbEmployer = Forms![Current and Prior Plans Revised Test]![txtEmployer]
Me.txtEIN = Forms![Current and Prior Plans Revised Test]![txtEmpEIN]
Me.txtStreet = Forms![Current and Prior Plans Revised Test]![txtEmpStreet]
Me.txtCity = Forms![Current and Prior Plans Revised Test]![txtEmpCity]
Me.txtState = Forms![Current and Prior Plans Revised Test]![txtEmpState]
Me.txtZip = Forms![Current and Prior Plans Revised Test]![txtEmpZip]
Me.txtPhoneNum = Forms![Current and Prior Plans Revised Test]![txtEmpPhone]
Me.txtFaxNo = Forms![Current and Prior Plans Revised Test]![txtEmpFax]

All of the fields work EXCEPT the combo box. I do not get an error; the combo box is just blank.

Any ideas on what I am missing?

Thanks,
Rebecca

 

[Darrylles]

Hi,

This depends on your tables.

Is your tblEmployer primary key the Employer name or is there a different ID field?

Also remember, that you are not 'populating' the combo control, you are attempting to change it's selection after it has been 'populated' with a list of possible options.

E.g. it's recordsource should be 'tblEmployer' BEFORE you run the above code, or there is nothing in it to display in order for it to be equal to the other form's txtEmployer value.

Hope this makes sense.

Regards,

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[rebeccabarker]

The primary key field is an autonumber field called Empid. When a new employer is added (a different form) I save the employer information to tblEmployer first and then get the empid from tblEmployer and save it with the associated plan in tblPlan.

Forgive my poor choice of words, as I realize I am not populating the combo box. When I initially created the combo box on the form I followed the wizard and set the rowsource to the following query: SELECT [TBLEMPR].[EMPID], [TBLEMPR].[EMPLOYER] FROM TBLEMPR ORDER BY [TBLEMPR].[EMPLOYER]; So when I open the form the combo box is filled with all the employers from tblEmployer. I still want the combo box to have all of the employers in it, I just want it to display the one from the plan form instead of being blank initially.

I am running the code from my original post in the Form_Load subroutine. Is there a better place to do this?