VBA only appends on second run?

[schulzy175]

Here is my code:

Private Sub runreport_Click()

DoCmd.SetWarnings False
Dim runn As Integer
Dim starttime As String
Dim endtime As String
Dim ftime As String


Dim ssql(1 To 7) As String
ssql(1) = "LMSB_Recharges"
ssql(2) = "LMSB_Volume"
ssql(3) = "LMSB_Wextras"
ssql(4) = "LMSB_WType_Q"
ssql(5) = "PriorSales_Append"
ssql(6) = "GF_NoNote_RemovedARM"
ssql(7) = "GF_Month_Signup"


starttime = Timer


For runn = 1 To 7
    DoCmd.OpenQuery ssql(runn)
Next runn
DoCmd.SetWarnings True


endtime = Timer
ftime = Format(((endtime - starttime) / 86400), "hh:mm:ss")
Debug.Print ftime
DoCmd.OpenReport "TestReport_Table", acViewReport
End Sub

I do not know where to begin to do research on this issue. For some reason, If I run this code once, nothing appends except my sales. If I run it a 2nd time, all the following queries append including the sales twice. Any clue why the queries don't append on the first run?

[schulzy175]

Update: So I ran it step by step and on

docmd.openquery "LMSB_Recharges"

it just skips running the query. It doesn't actually skip the line, but it registers and runs the line of code, but nothing gets run.

[davegri]

Arrays are zero based, so try restructuring your array code to set up as 0-6.

[schulzy175]

So I changed

DoCmd.OpenQuery

to

CurrentDB.Execute

and now it's not recognizing any of the queries. All the queries listed in my code are action queries.

[pbaldy]

To me it's an over-complication of things to populate an array, then loop through it. Why not just have 7 lines to execute queries, either with OpenQuery or Execute? You can add to Execute to make it error, it will fail silently if not included:

CurrentDb.Execute "QueryName", dbFailOnError

[schulzy175]

To me it's an over-complication of things to populate an array, then loop through it. Why not just have 7 lines to execute queries, either with OpenQuery or Execute? You can add to Execute to make it error, it will fail silently if not included:

CurrentDb.Execute "QueryName", dbFailOnError

Thanks for the suggestion. When I first chose to run multiple queries back to back using VBA, I didn't know if the best way would be an array or just lines for each query.

[pbaldy]

No problem. Arrays have their place, but this didn't seem like one of them to me.

[schulzy175]

I am still getting a parameter error (missing parameters 1) when it reaches "PriorSales_Append". I'm still doing some debugging so I'll keep everyone posted.

[schulzy175]

So I figured out my problem. On my query "PriorSales_Append", I have a date range that uses my forms textboxes. So I just needed to change my VBA to run SQL instead of Currentdb.Execute. All other queries are run from "PriorSales_Append_Table", which is the appended table of "PriorSales_Append."

[schulzy175]

Update: So I fixed the issue with parameters, but still it doesn't run my action queries. I've purposes added both to debug:

DoCmd.OpenQuery "LMSB_Recharges"
CurrentDb.Execute "LMSB_Recharges"

It gives no errors, but just goes to the next line not executing anything?

[pbaldy]

Did you try adding the dbFailOnError argument as mentioned earlier?

[schulzy175]

Ok, I've really found the solution this time. So my

DoCmd.SetWarnings False

was basically selecting no when asked to append the results/make changes to the table.

[schulzy175]

Apologies for the run-around. I appreciate all the help. Still learned stuff though Thanks again!

[pbaldy]

Really? SetWarnings should just suppress the warnings, basically answering yes so the query still runs.

[schulzy175]

Yea, the last thing I could think of to debug it was to set warnings back to true in the beginning and voila, it asked if I wanted to append the results, but when I re-ran the code setting warnings back to false, it just went to the next line of code not appending anything. I figured setting warnings to false would automatically choose yes, but this is the first case where that isn't the case. Now I need to do some research to see what I need to do to have it choose yes. I was going to just run the whole thing as an actual SQL statement in VBA, but I kept getting an error "Query must contain at least one query or table". So I just reverted back to DoCmd.RunQuery.