vba help to run an append with parameters

[tuktuk]

hey there....i am super close but can figure out how to run the append query itself....

i have an append query who contain a month field. I have [parameter] in the criteria line to prompt me to enter the desired month that i want to query. i need to query all months 1 thru 12 so i've creete a tbl_month and want to pass through each MonthNo and run the query.

i am lost on the part where you define the qdf but can figure out how to run the qdf once the parameter is set. i've attempted "DoCmd.Requery (qdf)" but no go.

thank you so much in advance.
Tuktuk

Here's my code:

Public Function UpdateODBCPull()

Dim StrMonth As String
Dim rsMonth As Recordset, rsUpdate As Recordset
Dim x As Integer
Dim qdf As QueryDef


Set rsMonth = CurrentDb.OpenRecordset("tbl_Month")

rsMonth.MoveLast
rsMonth.MoveFirst

DoCmd.SetWarnings False

Set qdf = CurrentDb.QueryDefs("qry_ODBC_Prod_Codes_DAILIES_APPEND")
    
    For x = 1 To rsMonth.RecordCount
    qdf.Parameters(0) = rsMonth.Fields("MonthNo")

THIS WHERE IS AM STRUGGLING
I've tried the following:
    DoCmd.Requery (qdf)

AND 

Dim SQL As String

Set SQL = SEE BELOW FOR FULL SQL (copied directly form the qry)

    DoCmd.RunSQL SQL


    rsMonth.MoveNext
    Next x

DoCmd.SetWarnings True
Set rsMonth = Nothing
End Function

am i on the right track. am i making it harder that it needs to be with SQL?

here is my appends query's sql:

INSERT INTO [Daily RMT] ( Field1, Field2, Field3, Field6, Field4, Field5, Field7, Field8, Field9, Field10, Field11, Field12, Field13, Field14, Field15, Field16, Field17, Field18, Field19, Field20, Field21, Field22, Field23, Field24, Field25, Field26, Field27, Field28, Field29, Field30, Field31, Field32, Field33, Field34, Field35, Field36, Field37 )
SELECT DS100PDLIB_DSAGTR01.DMA, DS100PDLIB_DSAGTR01.DMADES, DS100PDLIB_DSDLYP01.PRODCT, DS100PDLIB_DSDLYP01.RECTYP, DS100PDLIB_DSDLYP01.AYEAR, DS100PDLIB_DSDLYP01.AMONTH, DS100PDLIB_DSDLYP01.AMT001, DS100PDLIB_DSDLYP01.AMT002, DS100PDLIB_DSDLYP01.AMT003, DS100PDLIB_DSDLYP01.AMT004, DS100PDLIB_DSDLYP01.AMT005, DS100PDLIB_DSDLYP01.AMT006, DS100PDLIB_DSDLYP01.AMT007, DS100PDLIB_DSDLYP01.AMT008, DS100PDLIB_DSDLYP01.AMT009, DS100PDLIB_DSDLYP01.AMT010, DS100PDLIB_DSDLYP01.AMT011, DS100PDLIB_DSDLYP01.AMT012, DS100PDLIB_DSDLYP01.AMT013, DS100PDLIB_DSDLYP01.AMT014, DS100PDLIB_DSDLYP01.AMT015, DS100PDLIB_DSDLYP01.AMT016, DS100PDLIB_DSDLYP01.AMT017, DS100PDLIB_DSDLYP01.AMT018, DS100PDLIB_DSDLYP01.AMT019, DS100PDLIB_DSDLYP01.AMT020, DS100PDLIB_DSDLYP01.AMT021, DS100PDLIB_DSDLYP01.AMT022, DS100PDLIB_DSDLYP01.AMT023, DS100PDLIB_DSDLYP01.AMT024, DS100PDLIB_DSDLYP01.AMT025, DS100PDLIB_DSDLYP01.AMT026, DS100PDLIB_DSDLYP01.AMT027, DS100PDLIB_DSDLYP01.AMT028, DS100PDLIB_DSDLYP01.AMT029, DS100PDLIB_DSDLYP01.AMT030, DS100PDLIB_DSDLYP01.AMT031
FROM (DS100PDLIB_DSDLYP01 INNER JOIN ProdCodes ON DS100PDLIB_DSDLYP01.PRODCT = ProdCodes.ProdCode) INNER JOIN DS100PDLIB_DSAGTR01 ON DS100PDLIB_DSDLYP01.AGNTID = DS100PDLIB_DSAGTR01.AGNTID
WHERE (((DS100PDLIB_DSDLYP01.RECTYP) In ("RVO","RCG","RPR","PVO")) AND ((DS100PDLIB_DSDLYP01.AYEAR)="2005") AND ((DS100PDLIB_DSDLYP01.AMONTH)=[Parameter]));

