Unbound text box real time updates

[DB2010MN26]

I have a form setup that connects to a simple table that lets me edit it through datasheet view. Additionally, I have an unbound text box above my datasheet on the form which calculates a total value from the Tax field on a my table. My problem is I can't seem to get the unbound text box to update real time when I make changes to my table, the only way I can get it to update is by closing the form and reopening. Is there anyway to avoid having to do this, so the unbound text box calculates the total value real time?

[alansidman]

In the after update event for the each field that you would edit enter

me.requery

Alan

[DB2010MN26]

I'm not seeing that in the options in the property sheet for my table. Just to clarify, Within the form, I'm making actual changes to a table, and the unbound text box is calculating totals off that table.

[WAstarita]

In the AfterUpdate event of the text box your boss is entering the new value

Me.Requery

Since afterupdate only fires after the control loses focus, if your boss is old school he would need a clear way to do that, you might want to add a calculate button and put Me.Requery in there too. This way if he tabs away from the text box or clicks the button its the same effect. If he wants to be able to type and hit enter to make the value calculate, set the Default property of the button to Yes

[alansidman]

Just to clarify, Within the form, I'm making actual changes to a table,

Let me make sure I understand. You have a form. Within that form, you have a subform that is showing in datasheet format (looks like a table but is still a form.) In the design view of the subform, in the properties, you will find events.

If this is not what is happening, then you will need to explain further. You should not be entering data directly into a table. You should be using a form (even if it looks like a table--datasheet view).

Alan

[DB2010MN26]

Sorry for the confusion, yes you are correct. This is a form with a subform showing the datasheet format.

So I went into the form, and opened up the properties box and found the Afterupdate event. Entered in me.requery, but I still does not update. Does me.requery need to be entered differently? As VBA code?

update: I entered it in as VBA code in the code builder and still no luck..

[alansidman]

Can you post a copy of your db? Make sure to sanitize it for confidential information and replace that with dummy data. Run a compact and repair to keep the size down before you upload. Make sure that there is sufficient data (not necessarily all) to run some tests.

Alan

[DB2010MN26]

Just pm'd you it
Thanks

[DB2010MN26]

Link pasted below with the database to download. I'm simplified it to just the form and table. The field I'm trying to get to sum real time is "Total Trade Amount" which is calculated off the [TradeAmt] field within the table.

http://www.mediafire.com/?yahzu803gupnntd

[alansidman]

I understand what you have now. You have put your table in the main form. I would urge you to remove the table. Create a form that is bound to the table. Arrange the field orders as you would like. In the properties for the form, set the default as datasheet view. It will look like your table but you can now manipulate data which you cannot do directly in a table. Insert this form as a subform in your main table making the appropriate parent child link. Now in the fields that you will be editing data in, put a me.requery or me.refresh in the afterupdate events for those fields in the subform.

It is not a good idea to manipulate data directly in a table. You have less control over what you are doing.

Alan

[DB2010MN26]

Thanks, I made the corrections you pointed out. It now reads off a subform in datasheet view rather than the actual table. Does the me.requery need to be entered as VBA or can I just enter it right in the space for After update? Also, does me.requery need to be in the properties for my unbound text thats totaling the field?

[DB2010MN26]

Ok, so after some reading, I'm assuming you mean to insert the me.requery code in VBA using the Code Builder. So with that being said, in the After Update event for the [TradeAmt] field that I want my Unbound text box to total off of (Unbound text box object name is Text15). I enter the following:

Option Compare Database
Private Sub TradeAmt_AfterUpdate()
Me.Requery Text15
End Sub

However, now when I attempt to update the [TradeAmt] field in the sub form, I get the following error and the debugger pops up:

Compile error:
Wrong number of arguments or invalid property assignment

Also, not sure if it matters, but these are the current references I have loaded:
>Visual Basic for Applications
>Microsoft Access 14 Object Library
>Microsoft Excel 14 Object Library
>Microsoft Data Access Components Installed Version
>Microsoft ActiveX Data Objects 6.0 Library
>Microsoft Office 14 Access database engine Object Library

[alansidman]

Try this:

Option Compare Database
Private Sub TradeAmt_AfterUpdate()
Me.Requery
End Sub

Remove the reference to Text15

[DB2010MN26]

Still no luck, I removed all the other code and only put in the me.requery for the afterupdate of the TradeAmt field.

updated db:
http://www.mediafire.com/?b0bkr2awk3hfl7m

[DB2010MN26]

Managed to figure out the solution. I just specified the entire form to Requery in the afterupdate.

Private Sub TradeAmt_AfterUpdate()
Forms("USER_frm_AssetTradeEditor").Requery
End Sub