Trying to update the records in a continuous form

[uncle-lai]

The purpose of my code is to update the records that follow the edited one in a continuous form, but I could not make it work.

Here is the code simplified:
-----------------------------------------------------
Private Sub Adjust_AfterUpdate()
Dim rst As Recordset
Dim n as Interger
set rst = Me.RecordsetClone

' set n = the value just entered
n = Adjust
' update the following records with the same value
Do While Not rst.EOF
rst.MoveNext
Adjust = n
rst.Update, True
Loop
End Sub

------------------------------------------------------
I am using Access 97
References include:
Visual Basic For Applications
Ms Access 8.0 Object Library
OLE Automation
Ms DAO 3.6 Object Library

*In Windows 2000 with Access 2000 installed too.

Thanks for any help and advice.
Peter

 

[cable]

use sql?

SQL would look like: UPDATE Table SET Field=NewValue;
Then you could refresh/requery the form.

mmm, you might need a WHERE IDField>CurrentID on the end of the sql to make sure it only applies to later records.

If these records don't exist and you want the defualt for new records to change, the look at using the defualt property of the controls.

 

[uncle-lai]

Thanks Cable,

Here is an example of what I am trying to do, and, it worked fine in Access 2K (with Dim rst As DAO.Recordset):

I have a continuous form with say 10 records. When I enter "-1" in the [Adjust] field in the 5th record the code will automatically enter the same value into the same field in the 6th, 7th.....all the way to the last record.

The code just did nothing when my mdb file was converted to run in A97. I had compiled the module and there seemed to be no error. But the code did not even move to the next record after I updated a record.

I had searched all resources for 2 days but could not find out any clue on what was wrong with my code - which worked fine in Access 2k.

Peter

 

[cable]

I'd guess its the recordset clone thats changed behaviour, but I can't say more than that.

I'd rewrite it as long as you have an field which you can order by (which I guess you must have) then the SQL idea will work, now and forever

 

[uncle-lai]

Hi Cable,

Yes you're right. The behaviour of the Recordset Clone in A97 is not the same as in A2K.

In A2K I use
Dim rst As DAO.Recordset
Set rst = Me.Recordset
Thus rst.MoveNext is the same as GotoRecord acNext

In A97 I can only use
Dim rst As Recordset
Set rst = Me.RecordsetClone
The Cloned Recordset is a separate set of records apart from the Form.

Yes, I need to rewrite an SQL to update the records and refresh the form too. Thank for you advice.

Peter

 

[uncle-lai]

I finally found a way to make it work both in A2K and A97:

I put a Textbox in the Form Footer to count the total number of records
txtTotalItem = Count([Items])

Code:
Dim x as Integer
x = Me.txtTotalItem
Do Until Me.CurrentRecord = x
Docmd.GoToRecord, , acNext
....(my update action)
Loop

 

[Pat Hartman]

Storing calculated values in tables is poor practice. Not to mention a pain to maintain.

 

[RoyVidar]

I agree with Pat Hartman, storing calculated should be avoided!

But, then addressing the code in the first post, to update a dao.recordset, one would need the .edit method, and not to forge, specify which field is to be updated.

Do While Not rst.EOF
    [b]rst.[/b]edit
    [b]rst![/b]Adjust = n
    rst.Update
    rst.MoveNext
Loop

 

[uncle-lai]

Hi Pat, RoyVidar,

Thank for your advice.

It's not a calculated field. I had removed all calculated fields in my dbs since I joined this forum, and had a lot of improvement on my applications. Thanks to all who contribute to this wonderful forum.

My app was built with Access 2K, it worked fine in the first place using the DAO.Recordset method. The form is a continuous form listing up to 40 records. The records are sorted by Step No. of procedures of production. There is a Quantity field and an Adjust filed. The Quantity field stores the original quantity, the Adjust field stores any adjustment in quantity, like -2 or +1. Usually an adjustment is required at the middle of the production. When run in Access2K the user enters adjustment in Step 5, for example, a dialogue box pops up asking if the user want to apply the adjustment to the following Steps, if yes (99% yes) the user can see the same changes run all the way to the last record.

When my mdb file was converted to A97 I need to change the line:
set rst = Me.Recordset
to
Set rst = Me.RecordsetClone

But when run in Access97 the cursor only jumped to the next record after the user selected yes, not even the next record was updated.

I choose to update the records in this way because I wish to let the user visualize the changes instead of running an update query in the background and then send a dialogue box saying "Done !".

This was another problem I had encountered besides the Round() funtion when I converted mdb file from A2K to A97.

Peter