MS Access VBA - displays dynamically constructed SQL results in a data subformat

Question

I have several years of experience working with VBA in MS Office applications (for automation and ETL processes), but until recently they did not have to deal with forms in MS Access. I am laying the design for some simple forms of data mining for the database that I developed, and I hung up on what seems like a simple task.

Purpose: I need a datasheet sub format to display records returned from a dynamically generated SQL statement from controls in the main form.

In my main form, I have a button, when the user clicks on it, the button will compile the information specified by the user in other user controls into an SQL query, and then run this query so that the subformation displays the result of the record.

No matter what I do, I cannot get this to work. I keep getting (most of the time anyway) the Microsoft startup time visual error "2467: The expression you entered refers to an object that is closed or does not exist." This is the error I get with the code below. I can’t understand if I need to somehow initiate the subform as soon as any code is run. I tried some other code options that also didn’t work with other code forums, but I seem to have found several message streams, including some that suggest that the code below should work.

The attached image shows what the basic basic form looks like. I designated a button that the user would click (btnDisplaySWData) to compile SQL, which gets the creation from controls that have not yet been enabled, but this is not a problem. I just hard code the SQL statement, as shown in the code snippet, trying to understand this problem. As already mentioned, I want the records to be displayed in a subformat called dataDisplaySubform. "JUNK" is a table in the Access database in which I can legitimately query the SQL code below, which I use only for testing, until I find out. All code in the given data form (with the name 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 called dataDisplaySubform, as shown in the following screenshot of the properties with the selected subform.

Here's what the general layout of the form looks like

I looked at several sites on the forum, and also tried all the options for Stack Overflow search terms to find potential solutions to my problem, but no one worked even when the original stream was marked as resolved by the person who posted it. I spent too much time, about 2 working days, trying to understand what I was doing wrong, and still could not.

I appreciate anyone who can help me in the right direction, it drives me crazy.

thanks, --TB

SOLUTION SOLUTIONS TURKISHGOL

Well, I think I figured it out myself, although HansUp helped me go down the path with a mention of the Source object of a subordinate form that has nothing attached to it. In my case, assigning a Source object in the form was not the right solution that was offered by HansUp. Instead, the saved request seems to make it do what I want.

Not sure if there is a better way to do this, but it looks like you need to set up a dummy, almost a placeholder request, so that you can set the original subform object in VBA for it. The placeholder request is as follows:

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

The above Access query is saved as the name "TESTQUERY". It does not display anything, but satisfies the need to assign a Source Object, essentially creating an instance of the subform when viewing the main form as a form. Thus, with the saved placeholder query, you can reassign the RecordSource for any SQL string using the user interface controls in the main form, for example:

 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 the process of production, the hard-coded SQL statement shown, stored in the pSQL string variable, will be combined through user input of controls in the main form.

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

Answer 1

If a "object that is closed or does not exist" error occurs in the line Me.dataDisplaySubform.Form.RecordSource , the likelihood that your subform control is not named dataDisplaySubform.

You can check the names of all the form subform form elements 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 launches break mode and takes you to the Immediate window, where you can view the names of the controls on the form.

The screenshot added to the question confirms that you are using the correct name for the subform control. However, this subform has nothing in the property of the original object. Since there is no form, the second part of the "nonexistent" error message is applied. There is no form that Me.dataDisplaySubform.Form can reference

Answer 2

Some clarifying points for other readers:

The subform property of the original representation of the objectObject determines which columns / fields are displayed. That way, you can set it to a table or query, then you don't have to use a filter to return records (if you want the recordset to be initially empty) or as an alternative to using a record source for custom SQL.

RecordSource can be any table, query or SQL, but the subformation will only display fields with names corresponding to the sourceObject fields. This can be confusing if, for example, you set sourceObject to a table, then the record source for the query with partially overlapping field names (Access displays all columns, but only overlapping ones will have data in them).

In order to have a form that displays an arbitrary SELECT statement or allows the user to choose which table (s) for SELECT, you can save their input as a new query (or have an existing one to overwrite), then set sourceObject to this (the form must be closed, and then reopened to display the new columns, so you may need to open a popup or a new tab to display the results).

Answer 3

Use CreateQueryDef , and then click Me.dataDisplaySubform.SourceObject = "Query.NewqueryName"
NewQueryName is the name specified during creation using CreateQueryDef

Answer 4

short and sweet. Here is the button code that creates a dynamic sql string, closes the current object (just in case it is open), removes the temporary definition of the query (because we need it), creates a new query definition with new sql, changes the source of the records and Bob is 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