Query will not execute

Question

This question has several parts. I am working on code that has a number of queries that create tables which are then exported to a word document. The previous author gathered the data through a mix of lookups and queries  exported piecemiel to the word document.  Since his code was broken up into many functions and subroutines many lookups wind up being repeated.  One case was a triple nested fuction call that was part of a Dlookup, which meant that the function hit 27,000 times as the Dlookup traversed the table!

I determined that it woud be bettter to gather all the information I needed by puttint eh Dlookups and queries in a form. Some of the queries use other fields on the form as parameters for the queries. This way I could make one pass ,gather all the info and then pass it to the word document.  Most of this is working OK. Also I can preview the data before sending it to the report in Word. At present there are editorial comments entered in the Word document to explain some of the results, and these are lost to the database. I plan to fix that by putting a comments field on the from.

But now I have a query that uses info from a field on the form as a parameter.  This query executes properly when run manually. The code to execute it reads:

Dim qry as querydef

Dim rst as DAO.recordset

Dim Recordcounter as integer

Set qry = CurrentDb.QueryDefs("qryrep1Table2")

Set rst = qry.OpenRecordset

rst.MoveLast

rst.move first

Recordcounter = rst.RecordCount

'manipulate rst

'write rst to a table

'export the table to word

This throws an error stating that rst is undefined (err 91).  This is similar to code used previusly, and I do not understand why the query works when run manually and fails when called in code.

Any Ideas why this fails?

Part two: When complete the final table ("tblrep1Table2")  needs a sequential  number in the first column (1,2,3,4). I can use queries to generte the table, then add a column and step through it to number it, but is there an easy way to create a a squentials number field within the querydef?

Thanks

Furthermore, the query (queries) will not run while the code is in debug mode: I have to turn the debugger off (reset) to test the queries and then re run the cose from the beginning.

any idea what causes this to fail?

Answer 1

That is my more or less my response. Once you declare the qry variable yu should be able to treat it the same as typing out the full name everywhere.

Obviously it is more direct to just open the recordset using the query name.  But this isn't my code, so I am hesitant to eliminate the qry variable in case it is used someplae else.    And sisnce I can't tell what is happening I will create a form and make its recordset the same as the one being manipulated, so the results show on the form, or in a listbox on a form.

Fro example:

 Set qdf = CurrentDb.QueryDefs("qyDataSelection")

qdf.Parameters(0) = [Forms]![DataSelectionForm]![txtbxPart]

qdf.Parameters(1) = [Forms]![DataSelectionForm]![txtbxLocation]

qdf.Parameters(2) = [Forms]![DataSelectionForm]![txtbxTest]

qdf.Parameters(3) = [Forms]![DataSelectionForm]![txtSartDate]

qdf.Parameters(4) = [Forms]![DataSelectionForm]![txtEndDate]

  '  For Each prm In qdf.Parameters

  '      prm = Eval(prm.Name)

  '  Next prm

    Set rst = qdf.OpenRecordset

===========================

I came up with this becasueI could not figure out waht eval(parm.name) meant, and withthe parms buried in the loop, I could not examine them.

 Now I just put the parameter definitions (the control names) in the qry def for qryDataSelection. And the control names themselvs may be the result of other queries.

I know this all sounds crazy, but it was one of situations where the requirements were unknown at the beginning, and things were added piecemeal over time, without the under lying data relationships to support them: hence the constant stream of Dlookups.

Anyway, inthe one case neither the construct   

Set rst = qdf.OpenRecordset      nor

Set rst = dbs.OpenRecordset("qyDataSelection" seems to work, even though the query operates manually, and all my other queries work this way.

Maybe I need a pause statement or something??????

Answer 2

That is more or less the way the original author had it, but because he had the code broken up into many subroutines or functions he would wind up running the same query and evaluating it over and over again for different purposes.  Part of the reason was that his tables were not relational, which I fixed.  even so, he needs the a value over and over again.  And then he uses that value as a parameter for another query and so on.

By simply putting the queries as recordsources on the form, I can point to another control or controls to provide the proper parameters, and do all that once,  Then whenever I need a value for the report I grab it from the form. And the same parameter can be used in different queries, and I only have to run the subquery that develops the parameter once.

And, I can inspect the value of the parameters by looking at the form, other wise I have to use Debug.print or a message box or the locals window.

Answer 3

This:

Set qry = CurrentDb.QueryDefs("qryrep1Table2")

Set rst = qry.OpenRecordset

 

Should be

 

Set rst = CurrentDb.OpenRecordset("qryrep1Table2")

Actually Bob, either will work with a query without parameters, but where, as in this case, the query has a parameter, I think the OpenRecordset method of the Querydef object has to be called so that the parameter can be evaluated.

Answer 4

But now I have a query that uses info from a field on the form as a parameter.

That's the problem; you need to evaluate the parameters:

Dim qry As DAO.QueryDef

Dim rst As DAO.Recordset

Dim prm As DAO.Parameter

Dim Recordcounter As Integer

Set qry = CurrentDb.QueryDefs("qryrep1Table2")

For Each prm In qry.Parameters

    prm = Eval(prm.Name)

Next prm

Set rst = qry.OpenRecordset

rst.MoveLast

rst.MoveFirst

Recordcounter = rst.RecordCount

Answer 5

You shouldn't need to have that query object, unless it comes into play later on and you didn't include that code.  But you are not opening a record set with your set rst statement.

This:

Set qry = CurrentDb.QueryDefs("qryrep1Table2")

Set rst = qry.OpenRecordset

Should be

Set rst = CurrentDb.OpenRecordset("qryrep1Table2")

...