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.

Fredericfrederica

Name of sub form has NOTHING to do with the form to display. Screen shot CLEARLY shows no form set for the sub form control. A sub form control can be placed on a form, but it will not display any form UNTIL you set the source object property to a legitimate (existing) form. Without a form to display, then no data source can be set. So the fact of you having the sub form control name correct is moot until such time you choose a form from the drop down list you see in the source object property. There is no need to use or set a dummy query here. You must set source object property = legal form. – Antipyretic

Answer 1

If the "object that is closed or doesn't exist" error occurs on the Me.dataDisplaySubform.Form.RecordSource line, chances are your subform control is not named dataDisplaySubform.

You can examine the names of all your form's subform controls with this temporary change to your code ...

'Me.dataDisplaySubform.Form.RecordSource = pSQL
Dim ctl As Control
For Each ctl In Me.Controls
    If TypeName(ctl) = "SubForm" Then
        Debug.Print ctl.Name, TypeName(ctl)
    End If
Next
Stop

The Stop statement will trigger debug (break) mode and take you to the Immediate window where you can view the names of your form's subform control(s).

The screenshot you added to the question confirms you're using the correct name for the subform control. However, that subform has nothing in its Source Object property. Since there is no form there, the second part of the error message, "doesn't exist", applies. There is no form to be referenced by Me.dataDisplaySubform.Form

Permit

I edited my question above to show the Name in the Properties with the subform, which shows it is named dataDisplaySubform. I also ran your code and got the same result: in the Immediate window the name of the control is "dataDisplaySubform". – Fredericfrederica

Put the name of a form in the Source Object property. – Permit

The only form name that I can put in the Source Object property is the name of the parent form which is frmDataExtract, which is the form that the dataDisplaySubform is located on. When I do that and go to the Form View, I get a warning that states "You can't place a form (or report) within itself. Select or enter a different form or report to serve as the subform or subreport". If I then click OK and then click the button to run the code, I get the same 2467 error highlighting the Me.dataDisplaySuform.Form.RecordSource = pSQL line. Do you specify something in Source Object property? – Fredericfrederica

Is your intention to alter the current form's RecordSource? If so, Me.RecordSource = pSQL I'm unsure what you're after, but at least we have the error message diagnosed. :-) – Permit

Sorry for the delayed response, internet connection at work has been jacked up today. My objective that I mentioned above is to get the datasheet subform named dataDisplaySubform to display the records returned from a dynamically built SQL statement that gets created via user input in controls (yet to be placed) on the parent frmDataExtract. You mentioned "alter the current form's RecordSource" - are you referring to the parent frmDataExtract that the subform is placed on? If yes, how do I modify the parent frmDataExtract and get changes to propagate to the subform? I may not be understanding – Fredericfrederica

I edited my original question post to include a screenshot of the entire form so it can be seen what I am referring to when I say frmDataExtract as the "parent form". I realize I may be misusing terms in the context of MS Access forms since I'm new to working with Forms in this manner, but hopefully not. – Fredericfrederica

AFAICT, you have a form with a subform control and you want to use that same form again as the subform control's Source Object ... so you can display it there in Datasheet View. But that can't work. However, you could make a copy of the form and use that copy in the subform control. Or create a simpler form based on the same data source and use that in the subform. Or just use a saved query as the subform Source Object. – Permit

This doesn't seem to make much sense to me. If I make a copy of the main/parent form and set that as the Source Object for the subform control, I'm just seeing the view of the form within the subform. I want to display the results of the table query in the subform like a datasheet form view. I'm struggling to believe that this can't be done. Again, the hardcoded SQL Statement is just so I can get past this roadblock - eventually the SQL would be built via user input in controls on the main form, and then execiuted when the btnDisplaySWData button is clicked. I appreciate your help with this – Fredericfrederica

I edited my original post above to clarify how @Permit led me to the answer via his dialogue. – Fredericfrederica

Answer 2

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

Intensity

I think this is the best answer. – Globose

Answer 3

Some clarifying points for other readers:

A detail view subform's sourceObject property determines which columns/fields are displayed. So, you could set it to a table or query, then optionally use a filter to return no records (if you want the recordset to be initially blank) or as an alternative to using the recordSource for custom SQL.

The recordSource can be any table, query, or SQL, but the subform will only display fields with names matching the sourceObject's fields. This can be confusing if, for example, you set the sourceObject to a table, then the recordSource to a query with partially overlapping field names (Access will display all columns, but only the overlapping ones will have data in them).

To have a form which displays an arbitrary SELECT statement or allows the user to choose what table(s) to SELECT on, one could save their input as a new query (or have an existing named one to overwrite), then set the sourceObject to that (the form would have to be closed then re-opened for the new columns to be displayed, so you might want to open a pop-up or new tab to display the results).

Gassy

Answer 4

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

Magus