Only updates every 2nd time

[Middlemarch]

 Set rst = db.OpenRecordset("SELECT Disks.TPL FROM Disks WHERE Disks.Cat=" & Cat & ";")
    
        total = Str$(hr) & ":" & Format(min, "00") & ":" & Format(sec, "00")
    
     If rst.RecordCount <> 0 Then
         rst.Edit
        rst!TPL = total
        rst.Update
        Me.Refresh
        MsgBox Me.TPL
    End If

TPL is bound to the table and I expected the above to update it. But it happens only every 2nd time.
If I erase TPL Then run the code the Msgbox fails with invalid use of Null.


BUT hovering over the word total shows the correct data. Moving over rst!TPL or Me.TPL shows Null
Is there are logic to that? To me it feels that just cannot happen.
If I don't erase it first, it is ok

[Welshgasman]

Have you walked the code, yiou know, simple debugging, see what you have, not what you think you have?
Especially on the 2nd run.
Does it happen for the 3rd run and miss on the 4th?

Is it only updating everysecond time, or just on the first time for that category?

[Middlemarch]

Yes I have stepped through the code, this is how I knew it was happening. Looking at the Pop up hover display
There is no 3rd or 4th run. If the control contains no data it fails to update until you do it a second time,
That happens every time. And I don't believe it either, but I see it happening.
I'll try a new db with just the essential bits.
I'm correct thinking this is not normal behaviour?

[xps35]

This is difficult to assess. We see a piece of code but this is not the whole story.
How is the code called?
How is it repeated?
Where does CAT's value come from?
How is me.TPL filled?
Why are you doing a me.Refresh?

Perhaps the most important question: why use a difficult recordset when you can also do a simple SQL update (UPDATE.... SET.... WHERE....)?

[CJ_London]

I'm correct thinking this is not normal behaviour?

I suspect not the normal way of doing it. However since you haven’t said what you are trying to achieve or what you actually have (you talk of controls but only show code to a recordset) I’m not prepared to guess

[Micron]

BUT hovering over the word total shows the correct data. Moving over rst!TPL or Me.TPL shows Null
Is there are logic to that?

I don't see what the concatenated string for [Total] has to do with TPL being null or not. I agree, not enough shown. Perhaps Cat is null. Perhaps there is at least one record where Cat is not null but TPL is. Run a query like the one for your recordset, but put criteria on TPL Is Null - and test that. Then test for TPL = "".

Peter may be correct in that you're going about this in the most difficult way.

[Middlemarch]

An Update query still was intermittent. Called from btnCount on the Form with cboTPL. This is bound TPL in Disks table
I'm having more success now with a simpler command
Me!TPL=total:Me.Refresh
But as a learning aid I'd like to know why the recordset fails. So some lines below are remmed out to keep seeing the problem.
Cat is a ID String field in Disks and named TCat in Tracks. It's also returned from Function Thisdisk()
Disks and Tracks are the 2 tables for frmDisks and subform Tracks
TPF is a short text field in Disks
I'm not sure what else I can say to better describe this.
When I first move to the record, everything works. But if cboTPL is cleared and then btnCount clicked the first time nothing happens (except the msgbox showing the error).
Click again and it works. You can repeat this at will. Move to another record and back and it works (until cboTPL is again cleared)
Here is the full routine:
Nothing is written to the Table. rst.RecordCount is 1 and both TCount and Total contain the expected values
rst.update executes with no error. The error shows on the MsgBox line where me!TPL is indeed null. But 3 lines above that it's been assigned to total.

Private Sub btnCount_Click()
    Call UpdateTPL(ThisDisk())
End Sub




Sub UpdateTPL(Cat)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim Durtime As String, min As Long, sec As Long
    Dim total, hr
    Dim sql As String
    Dim TCount As Long
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT DISTINCTROW Tracks.TDuration FROM Tracks WHERE Tracks.TCat=" & Cat & ";")
  
    While Not rst.EOF
        Durtime = Nz(rst!TDuration, "")
        min = min + Val(Left$(LTrim$(Durtime), 2))
        sec = sec + Val(Right$(Durtime, 2))
        rst.MoveNext
    Wend
      TCount = rst.RecordCount
        min = min + Int(sec / 60)
        sec = sec Mod 60
    
        hr = Int(min / 60)
        min = min Mod 60
        Set rst = Nothing
        total = Str$(hr) & ":" & Format(min, "00") & ":" & Format(sec, "00")
     '  sql = "Update Disks set TPL = '" & total & "' Where Cat = " & ThisDisk()
     '   DoCmd.RunSQL sql
    ' Me!TPL = total
    ' Me!TrackCount = TCount


    Set rst = db.OpenRecordset("SELECT Disks.TPL, Disks.TrackCount FROM Disks WHERE Disks.Cat=" & Cat & ";")


        total = Str$(hr) & ":" & Format(min, "00") & ":" & Format(sec, "00")


     If rst.RecordCount <> 0 Then
         rst.Edit
         rst!TrackCount = TCount
        rst!TPL = total
        rst.Update


       Me.Refresh
        MsgBox Me.TPL
    End If
    Set rst = Nothing
    Set db = Nothing
End Sub

[Micron]

IMO you're past the point of being able to garner a solution by describing the process. If you can't post a sample db that demonstrates the anomaly, I'm not hopeful that you'll get solution.

[CJ_London]

But as a learning aid I'd like to know why the recordset fails.

Best guess is your form is using the same records, so when you try to update the recordset it fails because the record has been locked by the form.

I'm not sure what else I can say to better describe this.

don't say anything, provide an example db that illustrates the problem

[Middlemarch]

Ok let me try that. A cut down demo might show something too.

[Middlemarch]

I have a 322KB zip file exhibits the problem as described. Repeatedly.
How can I get it to you ?

[June7]

Attach to post.

[Middlemarch]

Thanks June. I should have known!

[Welshgasman]

Yes I have stepped through the code, this is how I knew it was happening. Looking at the Pop up hover display
There is no 3rd or 4th run. If the control contains no data it fails to update until you do it a second time,
That happens every time. And I don't believe it either, but I see it happening.
I'll try a new db with just the essential bits.
I'm correct thinking this is not normal behaviour?

I would say that is not normal behaviour.
The good thing about computers is they just do what they are told, they do not suddenly think, I am tired of doing this, so I won't.

Well not yet, anyway.

[moke123]

The error is your message box. TPL is null and you don't handle that in your code.

try
MsgBox Nz(Me.TPL, "Your value if null here")