Updating records in a subform

  • Thread starter Thread starter my-wings
  • Start date Start date
M

my-wings

I often buy books in lots but sell them individually, and I need to allocate
lot costs over individual books in my database. What I want to do is enter
the total cost of a set of books and a total cost of shipping those books,
then figure the cost per book and place those amounts into the subform
record for each book. Then I want to add the two fields in the subform
record together to give me a single cost for that book. This will be done on
a button click, so there is no need to continuously update the number of
records or averages as I add books to the subform.

Here are the forms and fields I'm dealing with:

frmPurchases (the main form)
CostOfBooks (bound field)
CostOfShipping (bound field)
txtTotalBooks (unbound - this holds the number of records in the
subform and is used
to calculate the txtAvgCost and txtAvgShipping fields)
txtAvgCost (unbound - this will hold an amount to be put into the
subform record)
txtAvgShipping (unbound - this will hold an amount to be put into
the subform record)
sfrmBookList (the name of the control in frmPurchases that holds the
subform)
subfrmBookList (the name of the subform itself)
MyCostWOShipping (bound field - needs to be updated with amount from
txtAvgCost)
MyShippingCost (bound field - needs to be updated with amount from
txtAvgCost)
MyTotalCost (bound field - the sum of MyCostWOShipping and
MyShippingCost

I have been scouring the internet looking for pieces of code that will help
me with this, and I'm halfway there. The part I got works so far, but
somehow I don't think I have the best solution. Here what I have so far,
which counts the records in the subform, puts that number in the
txtTotalBooks field and calculates the average cost and average shipping per
book.

Private Sub btnTotalCostSpread_Click()

Dim frm As Form
Dim dblAvgCost As Double, dblAvgShip As Double

Set frm = Me.sfrmBooks.Form
With frm
.RecordsetClone.MoveLast
Me.txtTotalBooks.Value = .RecordsetClone.RecordCount
End With
Set frm = Nothing

dblAvgCost = Round(Me.CostOfBooks / Me.txtTotalBooks.Value, 2)
dblAvgShip = Round(Me.CostOfShipping / Me.txtTotalBooks.Value, 2)

Me.txtAvgCost = dblAvgCost
Me.txtAvgShipping = dblAvgShip

End Sub

Now I'm stuck as to how to update the records in the subform. I think I need
to (maybe) do something with a RecordsetClone, but I can't find anything on
the internet that looks like what I need. Can anyone help?

Thanks.
Alice
 
Hi Alice,

Be aware that you do not need to store the calculation of the cost of each
book. In fact it is against database normalization rules to do so unless
parts of the calculation can change over a period of time. Use a query to do
the calculation and the values can be displayed with each record on a form.

What I am not seeing in your design is a lot number or PurchaseID. How are
you going to show those values. That number is in the table behind
frmPurchases and will be used to define which of the apportioned values is
ascribed to each book. It is also the linking field or Foreign Key in the
table behind the subform.

Since each book in that lot has exactly the same value: AvgCost, the
calculation which may need to be displayed will be the profit. That is
calculated (but not stored) in the underlying query. I think the one number
that I would store is the total number of books purchased in that lot. That
number should be stored in the main purchases table and displayed in the
main form. If for any reason a record in the subform were not enterered, or
deleted it would recalculated the actual values for the cost per book.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Hi. Thanks for your comments.

I didn't actually put all the fields in the two forms into my first post. I
was trying to stick to the fields I thought someone would want to name if
they were helping me out with the code. I am using a PurchaseID to link the
books in the subform to the Purchases form.

I also didn't give all the cases of how I need to calculate total values,
because I thought if I had a clue on the hardest case, I could figure out
the others. If I buy books in a lot on an auction site, I would just divide
the total cost of the lot by the number of books, but sometimes I buy books
individually that are shipped together, so that the cost of the books
differs, but the shipping gets divided evenly over the books. Sometimes
there is no shipping if I buy the books locally. For that reason, I have two
fields on the subform record, one for the cost of the book and one for the
cost of the shipping. When the cost of individual books in the lot is
different, then I can fill in that part manually for each book and I plan to
have a separate button to click that will divide just the shipping.

You're right that I probably shouldn't have the "Total" field in the subform
record, since that can be calculated on the fly as needed. Right now, it's
there to be compatible with existing records that I entered before I knew I
was going to want to be this clever with the "shipping" vs. "book cost"
thing.

My problem right now, is: I don't know how to actually update the records of
the subform with the totals I've calculated. In addition to the code I had
in my original post, I've got this much more:

Set rst = Me.sfrmBooks.Form.Recordset
Do While rst.EOF = False
rst.MoveNext
Loop
Set rst = Nothing

What is there works, because when I run it, my curser winds up in the last
record of the subform. But as you can see, it's missing something in between
the "Do While" and the "MoveNext." It may be missing other things too. (I'm
thinking maybe I need to have an "openrecords" somewhere? I'm not sure.)
And of course, the biggie is that none of the records in the subform is
getting updated yet. I need to set the "MyCostWOShipping" field in the
subform equal to the dblAvgCost calculated in the code which would
immediately precede this snippet (and now resides at the bottom of this
post).

I appreciate any help you can give.

Thanks!

Alice
 
Yep, there's a bit more to updating a recordset. Assuming the rest of the
code (that I can't see) is correct, here's what you're missing (aircode):

Set rst = Me.sfrmBooks.Form.RecordsetClone
With rst
Do While Not .EOF
.Edit
!CostField = Me.Parent.txtCostPerBook
.Update
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks!

This is almost working, but only the last record in the subform is updated.
Here is the code using my field names. I'm going to include all the code
just in case there's something I don't see in the earlier code that makes a
difference:

Private Sub btnTotalCostSpread_Click()

Dim frm As Form
Dim dblAvgCost As Double, dblAvgShip As Double
Dim rst As DAO.Recordset

'The follwoing section counts the total books in the subform.

Set frm = Me.sfrmBooks.Form
With frm
.RecordsetClone.MoveLast
Me.txtTotalBooks.Value = .RecordsetClone.RecordCount
End With
Set frm = Nothing

'The next two steps calculate the average costs which will _
be entered into the records of the subform.

dblAvgCost = Round(Me.CostOfBooks / Me.txtTotalBooks.Value, 2)
dblAvgShip = Round(Me.CostOfShipping / Me.txtTotalBooks.Value, 2)

'The next two steps of placing the average costs in the unbound _
fields of the parent form are just a warm fuzzy...not really _
required for any reason other than to make the person entering _
it feel like they know what's going on.

Me.txtAvgCost = dblAvgCost
Me.txtAvgShipping = dblAvgShip

'The following section updates the records of the subform

Set rst = Me.sfrmBooks.Form.RecordsetClone

With rst
Do While Not .EOF
.Edit
!MyCostWOShipping = dblAvgCost
!MyShippingCost = dblAvgShip
.Update
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing


End Sub


I think I'm following it, and it looks as if it should work, since the
update is being done before moving off the record, but....?

Your help is greatly appreciated.

Alice
 
Back
Top