Update query working except for the last modified record

[jerem]

Hi
I have this update query that is triggered by an after update event on a main form. The records being updated are in a continuous subform. It works well except from the last added/modified record. If I save and close the form and then open it again it works for all records but if modify or add a record, the update query will not work for that last modified/added record.

I have tried several things such as save record, use dirty = false for the on exit event of the subform control but nothing works. Here is the procedure:

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

Hope someone can help!


I have posted this on other forums without results.

[June7]

Exactly what is being attempted here? You want to use UPDATE action to edit records of the form, not another table outside form?

Why do you need to save this calculated value? Why not calculate in query when needed?


Use CurrentDb.Execute and the SetWarnings is not needed which should be set back to True in your code.

[jerem]

To update a set of record in a subform based on a value. The result must be stored because the value can change over time but a record of the result at that time needs to remain. Also the result should be editable by the user.
I tried currentDb.Execute but it did not work.

[jerem]

Hum... I should also say that the tables are linked to Sharepoint lists. Could that be a record lock issue?

[June7]

I know little about SharePoint - never used. I thought VBA was not possible with web enabled database.

If you want to populate fields of a form then should not need to use recordset. I still don't understand why multiple records would need to be updated. If the same rate is applied to all line items then store the rate with the parent quote record not with each related line item.

[jerem]

It is not a web app. It is a desktop application with linked tables to Sharepoint, hence vba works.
The reason why the update on each record has to happen is that the user still wants to be able to edit the result manually after the rate has been applied. Don't ask me why but that's the way it is.

[June7]

So the rate is allowed to be different for each line item?

Not sure why your code fails. It looks fine to me.

CurrentDb.Execute had the same result? I did not expect otherwise. It simply allowed not having to use the SetWarnings lines.

If you want to provide db for analysis, follow instructions at bottom of my post.

[jerem]

I'm pretty sure it is a record lock issue from Sharepoint but I don't know how to handle it. When I convert the linked tables to local ones, there is no more problem, hence, my suspicion for the record lock.

[June7]

Reasonable conclusion. Sorry, I have no experience with SharePoint.

[jerem]

Ok since the current record is locked, I can still update it separately through the form... So here is the new code. It works well. The only issue is that it adds an unwanted empty record to my continuous form:

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]

See comment in your other thread https://www.accessforums.net/forms/l...ash-44959.html. Just realized these two threads are basically the same issue.

[jerem]

Updated as per June7's comments...

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