.MoveNext causes Access to crash completely (1 Viewer)

julianb

New member
Local time
Today, 13:58
Joined
Dec 15, 2021
Messages
4
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,357
Not sure, but try using a clone instead.
 

julianb

New member
Local time
Today, 13:58
Joined
Dec 15, 2021
Messages
4
Thankyou - i changed

Set rst = Forms!clients_core_data_form!frm_products_core_data.Form.Recordset

to

Set rst = Forms!clients_core_data_form!frm_products_core_data.Form.RecordsetClone

which worked in that Access has yet to crash. However, the value hasnt changed as the routine has looped through the recordset - it has returned the first row's value for every row.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,357
Are total fee and quantity controls unbound?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,357
Not sure if this will help, but try inserting one line in your code.
Code:
With rst

Do While Not rst.EOF

  .Edit
     ![pr_total_fee] = ctlReceived
  .Update

  Me.Bookmark = .LastModified  '<-- add this line

  .MoveNext

Loop

End With
You may have to play with its placement though. Cheers!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:58
Joined
Feb 28, 2001
Messages
26,999
OK, I'll tell you WHY you saw the crashes.

This line:
Code:
Set rst = Forms!clients_core_data_form!frm_products_core_data.Form.Recordset
makes your rst variable "point to" the data structure that is also pointed to by ...Form.Recordset - because in VBA, an object is really the address to a hidden data structure that is the embodiment of the referenced object. In VBA you can't really see these object structures. All you have is the pointer but there is no way IN VBA to easily decipher what is at the pointed location. I.e. might as well be the proverbial black box.

You are doing what is called a Set-assign, which essentially copies the pointer to that actual recordset structure. So now your RST variable is pointing to the same structure to which the form's recordset pointed. If you change stuff through RST you are doing so with the same structure used by the form (which thinks it is still using said structure). If the form code does a sanity check at any time, it might find that your recordset is no longer pointing where it should be pointing. The discovery of a code inconsistency leads to "bang - zoom - crash."

As suggested by theDBguy, and as indicated by having no more crashes, you can safely use the .RecordsetClone to look at the form's recordset. The form is not using the clone. In fact, the clone is there precisely so you CAN diddle with the same recordset.

Now, however, time for some intelligent guesses as to the other problem.

You have stopped doing things to move your current record (because that is controlled by the position of the form's .Recordset, not by the .RecordsetClone), so nothing moves. Your linkage, derived from the form's .Recordset, is still on the first record (or the most recent record selected by navigation). If the form and that sub-form were synchronized (i.e. the parent and child links pointed to each other), then moving the form by moving the recordset would have "dragged along" the content of the sub-form. Moving the clone's point of focus does nothing to that linkage, so you would see the results for the first record every time.

I'm not sure how to fix this because I can't see your computational interactions yet, but probably you would do well to think about a query that does summations of some computed formulas and that has a GROUP BY of whatever is the PK of the main table's bound form. Then you could do a DLookup of that sum from that query - or do a DSum with more complex criteria.
 

julianb

New member
Local time
Today, 13:58
Joined
Dec 15, 2021
Messages
4
Golly, i've learnt so much on this very quickly - very very helpful. Written in a way i can understand. I need a night's sleep to think through my next step and will revert. Many thanks.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:58
Joined
May 7, 2009
Messages
19,169
also, very important that you declare:

Dim rst As DAO.Recordset
 

Users who are viewing this thread

Top Bottom