effect all records on continuous form

[samonwalkabout]

I have a text box on a form containing a sum of several other boxes. I want to store this value in a field. I am using a on click event me.box = me.box2.

This works fine on a Single Form, but on Continuous Form it only does it to the record I have selected.

I Have tired on_current event same problem,
and on_load event but only works when i click on that record

Is there any code for calling all records shown on a filtered cont form to do the same at once?

Thanks

 

[samonwalkabout]

Its not duplicate data one is a unbound text box performing a sum, the other is a bound field.

 

[Rich]

Put the Unbound textbox in the footer

 

[samonwalkabout]

I want to do this on a cont form for each record, perhaos i should explain a little more.

The form Contains [DateID] [StartAmount] [Orders] [Usage] [unbound] [EndAmount].

The unbound box calcualtes [startAmount]+[Orders]-[Usage] then on click event passes this to [endAmount].

The some VB moves the [endamount] Down to the next [startAmount] for the next date.

So the form needs to update the unbound to the [EndAmount] for each record on a cont form.

Thanks

 

[samonwalkabout]

The plot thickens

i Have tried changing the Vb to use the unbound vaule rather than the [endAmount] thus removing the need to move it. But it replaces only with the unbound value of the record that is selected when the VB is ran. Can i get the VB to move onto the next unbound box???

Code is
Function UpdateAmounts(intRawMaterailID As Integer)

Dim rst As Recordset
Dim dt As Date
Dim Lngamnt As Long
Dim db As DAO.Database
Dim strSQL As String
Dim rsttblstock2 As Recordset
Dim intRawMaterialID As Integer


Set db = CurrentDb

intRawMaterialID = [raw]

strSQL = "Select [RawMaterialID], [Weekid], StartAmount, EndAmount" _
& " From tblstock2 " _
& " Where [RawMaterialID] = " & intRawMaterialID & ";"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)



Lngamnt = rst!StartAmount

With rst

.MoveFirst

Do Until .EOF
.Edit
!StartAmount = Lngamnt
.Update
Lngamnt = endo ' endo is the name of the unbound box
.MoveNext
Loop

End With
End Function

If it cant be done then i still need to figure out how to update the [endo] unbound to the [EndAmount] in each record at once. Maybe another bit of code?

 

[samonwalkabout]

Ok i got it. I needed use a qry rather than a sum in an unbound box and add the components of the qry to the sql.

Final Solution looked like this.

Dim rst As Recordset
Dim dt As Date
Dim Lngamnt As Long
Dim db As DAO.Database
Dim strSQL As String
Dim rsttblstock2 As Recordset
Dim intRawMaterialID As Integer


Set db = CurrentDb

intRawMaterialID = [raw]


strSQL = "SELECT tblStock2.RawMaterialID, tblStock2.WeekID, tblStock2.EndAmount,tblStock2.StartAmount, tblOrder2.OrderID, tblOrder2.Amount, tblForecast2.ForecastID, tblForecast2.Amount,tblUsage2.UsageID, tblUsage2.Amount, tblAdjustment2.AdjustmentID, tblAdjustment2.Amount, [StartAmount]+[tblForecast2].[Amount]+[tblOrder2].[Amount]+[tblAdjustment2].[Amount]-[tblUsage2].[Amount] AS Ender " _
& " FROM tblUsage2 INNER JOIN (tblOrder2 INNER JOIN (tblForecast2 INNER JOIN (tblAdjustment2 INNER JOIN (tblRawMaterial INNER JOIN tblStock2 ON tblRawMaterial.RawMaterialID = tblStock2.RawMaterialID) ON tblAdjustment2.AdjustmentID = tblStock2.AdjustmentsID) ON tblForecast2.ForecastID = tblStock2.ForecastID) ON tblOrder2.OrderID = tblStock2.OrderID) ON tblUsage2.UsageID = tblStock2.UsageID" _
& " WHERE (((tblStock2.RawMaterialID)=1))"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)



Lngamnt = rst!StartAmount

With rst

.MoveFirst

Do Until .EOF

.Edit
!StartAmount = Lngamnt
!EndAmount = !Ender
.Update

Lngamnt = !EndAmount
.MoveNext

Loop

End With
End Function


Thanks for suggestions