Subform "assigned" control not updating

Question

I'm creating a MS Access 2010 database to allow users enter series of data which would be used in a query.

I have a Main form named "Entry" and a bounded subform named "SubEntry" (used to view the entered records). The main form (Entry) consists of text/combo box controls that allows users to key in values, such as:

**Controls       ControlSource**

Date             "Entered by the user"
CompanyID        "Entered by the user"
TicketID         "Entered by the user"
TicketPrice      "Automatic retrieved by a query based on the entered 'Date' and 'TicketID' "
Pieces           "Entered by the user"
TotalPrice       "TicketPrice * Pieces"
CorrectedPrice   "Entered by the User"
Revenue          "=IIf([CorrectPrice]=0,[TotalPrice],[CorrectPrice])"

This works fine with entering and viewing record-sets. But the problem is, at the initial stage when the user adds recordset to the subform, the exact "Revenue" value is added to the subform's "Revenue" field. But after then, when the user scrolls in the subfrom and chooses a recordset (which appears in the main form's respective controls) and changes the values. For instance chooses another ticketID or Date, the "Revenue" value in the main form changes but not updated in Subform's Revenue field. Meanwhile anyother changes made main form automatically updates in the subform but not that of the "Revenue" value.

I'm not that conversant with MS Access nor VBA, just wondering if there is something obvious that is missing? How to deal with the SubEntry's "Revenue" field to automatically updates like any other fields in the subform does.

Thanks for your time and assistance.

@Remou: Revenue value is based on the the IIF statement: =IIf([CorrectPrice]=0,[TotalPrice],[CorrectPrice])which is then added to a table. It works fine but it doesn't update the table (subform) when the user alters the calculations. — Origin
You will need to update the table in the after update event of any control that changes correct price or total price. It is often best not to store the result of a calculation, just recreated it in a query. However, if this is a sales price, it needs to be stored and then never changed, for accounting purposes. — Fionnuala
@Remou : The thing is that the calculation and the IIF statement works perfectly fine in the main form. The problem is from the subform's 'Revenue field' the control source is bounded by IFF statement which doesn't update when the value is modified in the main form. I've tried using After Update Event but it doesn't work. When I unbound the field to allow user enter value manually, it does update in the subform when modification is done. Do you know what could be the problem with the field bounded to the IIF statement? Thanks — Origin
@Remou: Is there a way to refresh/requery a field in a subform? I tried using this code but it returns error ` Me!Subform1.Form!ControlName.refresh` it returns Run time error 438: Object doesn't support this property or method. — Origin

Fionnuala Fionnuala · Accepted Answer · 2012-11-09T16:11:44

You need to associate the textbox with the relevant field in the subform, so:

txtEinnahme
ControlSource: KorrEinahme

Then you need code to add the calculation, for example:

Private Sub txtpiece_AfterUpdate()
    Me.txtEinnahme = IIf(Me.[DKorrEin] = 0, Me.[txtTotal], Me.[DKorrEin])
End Sub

Put similar code in any control should change the value of txtEinnahme.

Subform "assigned" control not updating

1 Answers