Assign the records shown in a subform to a recordset

  • Thread starter Thread starter Paulo
  • Start date Start date
P

Paulo

What coulb be wrong with this code? I am trying to attribute the current
records shown in a subform to a recordset, and then performing some
calculations with the records. I am also assuming (I don't know if correctly)
that the recordset will reflect any filtering applied before to the subform
(through a combo box in the main form). This code is included in a event for
the main form. I am getting error messages.

Thanks in advance,

Paulo

====================================

Dim Total_Turnover As Single
Dim rst As DAO.Recordset
Set rst = Me.[Sub Bids Tracking].Form.Recordset
Total_Turnover = 0

With rst

.OpenRecordset
.MoveFirst

While Not rst.EOF
If rst![Channel].Value = "Wholesale" Then
If IsNull(rst![WSale Price].Value) Then
If IsNull(rst![Received Qty].Value) Then
Total_Turnover = Total_Turnover + (rst![WSale Price]
* rst![Received Qty])
End If
End If
End If
rst.MoveNext
Wend

Me.[Sub Bids Tracking].Form.Text123.Value = Totaller

rst.Close
 
Paulo said:
What coulb be wrong with this code? I am trying to attribute the current
records shown in a subform to a recordset, and then performing some
calculations with the records. I am also assuming (I don't know if correctly)
that the recordset will reflect any filtering applied before to the subform
(through a combo box in the main form). This code is included in a event for
the main form. I am getting error messages.

====================================

Dim Total_Turnover As Single
Dim rst As DAO.Recordset
Set rst = Me.[Sub Bids Tracking].Form.Recordset
Total_Turnover = 0

With rst

.OpenRecordset
.MoveFirst

While Not rst.EOF
If rst![Channel].Value = "Wholesale" Then
If IsNull(rst![WSale Price].Value) Then
If IsNull(rst![Received Qty].Value) Then
Total_Turnover = Total_Turnover + (rst![WSale Price]
* rst![Received Qty])
End If
End If
End If
rst.MoveNext
Wend

Me.[Sub Bids Tracking].Form.Text123.Value = Totaller

rst.Close
Click to expand...


Since the cakcukation will only be done when the price and
qty are Null the total will always be Null and that can not
be assinged to a single variable.

You probably want to chack for Not IsNull(...) and this kind
of calculation should use a Currency type, not Single.
 
That's what I missed in the code. It's definitely "Not Isnull".

Thanks a lot,

Paulo



Marshall Barton said:
Paulo said:
What coulb be wrong with this code? I am trying to attribute the current
records shown in a subform to a recordset, and then performing some
calculations with the records. I am also assuming (I don't know if correctly)
that the recordset will reflect any filtering applied before to the subform
(through a combo box in the main form). This code is included in a event for
the main form. I am getting error messages.

====================================

Dim Total_Turnover As Single
Dim rst As DAO.Recordset
Set rst = Me.[Sub Bids Tracking].Form.Recordset
Total_Turnover = 0

With rst

.OpenRecordset
.MoveFirst

While Not rst.EOF
If rst![Channel].Value = "Wholesale" Then
If IsNull(rst![WSale Price].Value) Then
If IsNull(rst![Received Qty].Value) Then
Total_Turnover = Total_Turnover + (rst![WSale Price]
* rst![Received Qty])
End If
End If
End If
rst.MoveNext
Wend

Me.[Sub Bids Tracking].Form.Text123.Value = Totaller

rst.Close
Click to expand...


Since the cakcukation will only be done when the price and
qty are Null the total will always be Null and that can not
be assinged to a single variable.

You probably want to chack for Not IsNull(...) and this kind
of calculation should use a Currency type, not Single.
Click to expand...
 
Back
Top