Auto Increment value to textbox in ms access 2003, vs 2008

[Ruchi224]

Hello !!

I have a form and some controls as dropdown and textboxes..

The situation is that when the form loads, i want the value in textbox to be incremented, everytime it loads..

This is something i tried.. I get the value '1' everytime !! It shows no sign of increment..

However, i also want it in the format 0001,0002 if possible.. Could anybody please help me out ?? I tried something like this..

Private Sub Order_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        textBox1.ReadOnly = True

        Try
            'MsgBox("Open")
            cn = New OleDbConnection("Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
            cn.Open()
            Message = "Select max(regnum) from order"
            cmd = New OleDbCommand(Message, cn)
            str = cmd.ExecuteScalar()


        Catch myException As Exception

            MsgBox("No Record Inserted" + myException.ToString())

        Finally
            'MsgBox("Closing Connection")

            cn.Close()
        End Try

        If str Is DBNull.Value Then
            Dim intregnum As Integer = Convert.ToInt32(str)
            str = 1
        Else
            str = str + 1
        End If

        textBox1.Text = str

    End Sub

[Ruchi224]

I got it.. Thanx neways.. :)

[Pgmer]

ur doing Select max(regnum) from order, Where is is the insert statement?

str is getting dbnull values i guess.

[Ruchi224]

Yes.. you r right.. I was facing same problem.. But was just trying and solved it.. This is the solution.. Posting for other readers, may help them !!

Private Sub Order_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        textBox1.ReadOnly = True

        Try
            'MsgBox("Open")
            cn = New OleDbConnection("Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
            cn.Open()
            msg = "Select max(regnum) from [order]"
            cmd = New OleDbCommand(msg, cn)
            str = cmd.ExecuteScalar()

        Catch myException As Exception

            MsgBox("No Record Inserted" + myException.ToString())

        Finally
            'MsgBox("Closing Connection")

            cn.Close()
        End Try

        If str Is DBNull.Value Then
            textBox1.Text = "1"
        Else
            textBox1.Text = (Convert.ToInt32(str) + 1).ToString
        End If