Try to append data from another table

[monoDeveloper]

Hi everybody

I run SQL query to add dat from another table based on form object data, it works when i save as separate query but failed when i run inside VBA script command :

[COLOR=#4E9A06]'Append data to Temporary table[/color] 
DoCmd = " INSERT INTO SupplyOrderDetails_Temp " & _
" ( Supp_Order_SN, ItemID, Sup_Ord_Item_ExDate, Ord_Old_QTY ) " & _
" from ( SELECT Supp_Order_SN, ItemID, Sup_Ord_Item_ExDate, Sup_Ord_QTY " & _
" FROM SupplyOrderDetails " & _
" WHERE (Supp_Order_SN= " & Int([cbo_Order_SN]) & "))"

This is Saved query code:

INSERT INTO SupplyOrderDetails_Temp ( Supp_Order_SN, ItemID, Sup_Ord_Item_ExDate, Ord_Old_QTY )
SELECT Supp_Order_SN, ItemID, Sup_Ord_Item_ExDate, Sup_Ord_QTY
FROM SupplyOrderDetails
WHERE (Supp_Order_SN=Forms!frm_Update_Receive_Supply_Order!cbo_Order_SN);

I want to know the reason & solution please.

 

[dhookom]

Please provide the error message or other symptoms. I expect it might be something about expecting parameters of similar. If so the issue is the reference to the control on the form which doesn't exist when running the VBA.

Duane
Hook'D on Access
MS Access MVP

 

[monoDeveloper]

Erro is:


Run-time error '438':
Object doesn't support this property or method

 

[monoDeveloper]

Another modification & it's error

[COLOR=#4E9A06]'Append data to Temporary table[/color]
DoCmd.RunSQL = " INSERT INTO SupplyOrderDetails_Temp " & _
" ( Supp_Order_SN, ItemID, Sup_Ord_Item_ExDate, Ord_Old_QTY ) " & _
" from ( SELECT Supp_Order_SN, ItemID, Sup_Ord_Item_ExDate, Sup_Ord_QTY " & _
" FROM SupplyOrderDetails " & _
" WHERE (Supp_Order_SN= " & [highlight #FCE94F][cbo_Order_SN][/highlight] & "))"

Error is: Compiler Erro ... Argument not optional & refer to [highlight #FCE94F][cbrder_SN][/highlight]

 

[PHV]

What about this ?

DoCmd.RunSQL "INSERT INTO SupplyOrderDetails_Temp (Supp_Order_SN,ItemID,Sup_Ord_Item_ExDate,Ord_Old_QTY)" _
 & " SELECT Supp_Order_SN,ItemID,Sup_Ord_Item_ExDate,Sup_Ord_QTY" _
 & " FROM SupplyOrderDetails WHERE Supp_Order_SN=" & Forms!frm_Update_Receive_Supply_Order!cbo_Order_SN

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886