updating all records in subform

Posted on 2013-05-31
Medium Priority
Last Modified: 2013-06-18
I have a little code that copies amounts from one field to another but it is only updating the first record in the subform. How would I get it to update all the records in the given subform?

If Me.AccountTypeID = 1 Then
[sbfrmOrders].Form.UnitPrice = [sbfrmOrders].Form.cUnitPrice
[sbfrmOrders].Form.TotalPrice = [sbfrmOrders].Form.Quantity * ([sbfrmOrders].Form.cUnitPrice)
[sbfrmOrders].Form.SpecialDiscount = 0
[sbfrmOrders].Form.UnitPrice = [sbfrmOrders].Form.cUnitPrice
[sbfrmOrders].Form.TotalPrice = [sbfrmOrders].Form.Quantity * ([sbfrmOrders].Form.cUnitPrice - [sbfrmOrders].Form.cUnitPrice * [sbfrmOrders].Form.SpecialDiscount)
End If

Open in new window

Question by:Shawn
1 Comment
LVL 20

Accepted Solution

GrahamMandeno earned 2000 total points
ID: 39212214
Hi Shawn

You can visit every record in a form (or subform) by looping through the RecordsetClone:
Dim rsc as DAO.Recordset
Set rsc = [sbfrmOrders].Form.RecordsetClone
Do Until rsc.EOF
    If Me.AccountTypeID = 1 Then rsc!SpecialDiscount = 0
    rsc!UnitPrice = rsc!cUnitPrice
    rsc!TotalPrice = rsc!Quantity * rsc!UnitPrice * (1 - rsc!SpecialDiscount )

Open in new window

Best wishes,
Graham Mandeno [Access MVP 1996-2013]

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question