Changing the data source of a form

[PortyAL]

Hi

I am trying to change the data source of a form via code. The form I which to change is called "template" and the query name I wish to change the data source to is the same as the value in a field in a visible form i.e. Forms![frm Recs tracked jobs]![Job]. (A query of that name was created by a previous routine).

I tried the following code but it gave an error "Compile error: Invalid use of Me keyword".

Sub Opentemplate_click()

Dim QueryName As String

DoCmd.OpenForm "template", acNormal, "", "", , acNormal
QueryName = Forms![frm Recs tracked jobs]![Job]
Me.RecordSource = "QueryName"
End Sub

Any advice would be greatly appreciated.

Thanks

AL

 

[Dennisk]

try placing the code to determine the recordsource in the on open event of the template form.
PS. open the template form as a dialog

 

[boblarson]

DoCmd.OpenForm "template", acNormal, "", "", , acNormal
QueryName = Forms![frm Recs tracked jobs]![Job]
Me.RecordSource = "QueryName"
End Sub

What you are using Me for doesn't make sense as the record source of "template' is what you are trying to set, but you can't use Me to refer to an object outside the scope of the form your code is on.

You would need to explicitly say:

DoCmd.OpenForm "template", acNormal, "", "", , acNormal
   QueryName = Forms![frm Recs tracked jobs]![Job]
      Forms!template.RecordSource = "QueryName"
      Forms!template.Requery

 

[PortyAL]

Thanks Bob & Dennis

Got it working.

I need another bit of help. After I have changed the data source as above, I want to save the "template" form as a data access page. Is there any code to do this and bring me directly to the file location dialog or do I have to use the DoCmd.RunCommand acCmdSaveAs command and then choose data access page from the dialog box?

Many thanks

Alan

 

[boblarson]

I don't use data access pages, so I am not sure about that one. You might want to create a separate post for that question.

 

[PortyAL]

OK Thanks!