Find current record number on a form

[Guest]

I have a sales form and a subform for entering product. After entering new
records in the subform, the total of some values from the products entered is
captured in a text box on the subform using the SUM function. The total
value is updated on the main sales form when I run an update query from a
command button on the main form. The update query works just fine, but after
the new value is updated on the main form from the sum value of a text box on
the subform, I immediately jump back to the first record on the main form,
not the record that I was just in and updated a value in. How can I stay on
the current record that I am in after updating the value on the main form?
can you help?

 

[Guest]

Hi Boomer,

You should not need an update query at all to achieve this result. The fact
that you are using an update query indicates to me that you are storing the
results of a calculation in a table. In general, you want to calculate stuff
on-the-fly, instead of trying to retrieve a previously calculated value.

Try this tutorial:

Displaying Subtotals and a Grand Total on a Form
That Displays a One-to-Many Relationship
http://www.access.qbuilt.com/html/subtotals.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Yes, it fact I did use the update query because I am actually updating the
field also on the main form. After the update query is run with a macro, I
requeried the form. That is when the main form jumped back to the first
record.
Any ideas?

 

[Guest]

Yes, it fact I did use the update query because I am actually updating the

field also on the main form.....Any ideas?

Yes, I've already told you that you should *not* be attempting to store the
results of a calculation. You're on your own, if you insist on doing so.
While it is certainly possible to do what you want to do, it is not
advisable. I'm not going to be the one to lead you down that path. Storing
the results of a calculation violates both 2nd and 3rd normal forms of
database design. This is not Excel, it is Access.


Good Luck.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

I am sorry to be such a pest. I do understand the concept you are outlining
to me. I am a novice Access user and am creating a tanning salon database.
I have created a sales form including such fields as SalesID, SalesDate,
CustomerID, etc. Attached to that form I have a subform in which I add
minute packages (Product). Each time I add a new product in the subform, a
calculated field in the subform footer adds, using the SUM function, all the
new minutes together. So I am looking for a way to update the
TotalCustomerMinutes in the Customer table. At present, after adding all the
minute packages to each Sales-SalesDetails forms, I am using a command button
on the main form to run an update query to add the new minutes from the
calculated field on the subform to the current value of the
TotalCustomerMinutes field in the Customer table. Hopefully, I can do this
programmatically either by using the AfterInsert or AfterUpdate event of the
subform. Am I still taking the wrong approach or is there a more prudent
methodology? Any assistance would be appreciated.