Update changes on datasheet of splitform

[Kevo]

Hi,



I have a split form in access 2007 with form above and datasheet below. On the form I have a few text boxes (qtyused,qtyreceived,etc...) and an "Update" button that has some calculations in the click on event.

So, the user chooses the record in the datasheet below and the details of that record pop-up in the form above. They then put the number of parts they used in "QtyUsed" text box and hit update. This changes the "QtyOnHand" on the form. This all works great.

My question is.....The "QtyOnHand" field in the datasheet view below does not update until you move to another record. Can I force it to update when the user clicks the "Update" button on the top part of the splitform?

Thank You,
Kevin

[June7]

Sounds like you need to save the record.

DoCmd.RunCommand acCmdSaveRecord

[Kevo]

Not sure where you wanted me to put that June7, but it didn't work in the On Click event.

Here is what I have currently in the OnCLick event of the "Update" button.

Private Sub btnUpdate_Click()
Me.QtyOnHand = Me.QtyOnHand - Me.QtyUsed + Me.QtyReceived
Me.QtyInTray = Me.QtyInTray - Me.QtyUsed + Me.QtyAddedTray
Me.QtyAddedTray.Value = "0"
Me.QtyUsed.Value = "0"
Me.QtyReceived.Value = "0"

Tried to add "Me.Requery" here and that instantly updates the datasheet but it also sends you back to the first record. So that wont work!
End Sub

Is there anything I can add to the bottom to update the datasheet part of the split form? Some kind of requery maybe?

Thanks for any help!

[June7]

That is the conundrum with having aggregate calculations on a form. Ideally, data is entered without regard to totals and calculations are performed on reports.

Try Refresh instead of Requery. But I think will have to commit record edits to table first. Record is committed when move to another record, close form, or acCmdSaveRecord.

[Kevo]

Me.Refresh did the trick!

Thanks June7. Awesome help here as usual!

Kevin