Change Record Source in VBA (1 Viewer)

[LB79]

Hello,

I'm having problems changing my form record source in VBA. Ive tried a few variations including the below.
Should this be an easy thing?

Thanks for any help..

[Forms]![ParentForm]![Parent_subfrm].[Subform].RecordSource = MySQL
Error = Object doesn’t support this property or method


Form.ParentForm.RecordSource = MySQL
Error = Object Required

 

[jzwp22]

I'm not sure from where you are trying to execute the code, but for example, if you want to change the record source for a subform from an event tied to a control on the main form, the code would look something like this:

Dim mySQL As String
mySQL = "SELECT * from tblEmpPhone where mid(txtphone,4,3)='444'"
Me!frmEmpPhone.Form.RecordSource = mySQL
Me.frmEmpPhone.Requery

frmEmpPhone is a subform and I am executing the code from an event tied to a control on the main employee form.

 

[LB79]

Hi and thanks for the message.
Im running this code in a module. Would that be coded differently?

 

[jzwp22]

Do you mean a stand alone procedure that is separate from the form for which you are changing the records source? Does your code also open the form?

You will have to put the full reference to the form (rather than just using me!). Maybe something along these lines:

Public Sub myopenform()

Dim mySQL As String
mySQL = "SELECT * from tblEmpPhone where mid(txtphone,4,3)='444'"
DoCmd.OpenForm "frmEmp", acDesign
Forms!frmEmp.frmEmpPhone.Form.RecordSource = mySQL
DoCmd.Close acForm, "frmEmp", acSaveYes
DoCmd.OpenForm "frmEmp", acNormal

End Sub

 

[LB79]

Thanks.

I wasn’t using the Me reference.

It is standalone and not in a form module.
It opens a form that contains a subform (which is OK), then I want it to change the record source.
I've tried to change your example to my data but its debugging out for me.

My parent for is "frmMain”
My subform is “frmSub” (this is where I want to change the record source.

I thought perhaps Forms!frmMain.frmSub.Form.RecordSource = MySQLbased on your example?

 

[jzwp22]

The following you provided looks correct:

Forms!frmMain.frmSub.Form.RecordSource = MySQL

From what I have done before, you have to open the form in design view, change the record source, save and close the form and then reopen it.

 

[LB79]

Thanks - ill give that a go and see if that helps

 

[gemma-the-husky]

perhaps its the syntax

in the current form you need

me.RecordSource = "queryname"

or

me.RecordSource = variable

you can set these in the open oevent (may need to be the load event)

possibly you cant set them for another unopened form.