Trying to get updated data for calculation 1

[mbaddar]

Hi,

Sorry about this lengthy question. I have a Sales Order Form with a subform. The subform has a set of product numbers, descriptions and their prices. A user (presumably a product distributor) can change the prices on the subform. There is a textbox underneath the subform to display the changing calculated price. Each time a user changes the prices, the total calculated price should change (I'm using the dsum method to calculate this price based on the prices values in the table).

The problem is my DSum calculation only calculates based on the prices that were in the table when the form loaded. So, no matter what prices I change when the form is open, the total calculated price only changes after I close the form and load it again.

Hope this question is clear. Any ideas?

Thanks so much for any help.

mbaddar

 

[jflachman]

In the AfterUpdate event on the price field in the subform, put the line

Me.Requery

In the OnCurrent event in the subform field, put the following line

Me![Total] = DSum("SubformName","Price")

This has the unfortunate effect of moving you back to the first record in the subform whenever you change a price.
Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[mbaddar]

Thanks Jonathan. I ended up doing something like what you suggested and you're right, unfortunately, it does move me back to the first record. Do you know if anything can be done about this?

Thanks,
mbaddar

 

[jflachman]

Actually, now that I look at it again, it's an easy fix.

Change the code in the OnCurrent event to:

Dim strBkmk As String
strBkmk = Me.Bookmark
Me.Requery
Me.Bookmark = strBkmk
me![Price].setfocus 'Or set the focus to the next field in the form

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[mbaddar]

Hi,

I'm not that familiar with the bookmark. In your code example what does the bookmark refer to?

Thanks,
mbaddar

 

[jflachman]

The bookmark is exactly that, a bookmark. You can set it for a specific record, normally the record that is current. You do this by saving the bookmark for that record in a string variable. To return to that record, just set the bookmark for the form equal to the string value you saved.

From Access Help:

When a bound form is opened in Form view, each record is assigned a unique bookmark. In Visual Basic, you can save the bookmark for the current record by assigning the value of the form's Bookmark property to a string variable. To return to a saved record after moving to a different record, set the form's Bookmark property to the value of the saved string variable. Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[mbaddar]

Hi Jonathan,

I understand what you're saying and what you told me did work perfectly. I hate to ask another question, but do you know if there is a way to check in code whether a user is entering in a new record. Because on my subform a user can enter a new product number and its price. After a price is entered for this new record, the requery recalculates the total. At this point, the cursor moves back to the last saved record. So, it seems like I need to check if a record is new and if it is, I need to do a movenext after my requery.

Do you have any idea how I can do this?

Thank you very much.

mbaddar

 

[jflachman]

The property Me.NewRecord is True if the current record is new. False if not.
Therefore...

If (Me.NewRecord) Then

'Insert code here

Else

'Insert code here

End If

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[mbaddar]

Thanks a ton Jonathan. That did it.

mbaddar