Assign the records shown in a subform to a recordset


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
 
Ad

Advertisements

M

Marshall Barton

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


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.
 
Ad

Advertisements

P

Paulo

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


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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top