Sub Form Combo Box Row Source

[LarryDeLaruelle]

I have set up a database for our HR department to track job applicants. The main form captures the basic identifier and locator information. Since an applicant can apply for more than one position with the same application, I have set up a sub form to identify the position/s the applicant is applying for. We have several corporate entities within our company and our list of job positions is over 100. Some job positions are generic and apply to all entities while some job positions are only applicable to specific entities.

I am trying to set up a row source property as an SQL string for the position combo box on the sub form to display only those positions appropriate to the corporate entity selection (from a list box) the user makes on the main form. Based on the choice the user makes for corporate entity, I set up a Select Case statement to create the SQL string and then set the row source for the combo box to that string.

Main Form is frmApplicant

Sub Form is frmSubPositions

Sub Form Combo box is cboPosition

My problem is that I haven’t been able to figure out the syntax for referencing the combo box on the sub form to set the row source. I’ve tried:

FrmSubPositions.cboPositions.RowSource = strSQL and get a Compile Error “Method or data member not found”;

Forms!frmSubPositions.cboPosition.RowSource = strSQL and get a Run Time Error “2450”, “ . . . can’t find the form ‘frmSubPositions’ referred to . . .”;

And various other combinations.

Any help would be appreciated.

Thanks.

Larry De Laruelle
larry1de@yahoo.com

 

[ghubbell]

Hi Larry,
Typically like:
Forms![NameOfMain]![NameOfSubAsSeenByTheMain].Form![TheFieldName] Gord
ghubbell@total.net

 

[RickSpr]

Gord is correct. The correct syntax for referring to a control or property of a subform is:
Forms!MainFormName!SubFormControl.Form!ControlOrPropertyName

A form that's being used as a subform is not in the Forms collection, and it can't be accessed by the form name. To access it, you have to use the Form property of the subform control that you placed on the main form.
Rick Sprague

 

[Quehay]

There is a very thorough info offering by Keri Hardwick in the "Forms" link at (this is an excellent tips page--a source for the killer work days routine by Ken Getz):

http://www.mvps.org/access/

 

[LarryDeLaruelle]

Gord and Rick:

Thanks both. I really liked the layout of Getz's instructions. I printed it out and will keep it as a handy reference. I've known about that site for some time but usually forget to use it. Tek Tips has really proven to be a great resource for expert help and this is what I usually go to first.

The syntax that I got to work is:

Me!frmSubPositions.Form!cboposition.RowSource = strSQL

Still seems convoluted to me but since it works . . .

Thanks again.

Larry De Laruelle
larry1de@yahoo.com