Solved Dmax to find highest ID (1 Viewer)

[ClaraBarton]

I copied this sub from somewhere a long time ago. I'd like to add a column to find the highest ID in all the tables. All ID fields are the first field but all have different names. Like BooksID and BookAuthorsID and PublisherID. My problem is getting the DMax (MxID) line to work. I've tried many things and think I could be close but would appreciate any help.

Public Sub sCountRecords()
On Error GoTo Error_Handler

Dim db As dao.Database
Dim tdf As dao.TableDef
Dim rst As dao.Recordset
Dim rstCount As dao.Recordset
Dim i As Integer
Dim MxID As Long
Dim strSQL As String

    Set db = CurrentDb
    
    strSQL = "DELETE * FROM tblRecordCounts;"
    db.Execute strSQL
  
    Set rstCount = db.OpenRecordset("tblRecordCounts", dbOpenDynaset)
    
    For i = 0 To db.TableDefs.Count - 1
        Set tdf = db.TableDefs(i)
        
        If Mid(tdf.Name, 2, 3) <> "sys" Then
            If tdf.Name <> "tblRecordCounts" Then
                Set rst = db.OpenRecordset(tdf.Name, dbOpenSnapshot)
                MxID = DMax(tdf.Name & ".Fields(0)", tdf.Name)
                If Not rst.EOF Then
                    rst.MoveLast
                    With rstCount
                        .AddNew
                        !TableName = tdf.Name
                        !recCount = rst.RecordCount
                        !MaxID = MxID
                        .Update
                    End With
                Else
                    With rstCount
                        .AddNew
                        !TableName = tdf.Name
                        !recCount = 0
                        !MaxID = MxID
                        .Update
                    End With
                End If
                rst.Close
                Set rst = Nothing
            End If
        End If
    Next i
    
Exit_Here:
    On Error Resume Next
    Set tdf = Nothing
    rstCount.Close
    Set rstCount = Nothing
    MsgBox "Done!"
    
Error_Handler:
    Resume Exit_Here
End Sub

 

[Gasman]

You only need the name of the field?
Why not get that into a string variable and use that?
Have you walked through your code to see what is produced?

 

[ClaraBarton]

I only need the highest ID in the first field. I do not need the name. I've walked through this code for a week and tried every variation except the one that works. Everything produces except for the DMax.

 

[Gasman]

Ok, so you have not walked though your code?
You use .Fields, but I do not see a With statement?
If you hover over the field/variable you would see what it contains.
I am in a hospital car park on my phone, so cannot test your logic.

 

[Gasman]

Back home and between trips.

Here is what you can see when you walk through your code.

                Debug.Print tdf.Fields(0).Name & " - " & tdf.Name

'                Set rst = db.OpenRecordset(tdf.Name, dbOpenSnapshot)
                MxID = DMax(tdf.Fields(0).Name, tdf.Name)
                Debug.Print MxID & " - " & tdf.Name

You are also missing an Exit Sub ?, so the code goes on forever? I have added it in here.

Exit_Here:
    On Error Resume Next
    Set tdf = Nothing
    rstCount.Close
    Set rstCount = Nothing
    MsgBox "Done!"
    Exit Sub

Error_Handler:
    Resume Exit_Here
End Sub

 

[ClaraBarton]

Brilliant! That is EXACTLY what I needed. The whole thing works! Thank you so much.

 

[Gasman]

Brilliant! That is EXACTLY what I needed. The whole thing works! Thank you so much.

Pay attention to what I did to get there.
I did not know the syntax, but by using F8 and Debug.Print I was able to get there.

Your code just jumped straight to the error label, then went around in circles.

That tells me you did not walk through your code? , else you would have found the problem as well?

Doing so, always finds what I call 'silly errors', where the code *might* look OK, but does not do what you think it does.

 

[ClaraBarton]

I erased all my commented errors before showing you the code. I wish I had saved them to show you.