Update Subform Recordsource

[mystifier]

There are lots of threads on subforms and I have seen http://www.mvps.org/access/forms/frm0031.htm as a result, but frustratingly, I can't get something really simple to work.



If I have a form 'frmAdHoc' with a subform 'subAdHoc', a textbox 'txtSQL' and a button 'cmdApply'

So I want something like:

Private Sub cmdApply_Click()
Me!subAdHoc.Form.RecordSource = Me!txtSQL.Text
Me!subAdHoc.Form.Requery
End Sub

(which returns: You can't reference a property or method for a control unless the control has the focus).

How hard can it be

[RuralGuy]

Use the .Value property instead of the .Text property.
Me!subAdHoc.Form.RecordSource = Me!txtSQL.Value
...instead of...
Me!subAdHoc.Form.RecordSource = Me!txtSQL.Text
FYI:
Since the .Value property is the default property of a control
Me!txtSQL
...is the same as...
Me!txtSQL.Value
.
Another thing: you get an automatic Requery when you change the RecordSource so:
Me!subAdHoc.Form.Requery
...is not necessary and in fact is redundant.

[mystifier]

Thanks RuralGuy,

Now I have a more fundamental:

If I have a qryCustomer as 'Select * From Customer' and I build a (sub)form shown as a datagrid, it creates textboxes bound to each field.

If I change the recordsource to 'Select * From Customer Where Firstname = "John"'; everything is fine because it uses the same fields.

If I change the recordsource to 'Select * From [Order]'; it falls over because there are no valid fields.

So, any pointers on how to have a general purpose datagrid (in Access Runtime) where the Recordsource can be set to any desired query?

[RuralGuy]

Have you looked at just running a dynamic query rather than making it a SubForm?

[mystifier]

I am afraid that I am completely green RuralGuy.

Is there a simple way to open a recordset along the lines:

Private Sub cmdQuery_Click()
qryAdHoc.RecordSource = "Select * From Customer"
DoCmd.OpenQuery ("qryAdHoc")
End Sub

The idea of trying to use a subform was that the full process will be:
- Click a button to programmatically create SQL and display it in datagrid.
- If it seems to make sense, click a button to create an export file.

[RuralGuy]

Try:

Private Sub cmdQuery_Click()
   Dim MySQL As String
   MySQL = "Select * From Customer"
   DoCmd.OpenQuery MySQL
End Sub

[mystifier]

I tried exactly that, but it reported Can't find the object 'select * from customer'

Does there need to be a recordset in there somewhere? I also need to get hold of something to export with ExportWithFormatting.

[RuralGuy]

Okay, I'm all wet here. I don't use this so I will have to take a stab at it. You will probably need to define a DAO.QueryDef and then open the query in order to do what you want dynamically for the user.

[mystifier]

Thanks for the pointers.

I have the following which keeps on dropping the created QueryDef which is not ideal but at least gets me over the hurdle:


Private Sub cmdQuery_Click()
Dim sqlQuery As String
Dim sqlDrop As String
Dim qryDef As Object

sqlDrop = "Drop Table qryAdHoc"
On Error Resume Next
CurrentDb.Execute sqlDrop, dbFailOnError

sqlQuery = Me!txtSQL.Value
Set qryDef = CurrentDb.CreateQueryDef("qryAdHoc", sqlQuery)
DoCmd.OpenQuery "qryAdHoc"
End Sub