MS Access VBA - display dynamically built SQL resu

问题:

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.

回答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

回答2:

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).

回答3:

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

回答4:

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