Datasheet Subform: trying to get combo box to populate another field 1

[fazm]

I have a form called frmClientTracking, within this form is subform called sfrmService.

The subform (datasheet view) consists of ServiceCd and ServiceNm, etc. ServiceCd is a pulldown list populated from another table via the row source control, when a certain number is selected, I want to populate the ServiceNm field with designated text from my VBA code.

Here is the code I have:

ServiceCd_OnClick()

Select case Forms!frmClientTracking!sfrmService.Form!ServiceCd

case 242
Forms!frmClientTracking!sfrmService.Form!ServiceNm = "No Activity"
case 252
Forms!frmClientTracking!sfrmService.Form!ServiceNm = "YMCA"
case 262
Forms!frmClientTracking!sfrmService.Form!ServiceNm = "MCO Medical Center"
'and so on

End Select

Get error saying cant find sfrmService in my expression. Is it possible to do this in datasheet view of a subform. It seems straight forword, but I cant seem to get it to work.

Any ideas would be a great help.

Thanks,

 

[Eupher]

fazm,
Are you sure that "sfrmService" is the name of the subform control on your main form? Remember, the name of the form and the name of the control are not the same thing, and you must refer to the control in your expression.

HTH,

Ken S.

 

[fazm]

Ken -

Thanks for the quick response. I do know that sfrmService is the name of my subform and the control would be the text box called ServiceNm on that subform.

It looks like I am not getting the syntax correct. Could you provide me a sample to identify my control (textbox called ServiceNm) on the subform.

I looked under help in Access and get no clear explanation. Originally, I thought it would be:

Forms!frmClientTracking!sfrmService.ServiceNm

which identifies the form, subform, then the control on that subform, but it didnt work, then looked in help and got the example from above.

I must have exclamation or period in the wrong place......

Thanks,

 

[Eupher]

fazm,
That's not what I'm getting at. When you place a subform on your form, you are placing a subform control which has its own name. The name of the subform control will not necessarily be the same as the name of the subform which is contained in the subform control. When referring to the subform, you must refer to the name of the subform control, i.e.:

Me!subformControlName.Form!fieldName

Here's a link with more info about the syntax for referring to subform controls and such:

http://www.mvps.org/access/forms/frm0031.htm

HTH,

Ken S.

 

[fazm]

Thanks Ken for all your help. Got it to work. The actual subform control was renamed and I didnt catch it.

I kept looking at the subform properties and the name of the subform itself, not the actual subform control itself when selcting it in design mode. So I was never getting the name of the subform control itself.

Thanks again!!