Run Query when other query has run

DreamAwake

Registered User.
Local time
Today, 05:26
Joined
Dec 13, 2007
Messages
23
I'm having major issues in an access form where I run a Query and it populates 10 fields or so.

From those populated fields I run another query based on a fields value.

Now the 2nd query at some point is trying to run when the fields value is not populated, so i'm getting errors. Its a sporadic issue based on the speed of the form opening.

Is there anyway I can either postpone the function being executed or perhaps use some sort of UNTIL SET function or check if a query has been executed.

I performed a rough search on this forum to find nill.

I have a programming background just not in VB.
 
How are you running the queries? From what event(s)? What code are you using to run them?
 
strSQL = "SELECT Sum(tblPayment.Amount-(tblPayment.Amount*0.1)) AS SumOfAmount FROM tblPayment GROUP BY tblPayment.ContractID, tblPayment.Method HAVING (((tblPayment.ContractID)= " & [Forms]![frmViewMNW].[txtContID] & ") And ((tblPayment.Method)='GST Included'));"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)

I have 3 similar queries and I need to use the SUM value (SumOfAmount)

So just imagine 3 of them in a row and I need to add the value at the end. I think I might actually have a problem with too many connections open at once, unsure how Visual Basic handles connections.

Regards
 
Again, I'll ask - what EVENT are you using to run these?

Also, what are you doing with the results?
 
My Apologies, I am using the onload event. But the actual SQL code is stored in a function as i'll be requesting it again.

I am doing calculations on tax information. So I need to calculate certain rows depending on how they paid. So thats why multiple queries are required.
 
Can you post what you have in the On Load event (the whole code)? It SHOULDN'T load before the queries run as they are not asynchronous, in other words they run in sequence and then the next code runs. It may be that putting it on the ON OPEN event would make sense as that event occurs BEFORE the On Load event and you might get better results there.
 
I tried that, order it still failed, thats why I assumed it was a connection problem.

PHP:
Dim dbs As DAO.Database
   Dim rst0 As DAO.Recordset
   Dim rst As DAO.Recordset
   Dim rst2 As DAO.Recordset
   Dim strSQL As String
   Dim strSQL0 As String
   Dim total_r As Integer
   Dim total_r_inc As Integer
   Dim strSQL2 As String
   Dim checkdup As Integer
   Dim response As String
   
   strSQL = "SELECT Sum(tblPayment.Amount-(tblPayment.Amount*0.1)) AS SumOfAmount FROM tblPayment GROUP BY tblPayment.ContractID, tblPayment.Method HAVING (((tblPayment.ContractID)= " & [Forms]![frmViewMNW].[txtContID] & ") And ((tblPayment.Method)='GST Included'));"

   Set dbs = CurrentDb()
   Set rst = dbs.OpenRecordset(strSQL)
   
   strSQL0 = "SELECT Sum(tblPayment.Amount) AS SumOfAmount0 FROM tblPayment GROUP BY tblPayment.ContractID, tblPayment.Method HAVING (((tblPayment.ContractID)= " & [Forms]![frmViewMNW].[txtContID] & ") And ((tblPayment.Method)='GST Included'));"

   Set dbs = CurrentDb()
   Set rst0 = dbs.OpenRecordset(strSQL0)
   
   strSQL2 = "SELECT Sum(tblPayment.Amount) AS SumOfAmount2 FROM tblPayment GROUP BY tblPayment.ContractID, tblPayment.Method HAVING (((tblPayment.ContractID)=" & [Forms]![frmViewMNW].[txtContID] & ") AND (Not (tblPayment.Method)='GST Included'));"

   Set rst2 = dbs.OpenRecordset(strSQL2)
   
   total_r = rst!SumOfAmount + rst2!SumOfAmount2
   Forms!frmViewMNW!exgst = total_r
   total_r_inc = rst0!SumOfAmount0 + rst2!SumOfAmount2
   Forms!frmViewMNW!incgst = total_r_inc
 
