Hi, all. I was wondering how to finetune below code to allow to my inbound combox box named 'code' to show query 's return value on my main form's textbox ? I did it by using Code_afterUpdate event .
I got 1 main form called frm_itemRequestHead which is link up to subform called frm_ItemRequestDetail. The subform got inbound combox box called code.
The main form got an unbound textbox called txtbalance
The query i wrote is tested ok when run in SQL view.
Below is my attempt to do it but it doesn't work and end up compile error : Argument not optional
Private Sub Code_AfterUpdate()
[Forms]![frm_itemRequestHead].[txtbalance] = DoCmd.RunSQL 'SELECT Nz(Sum([ILQTY]),0)AS Cbalance From tbl_itemledger WHERE (((tbl_itemledger.ildate) <= #" & Date & "#))GROUP BY tbl_itemledger.ILLITM HAVING (((tbl_itemledger.ILLITM)='" & [Forms]![frm_itemRequestHead]![frm_itemRequestDetail].[Form]!Code & "'));"
Hope u guys can understand what i was trying to say. Somebody told me above statement can not display the return value unless recordset is used. So, i am stuck here. Pls enlighten...
Thanks and best Regards
I got 1 main form called frm_itemRequestHead which is link up to subform called frm_ItemRequestDetail. The subform got inbound combox box called code.
The main form got an unbound textbox called txtbalance
The query i wrote is tested ok when run in SQL view.
Below is my attempt to do it but it doesn't work and end up compile error : Argument not optional
Private Sub Code_AfterUpdate()
[Forms]![frm_itemRequestHead].[txtbalance] = DoCmd.RunSQL 'SELECT Nz(Sum([ILQTY]),0)AS Cbalance From tbl_itemledger WHERE (((tbl_itemledger.ildate) <= #" & Date & "#))GROUP BY tbl_itemledger.ILLITM HAVING (((tbl_itemledger.ILLITM)='" & [Forms]![frm_itemRequestHead]![frm_itemRequestDetail].[Form]!Code & "'));"
Hope u guys can understand what i was trying to say. Somebody told me above statement can not display the return value unless recordset is used. So, i am stuck here. Pls enlighten...
Thanks and best Regards