APPEND QUERY ERROR. NO VALUE GIVEN FOR REQUIRED PARAMATER 1

[jadams0173]

When I try to run the query below in VBA like this
Currentproject.Connection.Execute strSql

I get and error stating that one or more required paramaters are missing.

However if I use the DoCmd.RunSQL strSql method it seems to run fine. It will also run fine inside access. Any ideas?

INSERT INTO tblReleased ( MO_NUMBER, ITEM, ORDER_QTY, SCHED_DATE, MOTYPE, FEEDERTOTAL, WH_PICKS1, RELEASED, TotalPicks, TotalFeedersNeeded, [Date], [time], lines, PRIORITY, COMMENT ) 

SELECT Schedule_1.MO_NUMBER, Schedule_1.ITEM, Schedule_1.ORDER_QTY, Schedule_1.SCHED_DATE, Schedule_1.MOTYPE, Sum(Schedule_1.TotalFeeders) AS FEEDERTOTAL, Schedule_1.WH_PICKS1, Schedule_1.RELEASED, Int([forms]![frmmain]![txtPicksReleased]) AS TotalPicks, Int([forms]![frmmain]![txtReleasedFeeders]) AS TotalFeedersNeeded, Date() AS [Date], Time() AS [time], [Schedule_1]![PrimaryB] & ' / ' & [Schedule_1]![PrimaryT] AS lines, PRIORITY, COMMENT 

FROM Schedule_1 

GROUP BY Schedule_1.MO_NUMBER, Schedule_1.ITEM, Schedule_1.ORDER_QTY, Schedule_1.SCHED_DATE, Schedule_1.MOTYPE, Schedule_1.WH_PICKS1, Schedule_1.RELEASED, Date(), Time(), [Schedule_1]![PrimaryB] & ' / ' & [Schedule_1]![PrimaryT], PRIORITY, comment 

HAVING (((Schedule_1.RELEASED)=-1))

 

[rjoubert]

I would suggest changing the names of the [Date] and [time] fields in your tblReleased table.

Also, have you verified that the form fields you reference in the "SELECT" portion of your code have values?

 

[jadams0173]

rjoubert,

Sorry taking so long to get back to you. Thanks for responding. I tried changing the [date] and [time] as suggested but still encountered the error.

 

[PHV]

Any chance you could post the code populating the strSql variable ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

Sorry for the delay. Here is the code

sql = "INSERT INTO tblReleased ( MO_NUMBER, ITEM, ORDER_QTY, SCHED_DATE, MOTYPE, FEEDERTOTAL, WH_PICKS1, RELEASED, TotalPicks, TotalFeedersNeeded, RELDate, RELtime, lines, PRIORITY, COMMENT )" _
    & "SELECT " & TempTable & ".MO_NUMBER, " & TempTable & ".ITEM, " & TempTable & ".ORDER_QTY, " & TempTable & ".SCHED_DATE, " & TempTable & ".MOTYPE, Sum(" & TempTable & ".TotalFeeders) AS FEEDERTOTAL, " & TempTable & ".WH_PICKS1, " & TempTable & ".RELEASED, Int([forms]![frmmain]![txtPicksReleased]) AS TotalPicks, Int([forms]![frmmain]![txtReleasedFeeders]) AS TotalFeedersNeeded, " & TempTable & ".RELDATE, " & TempTable & ".RELTIME, [" & TempTable & "]![PrimaryB] & ' / ' & [" & TempTable & "]![PrimaryT] AS lines, " & TempTable & ".PRIORITY, " & TempTable & ".COMMENT " _
    & "FROM " & TempTable _
    & " GROUP BY " & TempTable & ".MO_NUMBER, " & TempTable & ".ITEM, " & TempTable & ".ORDER_QTY, " & TempTable & ".SCHED_DATE, " & TempTable & ".MOTYPE, " & TempTable & ".WH_PICKS1, " & TempTable & ".RELEASED, " & TempTable & ".RELDATE, " & TempTable & ".RELTIME, [" & TempTable & "]![PrimaryB] & ' / ' & [" & TempTable & "]![PrimaryT], " & TempTable & ".PRIORITY, " & TempTable & ".COMMENT " _
    & "HAVING (((" & TempTable & ".RELEASED) = -1))"

 

[PHV]

You may try something like this:
sql = "INSERT INTO tblReleased ( MO_NUMBER, ITEM, ORDER_QTY, SCHED_DATE, MOTYPE, FEEDERTOTAL, WH_PICKS1, RELEASED, TotalPicks, TotalFeedersNeeded, RELDate, RELtime, lines, PRIORITY, COMMENT )" _
& "SELECT MO_NUMBER, ITEM, ORDER_QTY, SCHED_DATE, MOTYPE, Sum(TotalFeeders) AS FEEDERTOTAL, WH_PICKS1, RELEASED, [!]" & Val([/!][forms]![frmmain]![txtPicksReleased]) [!]& "[/!] AS TotalPicks...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

All I can say is you are the man!!! I had to make that change in one other place in the query but that did the trick!!!

sql = "INSERT INTO tblReleased ( MO_NUMBER, ITEM, ORDER_QTY, SCHED_DATE, MOTYPE, FEEDERTOTAL, WH_PICKS1, RELEASED, TotalPicks, TotalFeedersNeeded, RELDate, RELtime, lines, PRIORITY, COMMENT )" _
    & "SELECT " & TempTable & ".MO_NUMBER, " & TempTable & ".ITEM, " & TempTable & ".ORDER_QTY, " & TempTable & ".SCHED_DATE, " & TempTable & ".MOTYPE, Sum(" & TempTable & ".TotalFeeders) AS FEEDERTOTAL, " & TempTable & ".WH_PICKS1, " & TempTable & ".RELEASED,  [red]" & Val([Forms]![frmmain]![txtPicksReleased]) & " AS TotalPicks, " & Val([Forms]![frmmain]![txtReleasedFeeders]) & " AS TotalFeedersNeeded,[/red] " & TempTable & ".RELDATE, " & TempTable & ".RELTIME, [" & TempTable & "]![PrimaryB] & ' / ' & [" & TempTable & "]![PrimaryT] AS lines, " & TempTable & ".PRIORITY, " & TempTable & ".COMMENT " _
    & "FROM " & TempTable _
    & " GROUP BY " & TempTable & ".MO_NUMBER, " & TempTable & ".ITEM, " & TempTable & ".ORDER_QTY, " & TempTable & ".SCHED_DATE, " & TempTable & ".MOTYPE, " & TempTable & ".WH_PICKS1, " & TempTable & ".RELEASED, " & TempTable & ".RELDATE, " & TempTable & ".RELTIME, [" & TempTable & "]![PrimaryB] & ' / ' & [" & TempTable & "]![PrimaryT], " & TempTable & ".PRIORITY, " & TempTable & ".COMMENT " _
    & "HAVING (((" & TempTable & ".RELEASED) = -1))"

Enjoy another star on me!!!