Access pass-through query is running after results are displayed
Posted on 2011-10-03
I have a form to collect parameters for a pass-through query in Access. The pass-through query executes a stored procedure. The results of the stored procedure are to be displayed in a results form. I've done this before with no-problems, but I must be overlooking something now because when my results page come up it does not display the results from my current paramters, it displays results from the last paramters I entered on my last attempt. For example, if I enter a date of '2009' to send to the stored procedure and then I view the results form, I dont see results for 2009, I see results from the last paramater I entered. If I do it again and now enter '2010', I now see results for 2009 and so on. My pass-though query is recieving the current paramaters I entered, because when I look at it in design view, I see the current paramters I just entered. I think I need another set of eyes...it seems like my results are not being sent until after the fact.
My paramters form (frmSearchgroupby) simply has unbound text boxes and a button to open the results form.
My pass-through query (qrySQLSearchDocs) is this: exec spSearchDocs
My stored procedure (spSearchDocs...tested and works great in sql) is this:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spSearchDocs]
@txtDocSearch varchar(6) = null,
@txtYearSearch int = null,
@txtGorLastSearch varchar (50)=null
SELECT * from DocDb.dbo.Search_groupby_view
(Doc = @txtDocSearch or @txtDocSearch IS NULL or @txtDocSearch = '')
and (Year = @txtYearSearch or @txtYearSearch Is Null or @txtYearSearch = '')
and (GorLast like @txtGorLastSearch or @txtGorLastSearch is null or @txtGorLastSearch = '')
order by Doc
My results form (frmSearchResultsGroubBy) has the data source set to the pass-through query.
In the On Open event I have this:
Private Sub Form_Open(Cancel As Integer)
Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
Set dbsForm = CurrentDb
Set frm = Forms!frmSearchgroupby
Set qdf = dbsForm.QueryDefs("qrySQLSearchDocs")
Dim rstForm As dao.Recordset
qdf.SQL = "EXEC spSearchDocs " & "'" & Forms!frmSearchgroupby!txtDocSearch & "'" & "," & "'" & Forms!frmSearchgroupby!txtYearSearch & "'" & "," & "'" & Forms!frmSearchgroupby!txtGorLastSearch & "'"
qdf.ReturnsRecords = True
Set rstForm = qdf.OpenRecordset()
Set qdf = Nothing
Set frm = Nothing
Am I mising something, or is something running in the wrong place? It is working great, except that my search is running after the fact!