Reference field in query from form textbox

[tstuve]

Hi,

I've got a textbox on my main form (txtSubtotal) which I would like to show the subtotal for the current order being entered. There is a subform which allows the user to select items and quantities. I've got a field (subtotal) in a query that contains the correct data.

I put this code in the quantity_change event on the subform:

DLookup("SubTotal", "OrderSubtotal", "OrderSubtotal.OrderID = Parent!OrderID")

This works somewhat, but for some reason gives erroneous results (the value in the query is correct)

Can't I put an SQL statement in the ControlSource for this textbox? I've tried this, but keep getting the #Name? error. Here's the sql

select orderSubTotal.subtotal from ordersubtotal where ordersubtotal.orderid = Parent.OrderID;

Any help is appreciated.

Thanks

 

[bat17]

I don't think that you can use the SQL as a record source but you could use it in code and add the value that way.

Dim rst As DAO.Recordset
Set rst = CurrentDb().OpenRecordset("select orderSubTotal.subtotal from ordersubtotal where ordersubtotal.orderid = Parent.OrderID;")
Me.Parent("txtSubtotal") = rst.Fields(0)


HTH

Peter