Hello, I am new to the forum and probably doing everything wrong so please treat me gently.
I have a main form of customers and a continuous subform of products being used by each of those customers. I have respective customer and products tables. For each product a function calculates the price that will be charged at the next monthly bill run, taking account of any discounts on products for periods of time or free units of product for a period of time. The product data can be refreshed by the user during the course of each month. This refresh process sucks in data from a variety of sources via ODBC connections which all work fime.
While I appreciate it might not be good practice, I want to store the calculated price in a field in the underlying products table. Until i can get this working i have the code running off a command button on the main form. Once i have overcome this challenge i will put on an afterupdate event on the subform.
My difficulty is that Access completely crashes sporadically when working through the recordset. By putting some message boxes into the routine i have worked out that it crashes on the .movenext line of code. What am i doing wrong?
Many thanks in advance...
The code is:
Dim rst As Recordset
Dim ctlQuantity As Control
Dim ctlReceived As Control
Set rst = Forms!clients_core_data_form!frm_products_core_data.Form.Recordset
Set ctlQuantity = Forms!clients_core_data_form!frm_products_core_data.Form!txt_pr_total_fee
Set ctlReceived = Forms!clients_core_data_form!frm_products_core_data.Form!txt_pr_quantity
With rst
Do While Not rst.EOF
.Edit
![pr_total_fee] = ctlReceived
.Update
.MoveNext
Loop
End With
I have a main form of customers and a continuous subform of products being used by each of those customers. I have respective customer and products tables. For each product a function calculates the price that will be charged at the next monthly bill run, taking account of any discounts on products for periods of time or free units of product for a period of time. The product data can be refreshed by the user during the course of each month. This refresh process sucks in data from a variety of sources via ODBC connections which all work fime.
While I appreciate it might not be good practice, I want to store the calculated price in a field in the underlying products table. Until i can get this working i have the code running off a command button on the main form. Once i have overcome this challenge i will put on an afterupdate event on the subform.
My difficulty is that Access completely crashes sporadically when working through the recordset. By putting some message boxes into the routine i have worked out that it crashes on the .movenext line of code. What am i doing wrong?
Many thanks in advance...
The code is:
Dim rst As Recordset
Dim ctlQuantity As Control
Dim ctlReceived As Control
Set rst = Forms!clients_core_data_form!frm_products_core_data.Form.Recordset
Set ctlQuantity = Forms!clients_core_data_form!frm_products_core_data.Form!txt_pr_total_fee
Set ctlReceived = Forms!clients_core_data_form!frm_products_core_data.Form!txt_pr_quantity
With rst
Do While Not rst.EOF
.Edit
![pr_total_fee] = ctlReceived
.Update
.MoveNext
Loop
End With