Pass form values to SQL JOIN operation?

[jonman03]

Hey everyone,

This is a pretty detailed question, but need some help, if this is even possible (??)..

I have a form that displays my Access system objects (tables) in two list boxes. I am trying to pass these form values (the names of the tables) to a query so that the query is totally dynamic.

I have replaced the SQL code for the SELECT operation ([Forms]![mainform]![listbox1]) and ([Forms]![mainform]![listbox2]), however, when I try to replace the hard coded table names in the SQL JOIN section, with these form control references, I am getting a SQL error, after attempting to run the query, that says: "Syntax error in JOIN operation"

Can anyone please help? I am wondering if this is even possible within Access SQL? Thank you!

[June7]

I think would have to use VBA to modify query definition with QueryDefs collection. Here is example from my project:

Private Sub btnExcel_Click()
Dim qdfUser As DAO.QueryDef
CurrentDb.QueryDefs.Delete ("UserQuery")
Set qdfUser = CurrentDb.CreateQueryDef("UserQuery", Me.tbxFilter)
DoCmd.OpenQuery "UserQuery", , acReadOnly
DoCmd.RunCommand acCmdExportExcel
End Sub

[jonman03]

I think would have to use VBA to modify query definition with QueryDefs collection. Here is example from my project:

Private Sub btnExcel_Click()
Dim qdfUser As DAO.QueryDef
CurrentDb.QueryDefs.Delete ("UserQuery")
Set qdfUser = CurrentDb.CreateQueryDef("UserQuery", Me.tbxFilter)
DoCmd.OpenQuery "UserQuery", , acReadOnly
DoCmd.RunCommand acCmdExportExcel
End Sub

I'm not sure I understand.. Maybe it would be easier to provide my actual example. Here is the SQL code for the query I am attempting:

SELECT ref_Entity.Region, ref_Entity.[Entity Name], ref_ProductNames.[Product Name], Sum([Forms]![formquerytest]![lefttable].[Qtr 1 ($USD)]) AS [Mar Fcst Q1], Sum([Forms]![formquerytest]![lefttable].[Qtr 2 ($USD)]) AS [Mar Fcst Q2], Sum([Forms]![formquerytest]![lefttable].[Qtr 3 ($USD)]) AS [Mar Fcst Q3], Sum([Forms]![formquerytest]![lefttable].[Qtr 4 ($USD)]) AS [Mar Fcst Q4], Sum([Forms]![formquerytest]![lefttable].[Total Year ($USD)]) AS [Mar Fcst Year Total], Sum([Forms]![formquerytest]![righttable].[Qtr 1 ($USD)]) AS [Feb Fcst Q1], Sum([Forms]![formquerytest]![righttable].[Qtr 2 ($USD)]) AS [Feb Fcst Q2], Sum([Forms]![formquerytest]![righttable].[Qtr 3 ($USD)]) AS [Feb Fcst Q3], Sum([Forms]![formquerytest]![righttable].[Qtr 4 ($USD)]) AS [Feb Fcst Q4], Sum([Forms]![formquerytest]![righttable].[Total Year ($USD)]) AS [Feb Fcst Year Total], [Forms]![formquerytest]![righttable].Product_Type, [Forms]![formquerytest]![righttable].Year
FROM (([Forms]![formquerytest]![lefttable] RIGHT JOIN [Forms]![formquerytest]![righttable] ON [Forms]![formquerytest]![lefttable].Unique_ID = [Forms]![formquerytest]![righttable].Unique_ID) INNER JOIN ref_ProductNames ON [Forms]![formquerytest]![righttable].Products = ref_ProductNames.Products) INNER JOIN ref_Entity ON [Forms]![formquerytest]![righttable].Entity = ref_Entity.[Entity Code]
GROUP BY ref_Entity.Region, ref_Entity.[Entity Name], ref_ProductNames.[Product Name], [Forms]![formquerytest]![righttable].Product_Type, [Forms]![formquerytest]![righttable].Year
HAVING ((([Forms]![formquerytest]![righttable].Product_Type)="Existing_FDF") AND (([Forms]![formquerytest]![righttable].Year)="FY12"))
ORDER BY Sum([Forms]![formquerytest]![lefttable].[Total Year ($USD)]) DESC;

In this example, I have replaced "Master_2012 March Fcst" with [Forms]![formquerytest]![lefttable]
and "Master_2012 Feb Fcst" with [Forms]![formquerytest]![righttable].

The names of the list boxes containing the names of my tables are found on the form "formquerytest" with the names "lefttable" and "righttable." Also - Master_2012 March Fcst and Master_2012 Feb Fcst are 2 tables with identical structure, simply different data. It is NOT possible to combine these 2 tables into 1.

When I attempt to run the query, I get the error "Syntax error in JOIN operation" at the ! after [Forms] in the FROM clause.

Thank you!

[June7]

I don't think an Access query can handle dynamic table referencing, just as cannot handle dynamic field referencing (tried that).

An SQL statement can be built by code and that SQL statement can be used to set the RecordSource property of form/report or RowSource property of combo/list box or the definition of a saved Access query.