Generate ID code not working

[Ace_806]

Hi! I found this code in one of the threads here

I converted it into an access kind of code(?) and unfortunately, it won't increment, nor find the top/max in my database. Someone please help?

`

Private Function GenID() As String
        Dim dr As OleDbDataReader
        Dim com As OleDbCommand
        Dim value As String = "2021000"
        Dim ID As String

        Try
            con.Open()
            com = New OleDbCommand("SELECT TOP 1 <studno> FROM record ORDER by <studno> DESC", con)
            dr = com.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.HasRows Then
                dr.Read()
                value = dr.Item("<studno>")
            End If

            value += 1

            If value <= 9 Then                'Value is between 0 and 10
                value = "202100" & value
            ElseIf value <= 99 Then        'Value is between 9 and 100
                value = "20210" & value
            ElseIf value <= 999 Then        'Value is between 999 and 1000
                value = "2021" & value
            End If

        Catch ex As Exception
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            value = "2021000"
        End Try

        Return value
    End Function

    Private Sub addRecords_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        studno.Text = GenID()
    End Sub

`

[rproffitt]

I wish I could help here as I did use VB6 and MySQL for over a decade but as the OS moved on I found it harder and harder to setup VB6 to work on Windows 10 and now, we have ended that app and the last users are told, we no longer support this platform.

Time to move off VB6 is the message.

[tinstaafl]

As this is VBA and Microsoft hasn't replaced it yet, I'll try and help. You're not getting the max ID because you're not looping through the rows.

Also you're using the same variable for strings and integers. This is very bad practice and can lead to very hard to find bugs.

Private Function GenID() As String
        Dim dr As OleDbDataReader
        Dim com As OleDbCommand
        Dim StrValue As String = "2021000"
        Dim value As Integer
        Dim ID As String

        Try
            con.Open()
            com = New OleDbCommand("SELECT TOP 1 <studno> FROM record ORDER by <studno> DESC", con)
            dr = com.ExecuteReader(CommandBehavior.CloseConnection)
            While dr.HasRows
                dr.Read()
                value = dr.Item("<studno>")
            End While

            value += 1

            If value <= 9 Then                'Value is between 0 and 10
                StrValue = "202100" & value
            ElseIf value <= 99 Then        'Value is between 9 and 100
                StrValue = "20210" & value
            ElseIf value <= 999 Then        'Value is between 999 and 1000
                StrValue = "2021" & value
            End If

        Catch ex As Exception
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            StrValue = "2021000"
        End Try

        Return StrValue
End Function

I added another variable to hold the return string and changed the If statement to a While loop. Since I don't have a connection to your database, I can't test this, but I think it will work.