Setting values over multiple records in one Query using Loop statement

[Maritime EDataSolutions]

Hi all,

Would love your help with this, I am realizing I am struggling with
how to set up my loop statement for this.

I have a form (f_WheatPriceDataSet) that contains 12 months of pricing
information coming from a query (Q_CWheatPriceshortlist). Here is the
SQL of the query"

SELECT T_CWheatPrice.id, T_CWheatPrice.Month,
T_CWheatPrice.CWheatPrice, T_CWheatPrice.Active, T_CWheatPrice.Ratio,
T_CWheatPrice.ratiototal, T_CWheatPrice.ratioprice, FROM T_CWheatPrice
WHERE (((T_CWheatPrice.Active)=Yes))
ORDER BY T_CWheatPrice.id;

In the end I have created the form to fill in a [Ratio] to be used for
each month for example,
MONTH WHEATPRICE ACTIVE RATIO RATIOTOTAL RATIOPRICE
Jan 11 $600 YES 1
2 ($600 * 1/2)
Feb 11 $550 YES 0
2 ($550 * 0/2)
Mar 11 $700 YES 1
2 ($700 * 1/2)
Apr 11 $500 YES 0
2 ($500 * 0/2)

The problem I am having is on the form I don't know how to AFTERUPDATE
of a individual records [Ratio] field to then set the valve of the
RATIOTOTAL to the correct valve for all records in the Query.. in
other words if I changed the April 11 ratio to a one the new
RATIOTOTAL value should be now 3 for all records in the query.

I believe a loop statement in the afterupdate of the Ratio field is
the way to go but have struggled with how to make the statement work.
Can you pls help?

Thanks in advance
Mike

 

[Maritime EDataSolutions]

UPDATE

I have created this AFTERUPDATE code but now I get a "Run-time error
13: Type mismatch"??. Any ideas??

Private Sub ratio_AfterUpdate()
Dim mydb As Database
Dim rstCheck As Recordset
Dim sqlCheck As String

Set mydb = CurrentDb()
sqlCheck = "Select [Q_CWheatPriceshortlist].[id],
[Q_CWheatPriceshortlist].[Active], [Q_CWheatPriceshortlist].
[ratiototal], [Q_CWheatPriceshortlist].[ratio]" _
& "FROM [Q_CWheatPriceshortlist]"

Set rstCheck = mydb.OpenRecordset(sqlCheck)
rstCheck.MoveFirst
Do Until rstCheck.EOF

[ratiototal] = Forms![F_WheatPriceDataSet]![sumratio]

rstCheck.MoveNext
Loop
rstCheck.Close
End Sub