Only updates every 2nd time

[xps35]

There is no need to store TPL, so why bother. Se my database (reconnect ListBE first).

[Middlemarch]

Yes, but it shouldn't be null. It's just been updated.
Or if it is null why has the update failed. But will work if done a second time.
That's what I don't understand.

[Middlemarch]

That may well be a better method xps35, I'll try it out. Thanks.
But I'm intrigued, the function doesn't run, nor does the click event fire now.
Obviously disabled, but I can't see how and haven't struck this before.

[June7]

I also cannot execute code in either file. Ever since I upgraded to Office 2020 I have had issues running code in downloaded databases. Thought I had already set Trusted Location but had to do it just now then close and open each file to get code to run.

Original code works. If you delete TPL on form, must commit this edit to table before running code again. Use a Refresh or Requery or SaveRecord or If Me.Dirty Then Me.Dirty = False at beginning of procedure.

Why is TPL a combobox with no RowSource? Make it a textbox - Locked Yes and TabStop No.

Should be able to update field with a single UPDATE action SQL instead of looping a recordset. However, I agree with xps35, just calculate this value when needed.

Tracks field is another that could be calculated when needed instead of stored.

Why are there 254 records in Tracks with the same data? Just for testing?

Instead of looping records, let SQL aggregate the data.

    Set rst = db.OpenRecordset("SELECT Sum(Val(Left(LTrim(TDuration), 2))) AS M, " & _
            "Sum(Val(Right(TDuration, 2))) AS S FROM Tracks WHERE TCat=" & Cat)

Or use two DSum() expressions to aggregate data.

Should always have Option Explicit at top of module.

[davegri]

@June7

I also cannot execute code in either file. Ever since I upgraded to Office 2020 I have had issues running code in downloaded databases. Thought I had already set Trusted Location but had to do it just now then close and open each file to get code to run.

Are you aware of this property that only appears for downloaded files?

[Welshgasman]

I do not get that for some reason?

[June7]

Thanks, I may have seen that at some point but forgotten. Maybe this was a 2020 implementation. I jumped from 2010 to 2020.

[Micron]

I do not get that for some reason?

Did that file come from the internet (and the protection was never removed)? If not, I think you don't see the option.

[Welshgasman]

Did that file come from the internet (and the protection was never removed)? If not, I think you don't see the option.

Yes, that is a DB I downloaded from some Access site. That is why it is in the downloads folder on my NAS box. That is so any downloads can be accessed from any computer.

[madpiet]

If you open an object (like a recordset), you should close it.

Aside from all that, why can you not achieve this with a maybe parameterized update query? It's a good thing you can't store a billion rows in an Access database, because this would force it to a crawl.

[davegri]

@jUNE7

Thanks, I may have seen that at some point but forgotten. Maybe this was a 2020 implementation. I jumped from 2010 to 2020.

it's a Windows thing, not just Access. Applies to any downloaded file, I think. At least any File that Microsoft deems capable of execution.

[Middlemarch]

I was able to run the db from xp35 as per daveGri advice. I had seen that before on some images and music files. I have a VB6 routine that removes it.
Ok on calculating a value than than storing it.
June, yes the table was just data to process, and I'll make the combo a textbox. It was good to get the answer must commit this edit to table before running code again.
That's what I thought Me.Refresh (or Me.requery) did. But I see both If Me.Dirty... and Me.Refresh are needed.
Thanks for the info and solution. Very good to get past this, and now I'll use calculated method as suggested.

[June7]

If Me.Dirty and Me.Refresh should not both be needed.

Refresh worked in my test.