tuk

 

[wongm003]

hello, since it does not sound like you need user input each time you run the append query you could use the following... it removes the parameter query altogether and just uses the SQL to append the values.

<code>
Public Sub my_append()
On Error Resume Next

DoCmd.SetWarnings False

Dim strSQL As String
Dim x As Integer

For x = 1 To 12

strSQL = _
"INSERT INTO [Daily RMT] ( Field1, Field2, Field3, Field6, Field4, Field5, Field7, Field8, Field9, Field10, Field11, Field12, Field13, Field14, Field15, Field16, Field17, Field18, Field19, Field20, Field21, Field22, Field23, Field24, Field25, Field26, Field27, Field28, Field29, Field30, Field31, Field32, Field33, Field34, Field35, Field36, Field37 ) " & _
"SELECT DS100PDLIB_DSAGTR01.DMA, DS100PDLIB_DSAGTR01.DMADES, DS100PDLIB_DSDLYP01.PRODCT, DS100PDLIB_DSDLYP01.RECTYP, DS100PDLIB_DSDLYP01.AYEAR, DS100PDLIB_DSDLYP01.AMONTH, DS100PDLIB_DSDLYP01.AMT001, DS100PDLIB_DSDLYP01.AMT002, DS100PDLIB_DSDLYP01.AMT003, DS100PDLIB_DSDLYP01.AMT004, DS100PDLIB_DSDLYP01.AMT005, DS100PDLIB_DSDLYP01.AMT006, DS100PDLIB_DSDLYP01.AMT007, " & _
"DS100PDLIB_DSDLYP01.AMT008, DS100PDLIB_DSDLYP01.AMT009, DS100PDLIB_DSDLYP01.AMT010, DS100PDLIB_DSDLYP01.AMT011, DS100PDLIB_DSDLYP01.AMT012, DS100PDLIB_DSDLYP01.AMT013, DS100PDLIB_DSDLYP01.AMT014, DS100PDLIB_DSDLYP01.AMT015, DS100PDLIB_DSDLYP01.AMT016, DS100PDLIB_DSDLYP01.AMT017, DS100PDLIB_DSDLYP01.AMT018, DS100PDLIB_DSDLYP01.AMT019, DS100PDLIB_DSDLYP01.AMT020, " & _
"DS100PDLIB_DSDLYP01.AMT021, DS100PDLIB_DSDLYP01.AMT022, DS100PDLIB_DSDLYP01.AMT023, DS100PDLIB_DSDLYP01.AMT024, DS100PDLIB_DSDLYP01.AMT025, DS100PDLIB_DSDLYP01.AMT026, DS100PDLIB_DSDLYP01.AMT027, DS100PDLIB_DSDLYP01.AMT028, DS100PDLIB_DSDLYP01.AMT029, DS100PDLIB_DSDLYP01.AMT030, DS100PDLIB_DSDLYP01.AMT031 " & _
"FROM (DS100PDLIB_DSDLYP01 INNER JOIN ProdCodes ON DS100PDLIB_DSDLYP01.PRODCT = ProdCodes.ProdCode) INNER JOIN DS100PDLIB_DSAGTR01 ON DS100PDLIB_DSDLYP01.AGNTID = DS100PDLIB_DSAGTR01.AGNTID " & _
"WHERE (((DS100PDLIB_DSDLYP01.RECTYP) In ('RVO','RCG','RPR','PVO')) AND ((DS100PDLIB_DSDLYP01.AYEAR)='2005') AND ((DS100PDLIB_DSDLYP01.AMONTH)=" & x & "));"

DoCmd.RunSQL strSQL

Next x

DoCmd.SetWarnings True

End Sub
</code>