Access pass-through query is running after results are displayed

Posted on 2011-10-03
Medium Priority
Last Modified: 2012-06-27
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:

USE [DocDb]
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
End Sub

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!
Question by:dcgimo
  • 2
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 36907956
Is the form bound or unbound. It almost sounds like the control hasn't updated when you clicked the run button - if bound do a saverecord first, if unbound find a way to ensure that the focus has ghone elsewhere first

LVL 22

Accepted Solution

Kelvin Sparks earned 2000 total points
ID: 36907962
Looking at it again. You set the qdf on form open, and then redone the SQL, but not topld it to run again. You need a me.requery after setting the sql

Author Comment

ID: 36909187
That is it!  I added the me.requery and it is working now.  Thank you so much!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question