cruising through rows on a subform

[jimatqsi]

I have a sales order form with a subform on a tab. The subform contains the line items of the order. If I change something in the heading (main form) I want to reprice the line ittems. The code I have reprices the first line item correctly, and then loops infinitely, continuing to reprice the first line item repeatedly. What have I done wrong?

[HTML]
Dim rs As dao.Recordset

With Forms!frmOEOrde r
On Error GoTo Error_txtUpchar gePct_AfterUpda te
Set rs = !subDetail.Form .RecordsetClone
rs.MoveFirst

Do Until rs.EOF
cswPricingLevel s Forms!frmOEOrde r, False, CodeDb
rs.MoveNext ' this does not move to the next row ???
Loop
End With

[/HTML]

Thanks in advance!
Jim

[jimatqsi]

Perhaps I should add that this is a MS Access front end with a SQL back end.

I've monitored through the code, and no error is reported on that .movenext command, it just looks like it doesn't move to the next row.

Jim

[mshmyob]

I recreated the code and it works for me. It cycles through each row of the subform.

My suggestion is that you remove the On Error event to see if you are getting any erros and also try removing the line above the MoveNext statement and replace it with a MSGBOX.

See how many times the message box appears (it should pop up the same number of times you have rows in the subform).

If it does then your line above the MOveNext would seem to be the culprit.


I did the following and all works fine.

[code=vb]
Dim rs As dao.Recordset

With Forms!form2
'On Error GoTo Error_txtUpchar gePct_AfterUpda te
Set rs = !invDetailSub.F orm.RecordsetCl one
rs.MoveFirst

Do Until rs.EOF
'cswPricingLeve ls Forms!frmOEOrde r, False, CodeDb
MsgBox (Order_ID)
rs.MoveNext ' works
Loop
End With
[/code]


Post back your results.

cheers,

I have a sales order form with a subform on a tab. The subform contains the line items of the order. If I change something in the heading (main form) I want to reprice the line ittems. The code I have reprices the first line item correctly, and then loops infinitely, continuing to reprice the first line item repeatedly. What have I done wrong?

[HTML]
Dim rs As dao.Recordset

With Forms!frmOEOrde r
On Error GoTo Error_txtUpchar gePct_AfterUpda te
Set rs = !subDetail.Form .RecordsetClone
rs.MoveFirst

Do Until rs.EOF
cswPricingLevel s Forms!frmOEOrde r, False, CodeDb
rs.MoveNext ' this does not move to the next row ???
Loop
End With

[/HTML]

Thanks in advance!
Jim

[jimatqsi]

Mshmyob,
That was a very good idea, thank you. And you are right, I get the message box once for each line item on the order, and then the loop ends normally.

The repricing function is having some effect on this code. What can it be doing, changing the recordset current record, setting the rs back to the beginning of file? How can I avoid that problem? I don't want to rewrite the pricing mechanism.

Thanks,
Jim

[mshmyob]

I'm still looking into it because I noticed some funny things happening in my sample code.

Question: Do you need to use DAO or would ADO suffice?

Mshmyob,
That was a very good idea, thank you. And you are right, I get the message box once for each line item on the order, and then the loop ends normally.

The repricing function is having some effect on this code. What can it be doing, changing the recordset current record, setting the rs back to the beginning of file? How can I avoid that problem? I don't want to rewrite the pricing mechanism.

Thanks,
Jim

[mshmyob]

OK got it to work properly now.

[code=vb]
Dim rs As dao.Recordset

With Forms!form2
'On Error GoTo Error_txtUpchar gePct_AfterUpda te
Set rs = !invDetailSub.F orm.RecordsetCl one
rs.MoveFirst

Do Until rs.EOF
'cswPricingLeve ls Forms!frmOEOrde r, False, CodeDb
xs=rs.fields("f ldPrice")
MsgBox (xs)
rs.MoveNext ' works
Loop
End With
[/code]

Notice whatever field I want to work with I identify it like so rs.fields("fldN ame")

So in your function that you have that updates the prices you should identify the field(s) using that type of syntax.

I have done it and it works fine now.

Let me know.

If you need help you can post the function code that you use to change the pricing.

cheers,

[jimatqsi]

Okay, this is very odd. I tried to bookmark the location in the recordset and return to the same location in the recordset after the repricing is done. But when I return from the repricing routine, my recordset is no longer set. Any reference to it gets an "object invalid or no longer set".

I have changed the name of the recordset, just in case it matters if there is a similar name in the function being called. Makes no difference, same result.

The function being called is several hundred lines long, so I'm reluctant to post all that code here.

As for the 'rs("data item")' format, I don't even see any references to the subform's recordset in the repricing function. It opens a number of recordsets, none of them based on the subform. (The repricing is accomplished by modifying a text box on the subform.)

The mystery, it seems to me, is how can this pre-existing repricing function modify or close the recordset in this event handler I have just recently written?

Thanks for any ideas you may have,
Jim

[mshmyob]

That may be your problem. If you do not have a reference to your recordset in your pricing function then moving through your recordset will have no effect.

From what you say and the little code you have shown it appears this is what is happening:

1. You create a recordset
2. You are automatically on the first record (row) of your subform (not recordset)
3. You set your recordset to the first record
4. You call your pricing function
5. Since you are on the first record of your subform (not recordset) the price gets changed since your pricing function is referring to the subform and not the recordset.
6. You move to the next record of your recordset. (this has no affect of moving to the next record in the subform).
7. You execute the pricing function again and it will still be referring to the 1st record in your subform since you have not changed records.
8. Cycle repeats until EOF in your recordset.

Your subform pointer will never move.

In reality since you are never referencing your recordset for the calculation it seems a waste.

Try moving through the sub form like so and let me know if this has any positive effect.

[code=vb]
Dim rs As dao.Recordset

'add this line to set focus to the subform
Forms!frmOEOrde r!subdetail.Set Focus

With Forms!frmOEOrde r
On Error GoTo Error_txtUpchar gePct_AfterUpda te
Set rs = !subdetail.Form .RecordsetClone
rs.MoveFirst

Do Until rs.EOF
cswPricingLevel s Forms!frmOEOrde r, False, CodeDb

' move through the subform
DoCmd.GoToRecor d , , acNext

rs.MoveNext ' this does not move to the next row ???

Loop
End With
'when done you can setfocus back to the mainform if you wish
Forms!frmOEOrde r.Setfocus
[/code]

Okay, this is very odd. I tried to bookmark the location in the recordset and return to the same location in the recordset after the repricing is done. But when I return from the repricing routine, my recordset is no longer set. Any reference to it gets an "object invalid or no longer set".

I have changed the name of the recordset, just in case it matters if there is a similar name in the function being called. Makes no difference, same result.

The function being called is several hundred lines long, so I'm reluctant to post all that code here.

As for the 'rs("data item")' format, I don't even see any references to the subform's recordset in the repricing function. It opens a number of recordsets, none of them based on the subform. (The repricing is accomplished by modifying a text box on the subform.)

The mystery, it seems to me, is how can this pre-existing repricing function modify or close the recordset in this event handler I have just recently written?

Thanks for any ideas you may have,
Jim