MS Access VBA - display dynamically built SQL results in datasheet subform

Question

I have several years experience with VBA in MS Office applications (for automation and ETL processes) but have not had the need to mess with Forms in MS Access until recently. I'm laying out the design for some simple data extraction forms for a database I have designed and am hung up on what seems to be a simple task.

Objective: I need a datasheet subform to display the records returned from a dynamically built SQL statement from controls on the main form.

On my main form, I have a button that when a user clicks it the button will compile the information specified by the user in other user form controls into a SQL query, and then run that query so that a subform displays the resulting records.

No matter what I do, I cannot get this to work. I keep getting (most of the time anyway) the microsoft visual basic run-time error "'2467': The expression you entered refers to an object that is closed or doesn't exist." That's the error I get with the code shown below. I can't figure out if I somehow need to initiate the subform as soon as any code gets run or what. I've tried some other variations of code that have also not worked from other code forums, but I seem to have found several forum threads including some on Stack Overflow that suggest the code I have below should work.

The attached image shows what the basic main form looks like. I have labeled the button that the user would click (btnDisplaySWData) to compile the SQL that gets create from yet-to-be-included controls, but that is not the issue. I'm just hard-coding a SQL statement as shown in the code snippet in trying to figure out this issue. As mentioned I want the records to display in the subform named dataDisplaySubform. "JUNK" is a table in the Access database that I can legitimately query with the SQL code below that I am just using for testing purposes until I figure this out. All the code in the data form shown (named frmDataExtract) consists of what is in the code window below.

Option Compare Database
Option Explicit
Public Sub btnDisplaySWData_Click()
    Dim pSQL As String
    pSQL = "SELECT JUNK.agency_ID, JUNK.agency_desc FROM JUNK"
    Me.dataDisplaySubform.Form.RecordSource = pSQL
End Sub

The form is named dataDisplaySubform, as shown in the below screenshot of the properties with the subform selected.

This is what the overall form layout looks like

I have scoured several forum sites and also have tried every variation of terms with searching Stack Overflow to find potential solutions for my issue, but none have worked even when the original thread was marked solved by the person who posted it. I've spent way too much time, about 2 workdays, trying to figure out what I am doing wrong and have not yet been able to.

I appreciate anyone that can help steer me in the right direction, this is driving me mad.

thanks, --TB

SOLUTION EDIT BY TURKISHGOLD

Well I think I figured it out on my own though HansUp helped lead me down the path with mention of the subform Source Object not having anything assigned to it. In my case, assigning the Source Object to a form was not the correct solution which is what HansUp was suggesting. Instead a saved query seems to get it to do what I want.

Not sure if there is a better way to do this, but it seems like you need to set up a dummy, almost placeholder query, so you can set the subform Source Object to it in VBA. A placeholder query like this:

SELECT * FROM JUNK WHERE JUNK.agency_ID ="_";

The above Access query is saved as the name "TESTQUERY". It doesn't display anything, but satisfies the need to have Source Object assigned to something, essentially instantiating the subform when looking at the main form in form view. So, with the placeholder saved query, you can then reassign the RecordSource to whatever SQL String is put together via user interface controls on the main form, like this:

Public Sub btnDisplaySWData_Click()
    Dim pSQL As String
    pSQL = "SELECT JUNK.agency_ID, JUNK.agency_desc FROM JUNK"
    Me.dataDisplaySubform.SourceObject = "Query.TESTQUERY"
    Me.dataDisplaySubform.Form.RecordSource = pSQL
    Me.dataDisplaySubform.Requery
End Sub

which when the Form is in production, the shown hard-coded SQL statement stored in the pSQL string variable will be put together via user input on controls on the main form.

So now, when the btnDisplaySWData is clicked, it does what I was trying to do and displays records.

Answer 1

short and sweet. Here is the code for a button that creates dynamic sql string, closes the current object (just in case its open), deletes a temporary query definition (because we need one), creates a new query definition with the new sql, changes the recordsource and Bob's your uncle.

Private Sub btnRunSQL_Click()
  'my subform is called datasheet, i know -- dumb name.
  'a dynamic sql needs to be saved in a temporoary query. I called my qtemp
  Dim sql As String
  sql = "select * from client order by casename asc"
  'in case there is something kicking around, remove it first, otherwise we can't delete the temp query if it is still open
  Me!Datasheet.SourceObject = ""
  'delete our temporary query. Note, add some err checking in case it doesn't exist, you can do that on your own.
   DoCmd.DeleteObject acQuery, "qtemp"
  'lets create a new temporary query
  Dim qdf As QueryDef

  Set qdf = CurrentDb.CreateQueryDef("qtemp", sql)
  'set the subform source object
  Me!Datasheet.SourceObject = "query.qtemp"
  'and it should work.
End Sub

Answer 2

Use a CreateQueryDef and then
Me.dataDisplaySubform.SourceObject = "Query.NewqueryName"
NewQueryName is the name given when created using createQueryDef