One way to check about the connections (and I can see that you don't need but one recordset object to do what you are doing), is to reduce it to ONE.

Just use a variable to store the information that you are going to put in a variable anyway:

Code:
   Dim dbs As DAO.Database 
   Dim rst As DAO.Recordset 
   Dim strSQL As String 
   Dim total_r As Integer 
   Dim total_r_inc As Integer 
   Dim checkdup As Integer 
   Dim response As String 
   
   Set dbs = CurrentDb

 
   strSQL = "SELECT Sum(tblPayment.Amount-(tblPayment.Amount*0.1)) AS SumOfAmount FROM tblPayment GROUP BY tblPayment.ContractID, tblPayment.Method HAVING (((tblPayment.ContractID)= " & [Forms]![frmViewMNW].[txtContID] & ") And ((tblPayment.Method)='GST Included'));" 

   Set rst = dbs.OpenRecordset(strSQL) 
   total_r = rst!SumOfAmount
   rst.Close

   strSQL = "SELECT Sum(tblPayment.Amount) AS SumOfAmount0 FROM tblPayment GROUP BY tblPayment.ContractID, tblPayment.Method HAVING (((tblPayment.ContractID)= " & [Forms]![frmViewMNW].[txtContID] & ") And ((tblPayment.Method)='GST Included'));" 

   Set rst = dbs.OpenRecordset(strSQL) 
   total_r_inc = rst!SumOfAmount0
   rst.Close 

   strSQL = "SELECT Sum(tblPayment.Amount) AS SumOfAmount2 FROM tblPayment GROUP BY tblPayment.ContractID, tblPayment.Method HAVING (((tblPayment.ContractID)=" & [Forms]![frmViewMNW].[txtContID] & ") AND (Not (tblPayment.Method)='GST Included'));" 

   Set rst = dbs.OpenRecordset(strSQL) 
    
   total_r = total_r + rst!SumOfAmount2 
   Forms!frmViewMNW!exgst = total_r 
   total_r_inc = total_r_inc + rst!SumOfAmount2 
   Forms!frmViewMNW!incgst = total_r_inc  

   rst.Close
   Set rst = Nothing
 
I tried the above cost in my application and received the following error;

Run-time error '3021' No current record

Sorry but I don't exactly get what you saying here "Just use a variable to store the information that you are going to put in a variable anyway:"

Regards
 
Sounds to me like maybe one of your sql statements returned no records and one of the following lines in your code could not be executed as a result:

total_r = rst!SumOfAmount
total_r_inc = rst!SumOfAmount0
total_r = total_r + rst!SumOfAmount2
total_r_inc = total_r_inc + rst!SumOfAmount2
 
It errors on this line, I thought it returned no results either, but then i copied that exact SQL statement into a query window and it returned a result, even on the same unique ID.
 
Not sure which line you're referring to.

So, when you did the test of the sql, did you do that while stepping through the code in debug mode?
 
The error I get is on this line;

total_r = total_r + rst!SumOfAmount2

No I created a query to test it, i'll test it again. Just a side not, can we add in any error handling which can prove that it has a value?
 
You could try

Code:
strSQL = "SELECT Sum(tblPayment.Amount) AS SumOfAmount2 FROM tblPayment GROUP BY tblPayment.ContractID, tblPayment.Method HAVING (((tblPayment.ContractID)=" & [Forms]![frmViewMNW].[txtContID] & ") AND (Not (tblPayment.Method)='GST Included'));"
 
Set rst = dbs.OpenRecordset(strSQL) 
[COLOR="Red"]'add this bit to test
   if not rst.EOF then 
      Msgbox "Well, there's a record here", vbinformation
   Else
      Msgbox "There's no record in the sql!",vbinformation
   End if[/COLOR]

 total_r = total_r + rst!SumOfAmount2 
   Forms!frmViewMNW!exgst = total_r 
   total_r_inc = total_r_inc + rst!SumOfAmount2 
   Forms!frmViewMNW!incgst = total_r_inc
 
OK guys, I think I got a grip on it now. For some reason its working now, and that error message handling gave the correct results.

Points for you both :)
 
Well, glad you got it sorted. :)

Not sure that the testing code would have helped much to solve the problem unless it pointed out the reason for the error. Strictly speaking, it's not an error handler. Just a bit of debugging code that you get rid of once you no longer need it.

Good luck with your db :)
 

Users who are viewing this thread

Back
Top Bottom