How to get SQL returned value displayed on my form 's unbound textbox ?

smyeong

Registered User.
Local time
Today, 23:26
Joined
Mar 17, 2003
Messages
27
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
 
DoCmd.RunSQL only works in conjunction with an ACTION query. You are trying to use a SELECT query.
 
Like you said, use a recordset based on the query, and use the recordset as the datasource, then clear the recordset once finished.
 

Users who are viewing this thread

Back
Top Bottom