Update Query not working on last record (1 Viewer)

[GinaWhipp]

I just got another thought, I have rearranged some lines and added a few, let's see if this works...

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

 

[jerem]

As you were saying... great minds think alike. While reviewing the code I noticed: I have declared the lngID variable and have not used it.
Basically the me.txtID in the SQL statement should be replaced by the variable lngID

 

[jerem]

But this has no effect on the outcome.

 

[GinaWhipp]

Oh, no it doesn't just cleaning up as long as I'm reading...

Did the moving around make any difference? And really, you can leave the Me.txtID, no reason to do it that way doesn't *save* anything.

 

[jerem]

alright... thanks for the advice. There is no difference. I can see that the save and doEvents make the whole process a bit slower but the result is the same. Last modified record doesn't get updated.

 

[jerem]

I just turn the problem around in my head and really I can't see. It's making me crazy!

 

[GinaWhipp]

Lightbulb! Let's requery the Subform, put the line below in place of DoEvents...

[SubformName].Requery

 

[GinaWhipp]

Making you crazy! I'm still here too! :banghead:

 

[jerem]

Yes thank you so much for trying!!! And it must be very late for you!

 

[jerem]

and unfortunately... No effect.

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
    DoCmd.SetWarnings False
    Application.RunCommand acCmdSaveRecord
    Me.sbfProductDetails.Form.Requery
    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

 

[GinaWhipp]

Hmm, I'm gaathering by your reply that did not work either. Did the Subform even react?

Not too late... a little after midnight. It's the shank of the evening or should I say morning...

 

[GinaWhipp]

Do you have a stripped down file you can send? I feel like I'm on a mission now...

 

[jerem]

Can it be sent in private? I would take a bit of time to strip it.

 

[GinaWhipp]

This line...

Me.sbfProductDetails.Form.Requery

should be

Me.sbfProductDetails.Requery

 

[jerem]

Well it's about lunch time on a very sunny day here... I'm based in Hong Kong.

 

[GinaWhipp]

Yes, if you go to my website under my signature there is a Contact form where you can upload, please zip and it needs to be under 5mb

 

[jerem]

tried

 Me.sbfProductDetails.Requery

but no luck

Is there a way to send you the strip file later? I'm sorry but I need to leave now. I can send it later in the afternoon. Thanks so much for helping with this.

 

[jerem]

Ok will do! Thank you!

 

[GinaWhipp]

Okay, waiting and checking eMail!

 

[GinaWhipp]

Hmm, okay, it's almost 1:00 am and still no file. This might have to wait till *my* morning (or rather later today) because I'm getting sleepy!

EDIT (ADD): Well, I guess you went to lunch, so I'll look later today (it's 1:15 AM here). Have a great sunny day!