how to let user to select a table to run?

[Jeff06]

Query
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;


I have varcurve1 varcurve2 varcurve3....
I want to provide a mean (interface?) for user to choose varcurve1 varcurve2 varcurve3 to run above query.

for eample if user choose varcurve1
the query will run

SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;

if user choose varcurve2
the query will run

SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve2 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;



is there a way to do that?

Thanks

 

[Jon K]

You can let the user select the table name from a list box on a form and run the query from a command button on the form.

In the On Click event of the command button, you can use code like the following (replacing with the correct names of command button, list box and query):-

Private Sub commandButtonName_Click()
   If IsNull(Me.listBoxName) Then
      MsgBox "Select a table first."
      Exit Sub
   End If
   
   Dim SQL As String
   Dim sOriginalTable As String
      
   SQL = CurrentDb.QueryDefs("queryName").SQL
   
   sOriginalTable = Left(Mid(SQL, InStr(SQL, "varcurve")), _
        InStr(Mid(SQL, InStr(SQL, "varcurve")), " ") - 1)
   
   SQL = Replace(SQL, sOriginalTable, Me.listBoxName)
   
   CurrentDb.QueryDefs("queryName").SQL = SQL
   DoCmd.OpenQuery "queryName"

End Sub

Note:
It's better to use a JOIN to join the tables in the query. In Access, a join is more efficient than a link in the Where Clause.

SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s INNER JOIN varcurve1 AS v ON (s.contracttypename=v.contracttypename) AND (s.mdiff=v.monthodr)
GROUP BY s.contracttypename;
.