Loop through continuous form recordset crash

[jerem]

Hi,

My application crashes when trying to change the value of a text box in a continuous form. Here is the code:

Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
    Dim rst As Recordset
    With Me.sbfProductDetails.Form
        Set rst = .Recordset
        rst.MoveFirst
        Do While Not rst.EOF
            .txtUnitCost = 1
            rst.MoveNext
        Loop
    End With
    Set rst = Nothing
ExitSub:
    Exit Sub
ErrHandler:
    Call ErrorAlert
    Resume ExitSub
End Sub

If I replace .txtUnitCost by MsgBox .txtUnitCost, it loops correctly through each record and returns the value. But if I try to change the value as shown in above code, MS Access crashes!
(This is a desktop application with tables linked to SP lists - not a web app)

Cross:
http://www.utteraccess.com/forum/Loop-Continuous-Form-Rec-t2019350.html#entry2472070
http://www.accessforums.net/forms/loop-through-continuous-form-recordset-crash-44959.html#post233489

 

[stopher]

What's the error message when it crashes? I suggest you comment out the On Error line so you can see the error message.

 

[jerem]

Well that's the think. It doesn't say anything...
Just that Access has stopped working.

 

[stopher]

I would to "compile" your database (Debug-compile).

 

[jerem]

I have just compiled it and tried again... Still crashing: "Microsoft Access has stopped working..."

 

[stopher]

Now try stepping through the code line by line using the debugger to see at which point the crash occurs.

I assume you have commented out the On Error line?

Can you upload the database or at least just the bit that is relevant?

 

[jerem]

Kind of difficult to upload. I need to make my tables local and somehow it creates errors. I have tried what you are suggesting already It crashes from the loop line.
I have also tried to use the recordsetclone in which case it doesn't crash anymore but I get the following error message: Update or CancelUpdate without AddNew or Edit
Here is the new code:

Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
    With Me.sbfProductDetails.Form.RecordsetClone
        .MoveFirst
        Do While Not .EOF
            !UnitCost = 1
            .MoveNext
        Loop
    End With
ExitSub:
    Exit Sub
ErrHandler:
    Call ErrorAlert
    Resume ExitSub
End Sub

 

[stopher]

Are you able to edit the record source without using code?

 

[jerem]

Yes I can. No problem with that

(I am making an answer longer than 10 character as the blog won't let me post just "yes")

 

[stopher]

If you just send your front end then I will create a SP list as a back end and see if I can replicate the problem. I can't promise I can do this today though.

 

[jerem]

Thanks. Can it be sent in private?

 

[jerem]

Ok... I have extracted relevant tables, forms and queries. It is a bit messy as I have taken over an existing database with macros :bang head: and I haven't converted everything back to VBA yet.

Click on the first column and you get the form. There are two currency drop downs on the main form. They each have a different procedure that is meant to achieve exactly the same thing. If you use the Sale currency drop down, it works like a charm... It uses an UPDATE query... Only that this does not work with Sharepoint due to record lock (I think) and therefore current record does not get updated.

Hence, I decided to go for the second method, which is the one we have discussed and creates errors. The procedure is on the Cost currency drop down.

Thanks for your help!

 

[spikepl]

I get the following error message: Update or CancelUpdate without AddNew or Edit

so why not try
.Edit
...
.Update

as Access suggests?

 

[jerem]

so why not try
.Edit
...
.Update

Just tried and now I get the error: Overflow

 

[stopher]

The overflow is probably due to dividing by zero. If you look where dblOPriceR is set, it is set in cboCurrency_BeforeUpdate. So if you don't run this event first then dblOPriceR will be zero thus causing a divide by zero error later.

I ran the code from your post 1 in your database and it ran fine (providing I avoided the divide by zero). I did not get a crash and the UnitPrice was set for all rows in the form. Were you able to get this standalone version working?

I see you tried to use an update query to update the detail records. What was wrong with this approach? Personally I prefer this approach although I have no reason for the preference.

Chris

 

[jerem]

Yes the UPDATE query was my first and logical choice. The problem is that those tables are linked to Sharepoint and somehow the current record gets locked. I have tried numerous things to exit from the current record (like subform.form.dirty = false) but nothing helped. The result is that the current record does not get updated but the UPDATE query.

I will try your suggestion. What I am afraid of is that since the code works on the field and not on the control, I will get the same record lock issue once I am back in the Sharepoint environment.

 

[jerem]

Ok, you are right... It's copy and paste error. dblOPriceR should actually be dblOCostR

 

[jerem]

Ok. This is the code I am running now and it works in the light version. Haven't tried it with SP though but when I add a new record in the continuous form and run the below coed again

I get the error message: There record source '|' specified for this form or report does not exist. Is that something to do with the bookmark?

Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
    Dim dblRate As Double
    dblNCostR = DLookup("ExRate", "Currency", "ID = " & [cboPoCurrency])
    dblRate = dblNCostR / dblOCostR
    With Me.sbfProductDetails.Form.RecordsetClone
        .MoveFirst
        Do While Not .EOF
            .Edit
            !UnitCost = !UnitCost * dblRate
            .Update
            .MoveNext
        Loop
    End With
    Me.txtPOExchRate.Value = dblNCostR
    Me.sbfProductDetails.Form.Requery
ExitSub:
    Exit Sub
ErrHandler:
    Call ErrorAlert
    Resume ExitSub
End Sub

 

[jerem]

My fears were correct:

When trying this on the Sharepoint linked tables and amending one of the records I get the error message: Could not update; currently locked

So I did not gain anything from this compared to the UPDATE query

 

[stopher]

I've managed to get a crash. I created an SP list for QuoteLineItems and linked to it. The database worked fine providing I was not in the SP list. However if I tried to run the Access update while editing a record in SP then Access crashes ungracefully.

Can you get everyone out of your SP and see if your code works?