Error 3021 no current record

[bbrazeau]

Below is my code to cycle thru the records in the subform and look for values that need updating.


The problem is it I get a no current record error after it updates the field value. I have marked the line of code it hangs on. Any thoughts??

Private Sub Form_Current()
   Dim rst As Recordset
   Dim fld As Field
   Dim response As Integer
   Dim controll As Control
   Dim cur As Currency
   Set rst = Me.Form.Recordset
   While Not rst.EOF
      For Each fld In rst.Fields
         If fld.Name = "Part_Qty" Then
            cur = fld.Value * Me.Part.Column(5)   'this where it hangs
         End If
         If fld.Name = "Total_Part_Cost" Then
            If cur <> fld.Value Then
               response = MsgBox("Value needs updating. Would you like to Update?", vbYesNo)
               If response = 6 Then
                  MsgBox ("Updating....")
                  Me.Total_Part_Cost = cur
                  Me.Total_Part_Cost.Requery
               End If
            End If
         End If
      Next
      rst.MoveNext
   Wend
End Sub

[pbaldy]

I don't see the purpose of the fields loop. Why not just:

cur = rst!Part_Qty * Me.Part.Column(5)

[ssanfu]

Since the event is "Form_Current", every time the current record changes, every total cost gets recalculated and possibly saved. Changing records when there are only 10 records might not be too bad, but how long will it take if there were 100,000 records??

This is a prime example of why calculated values should not be stored in a table. It also looks like the cost per unit is looked up (from a combo box). That is ok for today, but what about looking at a record that is 3 years old? I would bet that the cost per unit would not be the same as today. But the code will update the total cost from 3 years ago to the total cost for today, at today's cost per unit (totally wrong amount). It would be better to store the price per unit and calculate the total cost in the query.



OK, I'm off the soap box

[bbrazeau]

Thank you for your replies. pbaldy: Below is my modified code it still updates the field value, but still gives the "no current record" errror?? ssanfu: I'm not sure how to implement my goal with a calculated field.(The subform is ProductDetails, which is from a join table of Products and Parts. It serves as a B.O.M. The Part cost is kept in the Part table. The Total_Part_Cost is stored in the ProductDetails table. The Product Cost is a calculated field in the ProductDetails subform that is then stored in the Products table. The Product cost is eventually used in an OderDetails table, (join between Products and Orders) )
I don't know how to implement this solely with calculated fields. As far as not wanting to update a 3 year old cost goes... that is exactly what I want to do.
Private Sub Form_Current()
Dim rst As Recordset
Dim fld As Field
Dim response As Integer
Dim controll As Control
Dim cur As Currency
Set rst = Me.Form.Recordset
While Not rst.EOF
cur = rst!Part_Qty * Me.Part.Column(5)
If cur <> rst!Total_Part_Cost Then
response = MsgBox("Value needs updating. Would you like to Update?", vbYesNo)
If response = 6 Then
MsgBox ("Updating....")
Me.Total_Part_Cost = cur
Me.Total_Part_Cost.Requery
End If
End If
rst.MoveNext
Wend
End Sub

[pbaldy]

Have you set a breakpoint at that line and seen what values are returned? Perhaps the combo is null. Can you post the db here?

[ssanfu]

As far as not wanting to update a 3 year old cost goes... that is exactly what I want to do.

Normally, there is a history kept of previous invoices, costs,orders, etc. Sorry.

--------
As far as the error: I would be willing to bet the problem is "Me.Part.Column(5)".
Essentially, you are working with a clone of the form recordset (rst). It *should* be in the same order as displayed on the form. In the first record of the recordset clone, the form is at the top record. "Me.Part.Column(5)" should have a value.

After moving to the next record in the recordset (rst), you are still referring to "Me.Part.Column(5)" ON THE FORM. The record on the form has not changed. At the very least, you could get the same value as for "Me.Part.Column(5)" as the first record of rst.

"Me.Part.Column(5)" is (I hope) bound to a field. You could do a DLookup() of the Part table to get the part cost.
Something like:

cur = rst!Part_Qty * DLOOKUP("Cost","Part table","PartID_PK = " & rst!Part)

[bbrazeau]

pbaldy and ssanfu, Thank you for your help. I have attached a simplified version of my db. I have resolved the issue I originally posted about by taking ssanfu's advice and using calculated fields. I found it to be no easy task as I'm not a database programer by trade, but here is what I did. My problem was having both a join table between Products and Parts =ProductDetail and Orders and Products= OrderDetails. I needed a sum on ProductDetails of PartCost*PartQty for each Part that was in a Product to get a ProductCost. I needed a sum on OrderDetails of ProductCost*ProductQty for each Product that was in an Order. So I had to get the sum of PartCost*PartQty values from the ProductDetails table and multiply by the ProductQty in OrderDetails then get the sum of all OrderDetails for that Order. Did I do it the most efficiently?? I doubt it. Please, Please take a look at my db and explain a better way if there is one.

[bbrazeau]

I'm wondering why no one has answered back to my last post? I imagine that this is a common database functionality request. If I could develope a simple yet generic standard for performing this task, I would post it in the "Sample databases" or "Code repositiory" section of this forum. A more generic description for this database task would probably be..."Order Invoice database with option for selling Assemblies" I'll ask again Is there a definatively better way to accomplish this??

[ssanfu]

My apologies. I lost you somehow.

I'll look at the dB when I get home. (I have A2000 at work).

[bbrazeau]

Hi ssanfu, Did you get a chance to look at the db I uploaded? I have searched the subject and can't seem to find much in the way of a generic, simplistic, OrderInvoiceAssemblies db. What I want to do is come up with a highly customizable version of what I'm sure is probably one of the most common uses for a db. Then everyone could have a basic db to start with and customize as they see fit without having to worry about messing up the existing functionality by trying to add there own.

[ssanfu]

I've looked at it a couple of times. I'm still trying to get a feel as to what you are doing. Looked at the code... seems ok. Everyone has their own way of coding, so I haven't seen anything that would cause me concern.

Almost caught up with home stuff; I'm going to look at it some more.