Adding recordsets - doesn't do it on the first shot...? 1

[zahara666]

I have a query that I am adding to.. After many attempts of trying to get the openrecordset command working, I am not having this problem: it doesn't for some reason add to the query on the first shot. I need to close the query and run it again and then it works. everytime after that works fine.. but if i close the database down and try it again, it won't work the first time around... here is the code:

Set rst = dbs.OpenRecordset("InvoiceQuery&quot

start = InputBox("Please enter a start number", "Access", , 2000, 2000)

If Me.FromDate <> &quot;&quot; And Me.ToDate <> &quot;&quot; Then
DoCmd.SetWarnings False
x = MsgBox(&quot;You are about to print invoices, are you sure?&quot;, 1)
If x = 1 Then
CurrentDb.QueryDefs(&quot;InvoiceQuery&quot.SQL = strSQL

Let counter = 1
If (rst.RecordCount <> counter) Or (rst.RecordCount = counter) Then

rst.MoveFirst
rst.Edit
rst!InvoiceNumber = &quot;E&quot; & start
rst.Update
rst.MoveNext

Do Until counter = rst.RecordCount
Let counter = counter + 1
Let start = start + 1
rst.Edit
rst!InvoiceNumber = &quot;E&quot; & start
rst.Update
rst.MoveNext
Loop

End If
DoCmd.OpenQuery &quot;InvoiceQuery&quot;

Else
End If
Else
MsgBox &quot;You MUST enter a date range&quot;
End If

Set rst = Nothing

 

[RickSpr]

The statement:
CurrentDb.QueryDefs(&quot;InvoiceQuery&quot.SQL = strSQL
Modifies the SQL of the stored query (the QueryDef). However, you already opened a pre-modified copy of the query in the first statement. The value of the SQL property at the time you issued the dbs.OpenRecordset() determined the contents of the rst recordset; the change you made later was stored, but had no effect on the recordset already open on the rst variable. However, the next time you run it and open the recordset again, the modified SQL statement in the stored query is used.

One way you could fix it is to delete the Set rst statement at the top, and insert the following after updating the QueryDef SQL property:
Set rst = dbs.OpenRecordset(strSQL) Rick Sprague