Query "holding" values

[Alisdairjohnston]

I'm hoping someone here can give me a pointer in the right direction. I have a database which runs a (fairly complex (60 + fields)) query to output an annual report to a Word document. The procedure is called from a button on a form, which then opens a Word template and populates the template. The VBA code works fine (I have been reusing it for years), but sometimes the db goes into a spin, and will only return the report based on the first record in the dataset. Any ideas as to what I am doing wrong?

 

[bob fitz]

but sometimes the db goes into a spin, and will only return the report based on the first record in the dataset

Has it been doing that for years as well

 

[Alisdairjohnston]

Has it been doing that for years as well

LOL! No, it has not. However, this is the first time I have run this system over a fairly dispersed LAN (Server in London accessed by team members all over the UK and Europe via a VPN).

 

[JHB]

If it has works okay on a local server/computer I think your code is running to fast, the data isn't ready/loaded.
The same can happens on a local server/computer if you have a report using a query with a complex calculation or with sub queries.
In such cases a DoEvents placed in your code at certain places could help.

 

[Alisdairjohnston]

If it has works okay on a local server/computer I think your code is running to fast, the data isn't ready/loaded.
The same can happens on a local server/computer if you have a report using a query with a complex calculation or with sub queries.
In such cases a DoEvents placed in your code at certain places could help.

Thanks JHB - it is a fairly complex(long) piece of code - so you may be onto the solution. How would I use a DoEvents in this?

A snippet of the code is here - there is about 90 lines!

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="VisitsSCCurrent"
WordObj.Selection.TypeText rsKLETracker![VisitsSCCurrent]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="TotalVolunteersCurrent"
WordObj.Selection.TypeText rsKLETracker![TotalVolunteersCurrent]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="TotalCWCurrent"
WordObj.Selection.TypeText rsKLETracker![TotalCWCurrent]

 

[JHB]

A good place for a DoEvents is just after you fetching data, like opening a recordset.
If you've some errorhandling in the code then comment it out until your code runs okay.
You can test if DoEvents will help, by placing some breakpoints in your code, each time your code reach a breakpoint wait about 15 seconds and then continue the code until next breakpoint.
The code you show isn't enough, but in none of the codeline you shows here, it would help with a DoEvents.
When you post code, then use the code tags = "#".

 

[arnelgp]

execute .Movelast on your recordset prior on manipulating its data. this will force the recordset to fetch all its data before returning control to program:

if not (rs.eof and rs.bof) then
rs.movelast
rs.movefirst
end if

 

[JHB]

Did you get it to run?

 

[Alisdairjohnston]

I did, thank you. For information, the problem was not in the code, but in the underlying query which fed the data to the Word document.

However, the very fact of posting here forced me to go back and review the whole process thoroughly - and I found an error in the query which was causing the problem. So, thanks to all

 

[JHB]

...
However, the very fact of posting here forced me to go back and review the whole process thoroughly - and I found an error in the query which was causing the problem. So, thanks to all

Good you got it solved, good luck!