Reference a subform from main form Error

Question

Must be losing my mind. For some reason I can't get this simple procedure to work. It's a pulldown where user chooses what data they would like to see. I just need it to change the recordsource of the subform and refresh it. It's basically just substituting one filtered query for another. Can't seem to access the subform through main form. Not sure if this is the best way, but it's the way I know.

Private Sub PeriodSelect_Change()
If PeriodSelect.Value = "Active" Then
Me!ServiceWindow.SbfmService_Item.RecordSource = Service_Active
ServiceWindow.Requery

Else
If PeriodSelect.Value = "PDI" Then
Me!ServiceWindow.SbfmService_Item.RecordSource = Service_PDI
ServiceWindow.Requery

End If
End If
End Sub

I get Error 438 Object does not support this property or Method. Can't quite figure out what I've missed.

Any help is greatly appreciated.

Just checking low hanging fruit, but you do have the ServiceWindow form open when you're doing this correct? Is the subform open/visible/active as well? — Steve
Yes. All active. Subform is open to and as a default the recordsource is "Service_Active". Data is visible. When you choose "PDI" from the pulldown menu, the error appears. — NewbieVBA
I'm pretty sure you just need double quotes around your queries where you're assigning the recordsource. Me!ServiceWindow.SbfmService_Item.RecordSource = "Service_PDI" — Steve

Erik A Erik A · Accepted Answer · 2018-04-13T15:24:25

You're trying to set the RecordSource on the subform control, not the form inside the subform control. Use the Form property to access that form:

Private Sub PeriodSelect_Change()
If PeriodSelect.Value = "Active" Then
Me!ServiceWindow.SbfmService_Item.Form.RecordSource = "Service_Active"
ServiceWindow.Requery

Else
If PeriodSelect.Value = "PDI" Then
Me!ServiceWindow.SbfmService_Item.Form.RecordSource = "Service_PDI"
ServiceWindow.Requery

End If
End If
End Sub

Reference a subform from main form Error

1 Answers