Time requirement between multiple DoCmd.RunQuery lines

[NIWANut]

Hi,

I am writing a Wizard type approach for non Access gurus to extract data from a database. Each form drills further down into the data with only those records based on previous forms being presented. I have got it all generally working but have this annoying problem.

The first form asks the user to choose the Cruise(s) from a list (16 items at present). When the NEXT button on this form is pressed each item chosen in the Listbox is placed in a table then two queries are run, the first deletes all the old data from a table and the second appends new records to that table based on the Cruises selected and a table that lists the Cruise, Stations sampled and their Latitude and Longitude. This table is then presented in the next form. So the final lines in my code when the NEXT button is pressed on the first form are:

DoCmd.SetWarnings False
'Now clear table "SelCruise Data"
DoCmd.OpenQuery "Clear CruiseSel Data Query", acViewNormal
'Now run query linking "SelCruise" which has selected Cruises and "Cruise Station" table
'to put Cruise, Station, Latitude and Longitude into table "SelCruise Data"
DoCmd.OpenQuery "CruiseSel Data Query", acViewNormal
DoCmd.SetWarnings True

DoCmd.Close
DoCmd.OpenForm ("Get Stations Make Table&quot

When the form "Get Stations Make Table" appears the data does not reflect the Cruise(s) chosen, in fact it is from the chosen Cruise(s) from the selection before the present selection, in other words it appears that the second query has started before the first completed. To test this I changed my code to:

DoCmd.SetWarnings False
'Now clear table "SelCruise Data"
DoCmd.OpenQuery "Clear CruiseSel Data Query", acViewNormal

PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop

'Now run query linking &quot;SelCruise&quot; which has selected Cruises and &quot;Cruise Station&quot; table
'to put Cruise, Station, Latitude and Longitude into table &quot;SelCruise Data&quot;
DoCmd.OpenQuery &quot;CruiseSel Data Query&quot;, acViewNormal
DoCmd.SetWarnings True

DoCmd.Close
DoCmd.OpenForm (&quot;Get Stations Make Table&quot

When this code is run the second form gets exactly the data from the Cruise(s) I want. If I change the Pause time to 2 seconds, it fails.

This is all run on a Pentium 600 MHz machine on Windows 2000 with the database on my local hard drive so there is no lag with using netword drives.

Is there some way to force a wait until one query is completed before the next is run, I thought Access/VBA would do this automatically i.e. wait until the actions from one line of code is completed before the next is run.

Thanks


George

 

[BillPower]

Hi NIWANut,

Could you post the SQL for both the Delete and Append Queries. I've read your Question a few times now, the first thing that comes to mind is that because you are Deleting and Appending based on Multiple Criteria (up to items at present), there is the possibility of an inadvertent Loop being created. It might be good idea to post the above Code in its entirety too.

If all else fails you are welcome to Email a copy of your DB to me at billpower@cwcom.net removing any sensitive records first. Any suggestions will be posted here in this Thread, I do not reply by Email.

Good Luck

Bill

 

[NIWANut]

Hi Bill,

Thanks for the offer to look at my database. I have got it to work by doing two things. First I made sure my connection was closed before I did my query and secondly I put a short delay to ensure the query was finished - I would have thought that Access would automatically wait until one command was completed before moving onto the next but it appears that is not the case.

Not the best solution but OK until I get a better solution.

Thanks

George