Pointer Errors in Subform Recordsets

[chingachgook]

This is a doozey. Please be patient. I have a parent form for purchase orders. The parent form has a subform that displays all items on the given purchase order. Lets say I have three purcahse orders P1-P3 and I have 15 items that are distributed as 3 with P1, 5 with P2, and 7 with P3. As I scroll through the parent form, the subform updates correctly. However, as soon as I try to do any operations with the subform recordset my ability to navigate through the subform's records is corrupted. Here is an example:

By the way I should say that this is with MSDE SQL server, but the parent and subform are based on Querries, Snapshot updatable, and have a unique table defined.

Lets say I want to recalculate the total cost for all items on partent form2. I click on a button in the subform that goes through each record in the subform and multiplies the quantity * price.

dim rst as recordset

set rst = me.recordset

rst.movefirst
do until rst.eof
total = total + (rst.quantity * rst.price)
loop
rst.movefirst
rst.close


This works the first time. However, when I then try to navigate through the subform recordset sometimes I can't move from record 1 to record 2. Also, if I switch parent records and get a new subform recordset the length of the subform recordset is not correct and the values for each item in the subform recordset are a mish of data from the current and previous subform recordsets. Any help on what I am doing wrong?

Thanks!

 

[Amiel]

I beg to differ. The code below does not work. The following...

dim rst as recordset
set rst = me.recordset

rst.movefirst
do until rst.eof
total = total + (rst.quantity * rst.price)
loop
rst.movefirst
rst.close

... is an 'infinate loop' :-0 that multiplies the value of the first record in the recordset until an '[red]over-flow[/red]' occurs. The last two statements shown, 'rst.movefirst' and 'rst.close', will never execute!

Add the statement 'rst.movenext', just above the word 'Loop', to fix this.


Amiel
amielzz@netscape.net

 

[chingachgook]

Sorry- I really have that in there- I was just recreating the script from memory and left out the movenext.

thx