Forcing a ReQuery

[Dkline]

I have four queries which are run in sequence. All are Select queries.

#1 balance
#2 prorated
#3 calculated
#4 summary

For #1 and #2 I reset the SQL based on User Form entries. No problem with
either one.

#3 and #4 are just to be run - no resetting SQL or anything else. Just
update. Doesn't do it. Modifed date doesn't change.

I've tried forcing the issue by doing a MoveLast and a Requery.

I'm out of ideas.

Code is:

Dim strSQL As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb

'#1
'qry_balance
'reset the SQL to the user form dates
BuildSQLStringWHERE(strSQL)
dbs.QueryDefs("qrybalance").SQL = strSQL

#2
'qryprorated
'reset the SQL to the user form dates
BuildSQLStringHAVING(strSQL)
dbs.QueryDefs("qryproratedc").SQL = strSQL

#3
'open qrycalculation
Set qdf = dbs.QueryDefs("qrycalculation")
Set rst = qdf.OpenRecordset(dbOpenDynaset)
rst.MoveLast
rst.Requery
rst.Close
Set rst = Nothing
Set qdf = Nothing

#4
'open qrysummary
Set qdf = dbs.QueryDefs("qrysummary")
Set rst = qdf.OpenRecordset(dbOpenDynaset)
rst.MoveLast
rst.Requery
rst.Close

Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing

 

[Marshall Barton]

What do you want to happen?? Your code just opens a
recordset and closes it immediately, so it's just burning
some cycles without actually doing anything.

 

[Michael Keating]

Hi,

Sorry to be rather negative, but ..

I think you need to copy and paste the actual code here.

From what you've posted we can assume that queries 3 and 4 utilise the
results of queries 1 and 2, but you appear to only use 3 and 4 to populate a
recordset which you immediately close and destroy.

I get the feeling that the problem is either going to be in the SQL for
queries 1 and 2, which you say works correctly, or in the fact that queries
3 and 4 appear to achieve nothing in this code.

Perhaps the actual procedure will make things clearer. Also a brief
explanation of what you want to happen would help (Modified Date ?? Who
modified that ?? <g>).

MFK.

 

[Dkline]

The 4 queries need to be run in sequence. I'm assuming the last query
#4) - which is a query of queries - needs to be opened so it can be current
with the two tables that have been updated by SQL and the #3 table to be
updated based on changes to #1 and #2. The #1 mad #2 files get updated by
running the SQL. #3 just needs to be made current.

I am trying to get the last two queries (#3 and #4) to update. If I open
them directly in the Queries they update. When I open them in VB, they don't
update - or at least the "Modified" value doesn't change.

I first tried simply opening them which would cause them to update. When
that didn't work I tried moving the record set to the last item. That also
didn't work so I tried Requery.

 

[Marshall Barton]

I don't understand what you're trying to accomplish here.
Opening a recordset is not going to update anything (at
least without some code that explicitly changes something).

It almost sounds like you are trying to modify the data in
some tables, but opening a recordset only retrieves data. I
can't tell from what you've explained so far, but, if these
are action queries, maybe you want to Execute the queries
instead of opening a recordset??