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.access-programmers.co.uk/...41#post1359341
http://www.utteraccess.com/forum/Loo...l#entry2472070

[June7]

I presume the textbox is bound to field.

Try using RecordsetClone and reference the fieldname:

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

[jerem]

Well, Access is not crashing anymore but now I get the following error: Update or CancelUpdate without AddNew or Edit

[June7]

Okay,

.AddNew
!fieldname = 1
.Update


Actually, Recordset will work as well as RecordsetClone. The difference is with RecordsetClone will not see focus move record to record.

[jerem]

I get the error: "Overflow"
I can upload a light version of the DB if necessary

[jerem]

Ok, the overflow was a side error. I got it to work in a local table environment. But back in the Sharepoint linked table environment and I modify one of the records, I get an error that says: Could not update; currently locked
So basically I get the same issue as with an UPDATE query...

[June7]

Sorry, no experience with SharePoint. Maybe cannot be done.

[ItsMe]

This sounds like a lot of fun. I read one of the other threads. Maybe you can use recordset clone to update all of the records except the current. Use an If Then Statement to make sure you are not on current before you .Edit.

Then to edit the current record just use the form and no DAO. Seems like a lot of work and a serious workaround but....

[jerem]

Alright. Well, thanks for your help. I will keep on searching. I just can't believe that a set of records could not be updated by an update query...

[June7]

ItsMe has a point.

Setting value of current record: Me!UnitCost = 1

Then run UPDATE:

CurrentDb.Execute "UPDATE tablename SET UnitCost=1 WHERE ID <> " & Me.ID

Or in the loop using RecordsetClone:

If .ID <> Me.tbxID Then

[jerem]

Yes true. I had thought of somehow updating the current record separately but I wanted to explore this as a last resort solution. I always prefer getting things done clean and explore all the possibilities before going dirty ;-)

Seems that we have reached that point so I'll give it a try. Thanks ItsMe!

[jerem]

The result looks good. the only issue now is that it adds an empty record to my continuous form. Any idea why it would do that?
Here is the code.

Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
    Dim db As Database
    Dim strSql As String
    Dim dblRate As Double
    Set db = CurrentDb
    dblNCostR = DLookup("ExRate", "Currency", "ID = " & [cboCurrency])
    dblRate = dblNCostR / dblOCostR
    With Me.sbfProductDetails.Form
        .txtUnitCost = .txtUnitCost * dblRate
    End With
    DoCmd.SetWarnings False
        strSql = "UPDATE QuoteLineItems SET QuoteLineItems.UnitCost = [UnitCost] * " & dblRate & _
                " WHERE QuoteLineItems.QuoteID = " & Me.txtID & ";"
    DoCmd.RunSQL strSql
    Me.txtExRate.Value = dblNCostR
    Set db = Nothing
ExitSub:
    Exit Sub
ErrHandler:
    Call ErrorAlert
    Resume ExitSub
End Sub

[June7]

By empty record you mean only txtUnitCost has a value? That makes me think focus is on New Record row when the textbox value is set. Could be that with the AfterUpdate event, focus has moved to the next record and if you were on the last record then the focus would now be New Record.

If you are going to use SetWarnings then should reset back to True at end of procedure. Use CurrentDb.Execute and can eliminate SetWarnings.

Value is default property of data controls. Do not have to type it but certainly doesn't hurt if you do.

[jerem]

Yes you are right. This only happens if the cursor is in a new record. I made the other changes as suggested...

Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
    Dim db As Database
    Dim strSql As String
    Dim dblRate As Double
    Set db = CurrentDb
    dblNCostR = DLookup("ExRate", "Currency", "ID = " & [cboPoCurrency])
    dblRate = dblNCostR / dblOCostR
    With Me.sbfProductDetails.Form
        .txtUnitCost.Value = .txtUnitCost.Value * dblRate
    End With
        strSql = "UPDATE QuoteLineItems SET QuoteLineItems.UnitCost = [UnitCost] * " & dblRate & _
                " WHERE QuoteLineItems.QuoteID = " & Me.txtID & ";"
    db.Execute strSql
    Me.txtPOExchRate.Value = dblNCostR
    Set db = Nothing
ExitSub:
    Exit Sub
ErrHandler:
    Call ErrorAlert
    Resume ExitSub
End Sub

[June7]

Might try BeforeUpdate event.