CODEWITHSUNDEEP
excelvbams-accessuserformautonumber
Matt
Matt

Reputation: 31

How to find the next ID auto number from access table and display the number in an Excel userform?

I would like take the next ID auto number from an access table and display it in a textbox on an excel userform. I have found this code and I've played around with it but I'm not getting anywhere and completely lost. I'd be grateful if someone could point me in the right direction to solve it.

    Dim con As Object: Set con = CreateObject("ADODB.Connection")
    Dim rs As Object: Set rs = CreateObject("ADODB.Recordset")
    Dim sql As String

    connectionstring = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
    connectionstring = connectionstring & "DATA Source=C:\Users\MyPc\Desktop\Database1.accdb;"
    con.Open connectionstring

    sql = "select max(ID) as maxvalue from TableOne"

    con.Execute sql

    NextValue = (maxvalue) + 1

     MsgBox NextValue
     userform1.textbox2.value = NextValue
     rs.Close
     Set rs = Nothing

Upvotes: 1

Views: 774

Answers (1)

June7
June7

Reputation: 21370

Execute is for action SQL (INSERT, UPDATE, DELETE) not SELECT. Use SELECT to open a recordset object then read data from recordset.

    Dim con As Object: Set con = CreateObject("ADODB.Connection")
    Dim rs As Object: Set rs = CreateObject("ADODB.Recordset")
    Dim sql As String

    connectionstring = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
    connectionstring = connectionstring & "DATA Source=C:\Users\MyPc\Desktop\Database1.accdb;"
    con.Open connectionstring

    sql = "select max(ID) as maxvalue from TableOne"

    rs.Open sql, con, adOpenStatic, adLockReadOnly 

    NextValue = (rs!maxvalue) + 1

    MsgBox NextValue
    userform1.textbox2.value = NextValue
    rs.Close
    Set rs = Nothing

Since only a single value is needed, consider alternative:

Dim appAccess As Object
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase ("C:\Users\MyPc\Desktop\Database1Umpires.accdb")
userform1.textbox2.value = appAccess.DMax("ID", "TableOne")

Upvotes: 2

Related Questions