Problem running parameter queries using ADO

[Becky]

I am trying to run the following code which basically fills in a bunch of
tables with data for some reports. I am having problems with the section
“Individual Product Appendicesâ€. What it does here is loop through a table
collecting the values for the parameter queries. However, I seem to end up
with the same data in each table when the code finishes running (the data
from the first time through). It is like the parameters have not been
changed even though I have stepped through the code and the parameters are
changing and it gets the correct data when done manually. Any ideas? If I
set the cmd variable to a new adodb.command each time before I run the
queries, it runs OK with correct data. Is this the proper way to do this or
am I missing something else that should be in the code. Thanks for any help!

Private Sub cmdAppendix_Click()

Dim rs As ADODB.Recordset
Dim rp As ADODB.Recordset
Dim i As Integer
Dim j As Integer
Dim qname As String
Dim strmsg As String
Dim cmd As ADODB.Command
Dim prod As String
Dim coi As String
Dim fn As String
Dim smk As String

Set rs = New ADODB.Recordset
Set rp = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = gdb

With rs
.ActiveConnection = gdb
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.Open "tblControl_dates"
End With
rs.MoveFirst

strmsg = "Appendix 1"
Call logmsg(strmsg)

txtMsg.SetFocus
txtMsg.Text = "Appendix 1 " & Now

gdb.Execute "DELETE * from tbl_appendix_1;"
gdb.Execute "qry_app_appendix_1"

strmsg = "Appendix 2"
Call logmsg(strmsg)

txtMsg.SetFocus
txtMsg.Text = "Appendix 2 " & Now

gdb.Execute "DELETE * from tbl_appendix_2;"
gdb.Execute "qry_app_appendix_2"

strmsg = "Appendix 3"
Call logmsg(strmsg)

txtMsg.SetFocus
txtMsg.Text = "Appendix 3 " & Now

gdb.Execute "DELETE * from tbl_appendix_3;"
gdb.Execute "qry_app_appendix_3"

strmsg = "Appendix 4"
Call logmsg(strmsg)

txtMsg.SetFocus
txtMsg.Text = "Appendix 4 " & Now

gdb.Execute "DELETE * from tbl_appendix_4_all_data;"
gdb.Execute "DELETE * from tbl_appendix_4"
gdb.Execute "qry_app_appendix_4_alldata"

j = 1

For i = rs!StartYear To rs!EndYear
qname = "qry_app_appendix_4_y" & Format(j)
cmd.CommandText = qname
cmd.Execute Parameters:=Array(i)
j = j + 1
Next

gdb.Execute "qry_app_appendix_4_all"

strmsg = "Appendix by table"
Call logmsg(strmsg)

txtMsg.SetFocus
txtMsg.Text = "Appendix by table " & Now

gdb.Execute "DELETE * from tbl_appendix_bytable;"
j = 1

For i = rs!StartYear To rs!EndYear
qname = "qry_app_appendix_bytable_y" & Format(j)
cmd.CommandText = qname
cmd.Execute Parameters:=Array(i)
j = j + 1
Next
gdb.Execute "qry_app_appendix_bytable_all"

' individual product appendices

With rp
.ActiveConnection = gdb
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.Open "tbl_appendix_product"
End With

rp.MoveFirst

Do Until rp.EOF
If rp!appendix = "Y" Then

prod = rp!product
coi = rp!coitype

gdb.Execute "DELETE * from tbl_appendix_individual;"
gdb.Execute "DELETE * from tbl_appendix_individual_a;"

smk = "N"
qname = "qry_app_appendix_individual_nsmk"
cmd.CommandText = qname
cmd.Execute Parameters:=Array(prod, coi, smk)

smk = "S"
qname = "qry_app_appendix_individual_smk"
cmd.CommandText = qname
cmd.Execute Parameters:=Array(prod, coi, smk)

smk = "O"
qname = "qry_app_appendix_individual_oth"
cmd.CommandText = qname
cmd.Execute Parameters:=Array(prod, coi, smk)

qname = "qry_app_appendix_individual_all"
cmd.CommandText = qname
cmd.Execute Parameters:=Array(prod, coi)

j = 1
For i = rs!StartYear To rs!EndYear
qname = "qry_app_appendix_individual_y" & Format(j)
cmd.CommandText = qname
cmd.Execute Parameters:=Array(prod, coi)
j = j + 1
Next

qname = "qry_app_appendix_individual_yall"
cmd.CommandText = qname
cmd.Execute Parameters:=Array(prod, coi)

fn = "tbl_appendix_" & rp!app_name
gdb.Execute "DELETE * from " & fn & ";"
gdb.Execute "INSERT INTO " & fn & " SELECT tbl_appendix_individual.*
FROM tbl_appendix_individual;"
gdb.Execute "INSERT INTO " & fn & " SELECT tbl_appendix_dummy.* FROM
tbl_appendix_dummy;"
fn = fn & "_a"
gdb.Execute "DELETE * from " & fn & ";"
gdb.Execute "INSERT INTO " & fn & " SELECT
tbl_appendix_individual_a.* FROM tbl_appendix_individual_a;"
gdb.Execute "INSERT INTO " & fn & " SELECT tbl_appendix_dummy_a.*
FROM tbl_appendix_dummy_a;"
End If
prod = ""
coi = ""
smk = ""
rp.MoveNext
Loop

strmsg = "Appendices done!"
Call logmsg(strmsg)

txtMsg.SetFocus
txtMsg.Text = "Done! " & Now

rs.Close

End Sub