Update Query not working on last record

[jerem]

Thanks Gina and sorry I had to dash... I know I have cut you in your mission ;-)
I am working on it know and will send shortly. Thanks again for your help!

 

[jerem]

Oops! I have omitted to share a piece of information. My tables are linked to SharePoint lists.
I have tried to strip the application and to do so I imported relevant tables, form, queries and procedures into a new file. I converted the linked tables into local tables and... It works! No more problem.
Does that mean it has something to do with record lock on SharePoint?

 

[GinaWhipp]

I have a thing I used to say from an old movie...

"I feel I was denied critical, need to know information..." <not mad, just happy to know FINALLY what the problem is>

Yep, it definitely has something to do with Sharepoint, so no need to send a sample, you need to post to the Access Web Forum...

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=88

Make sure you put a link to this thread and explain not double posting but that this issue is with Access linked to Sharepoint. Good Luck! And, sorry, I couldn't be of more help!

 

[jerem]

Sorry about that and thank you. But it is not a web app, it is a desktop app with linked tables.

 

[GinaWhipp]

Oh no, don't be sorry... we have a conclusion! I'm thrilled because I was starting to think I was losing my mind with code that should be working and wasn't!

 

[jerem]

Thread moved to: http://www.access-programmers.co.uk/forums/showthread.php?p=1359210#post1359210

 

[jerem]

Gina,
Since the current record is locked, I can still update it separately through the form... So here is the new code. It works well.

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

 

[GinaWhipp]

I am guessing that in SP the current record is locked? One would think once you leave that record and go to the main form it would *release* it but I guess not the case with SP.

Thank you for posting your solution.. this is certainly going to help someone else!