Access pass-through query is running after results are displayed

Posted on 2011-10-03
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 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
    LVL 22

    Expert Comment

    by:Kelvin Sparks
    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

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now