SQL Server Query with variables as MS Access subform record source

Question

I'm trying to figure out the best way to go about building a dynamic query and use it as a record source for a MS Access subform. I've got my WHERE clause statically assigned at the moment just so I could get the form built and make sure data was pulling properly. The main form will have 2 text boxes formatted as ShortDate and 2 subforms below with the query results. The first subform is the query grouped by Employee name and the second subform is a sum of department totals.

FWIW this is an Access 2010 ADP/ADE front end, SQL Server 2008 back end. My current SQL for the Dept totals is as follows:

SELECT        COUNT(*) AS TotalNumEstimates, SUM(NumPanels) AS TotalNumPanels, SUM(PriceBase) AS TotalBasePrice, SUM(PriceBase) / SUM(NumPanels) AS ValuePricePerPanel 
FROM            dbo.tblBid 
WHERE        (Date > CONVERT(DATETIME, '2016-01-01 00:00:00', 102)) 
HAVING        (SUM(NumPanels) > 0)

I plan on changing the WHERE clause to "WHERE Date BETWEEN @FromDate and @ToDate". Then on the Access form when the dates are set and a "Run Report" button is clicked, programatically set the OnClick event to pass the txtFromDate and txtToDate to the @FromDate and @ToDate respectively, but I can't quite figure that part out.

The only other option I can see would be to type out the whole SQL statement as a string with the txtFromDate and txtToDate declared in the OnClick event and change the subform record source to the new string. Is there a better way to go about doing this?

Answer 1

I believe I have figured out my initial problem of passing the textbox to the WHERE clause by creating a stored procedure. I will repost if I can't get the stored procedure to work, but it seems easy enough.

Answer 2

Use a [permanent] temporary table, which you then remove when the form unloads. If multiple users will use the form simultaneously, you'll need to add a session number of some sort to the name. So:

SELECT COUNT(*) AS TotalNumEstimates, SUM(NumPanels) AS TotalNumPanels, SUM(PriceBase) AS TotalBasePrice, SUM(PriceBase) / SUM(NumPanels) AS ValuePricePerPanel 
INTO   dbo.TempSubFormTable    
FROM   dbo.tblBid 
WHERE  Date > '2016-01-01'
HAVING SUM(NumPanels) > 0

Remove the recordsource from your subform, and give it a public interface:

Public Sub SetRecordSource(tbl as string)
    Me.RecordSource = "SELECT * FROM tbl" 'ORDER BY ...
End Sub

Then, before the temporary table is created, make your subform invisible. After creating the temporary table,

Me.subformName.Visible = True
Me.subformName.SetRecordSource temporary_table_name 'without the dbo. part of the name