Sub Query for from clause

yaziron · December 11th, 2013, 02:10 PM

Hi all,

I am trying to use a select query for my from clause to access a table name. The reason being is I want the user to be able to select a table from a combobox without the prepended "tbleq...". So, I have a table named "tbleqMCC" and I want the combobox to show "MCC". That part works okay. Then, based on user's selection in the combobox, I want to show data from that table. That part is not working. My SQL looks like this:

Code:

SELECT *
FROM (
SELECT Replace([ID], [ID], "tbleq" & [ID]) 
FROM tblTypesofEquip
where ID = [Forms]![Main]![combo26]
);

The result is just the table name, not the data within that table.

Thanks much.

Yazzy

HiTechCoach · December 11th, 2013, 04:38 PM

I would build the SQL in VBA code first.

Not sure you need a sub query.

Something like this:

Code:

Dim strSQL as String

strSQL = "SELECT * FROM tbleq" & [Forms]![Main]![combo26]  & ";"

' print the  SQL statement into immediate window for debugging
Debug.Print strSQL

Now that you have the SQL built you can assign it to the record source of a form or save is as a query that can be used as a form's record source.

This example may help: DB Manager (Click Here)

yaziron · December 11th, 2013, 05:42 PM

"Now that you have the SQL built you can assign it to the record source of a form or save is as a query that can be used as a form's record source."

I am not sure how to do this? The only way I know to utilize the VBA code is with control items (i.e., combo box, command button, etc..). How do I write just a string of VBA code and then apply it to a query?

Thanks much for your quick response.

HiTechCoach · December 11th, 2013, 10:03 PM

Without more detail about your form design I am not sure which event will be best. You could use the after update event of the combo box that selects the table name.

Here is an example of how to create a saved query using the QueryDef() Collection.

Code:


    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef

    Dim strSQL as String

     strSQL = "SELECT * FROM tbleq" & [Forms]![Main]![combo26]  & ";"

    ' print the  SQL statement into immediate window for debugging
     Debug.Print strSQL


    'Set database to add querydef
    Set db = CurrentDb()

    'Add querydef
    Set qdf = db.CreateQueryDef("Your Query Name Here")
    qdf.SQL = strSQL

    qdf.Close
    db.Close

    'Refresh database window
    RefreshDatabaseWindow