Run-time error ‘3464’

[weavind]

I have an Invoices form (frmInvoices) which has a sub form linked to it (subfrmInvoiceDetails). These get their data from the two tables, tblInvoice and tblInvoiceDetail. Basically, I would like to take the subtotal of the subform and write that value to the main form (value to be stored in the table). I have written the code below, but I keep getting the error “Run-time error ‘3464’: Data type mismatch in critical expression.” If I debug the code, the last line of code has the following message: “rst!SubTotal = <Object variable or With block variable not set>” Any ideas of what I am doing wrong?

Private Sub Form_AfterUpdate()
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblInvoiceDetail.INoID, Sum(tblInvoiceDetail.Total) as SumOfTotal FROM tblInvoiceDetail GROUP BY tblInvoiceDetail.INoID HAVING (((tblInvoiceDetail.INoID)='" & Form_frmInvoices.INoID & "'));")
Form_frmInvoices.txtTotal = rst!SumOfTotal
End Sub

 

[JANR]

I would like to take the subtotal of the subform and write that value to the main form (value to be stored in the table).

You should NOT store this value in a table!!!! also why complicate things with recalculating when you can let Access do it for you.

Just create a hidden unbound field in the formfooter of your subform and set it's ControlSource to : =Sum([Price]) and call it txtTotal and set the default value to zero.

Then on your mainform create a Unbound textbox and set it's control source to:

=[NameOfSubformConteiner].Form!txtTotal

All done

JR

 

[weavind]

Thanks JR, That works fine. Can you show me how to call this value in a report?

 

[JANR]

In the detail section of the report you can put a hidden textbox with controlsource =Price ( just refer to the pricefield) and set the property of running sum to: over all.

Then in the report footer you put the salestotal contol's textbox to:

=[NameOfRunningSumtextbox]

JR

 

[weavind]

Thanks, you made it so easy. Works prefectly now!

 

[JANR]

Glad to help, goodluck with the rest of your project

JR