Hi there I have connected visual basic express to my access database and designed a windows form with navigation functions as well as add delete, update functions. I have used textboxes to display the database fields on the form, but i am having trouble displaying an updated autonumber when a user adds a new record to the database. Instead of the autonumber incrementing by 1 it appears on the form as the previous autonumber value. When i add a new record it successfully adds the new row to the database with a new incremented auto number in access.

So basically i am stuck on how to increment the autonumber by +1 when the user clicks the add record button so that all the textboxes on the form would be blank except the record ID which woud be a new incremented autonumber.

I have included my code below. I am new to visual basic so please go easy on me. If anyone can help me it is much appreciated. Thanks for your time.


Public Class Form1
Dim inc As Integer
Dim MaxRows As Integer

Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String

Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
'TODO: This line of code loads data into the 'AddressBookDataSet.tblContacts' table. You can move, or remove it, as needed.
Me.TblContactsTableAdapter.Fill(Me.AddressBookDataSet.tblContacts)
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\AddressBook.mdb"
con.Open()

sql = "SELECT * FROM tblContacts"
da = New OleDb.OleDbDataAdapter(sql, con)

da.Fill(ds, "AddressBook")

con.Close()

MaxRows = ds.Tables("AddressBook").Rows.Count
inc = -1


End Sub

Private Sub NavigateRecords()
txtRecordID.Text = ds.Tables("AddressBook").Rows(inc).Item(0)
txtFirstName.Text = ds.Tables("AddressBook").Rows(inc).Item(1)
txtSurname.Text = ds.Tables("AddressBook").Rows(inc).Item(2)
txtAddress1.Text = ds.Tables("AddressBook").Rows(inc).Item(3)
txtAddress2.Text = ds.Tables("AddressBook").Rows(inc).Item(4)
txtAddress3.Text = ds.Tables("AddressBook").Rows(inc).Item(5)
txtPostcode.Text = ds.Tables("AddressBook").Rows(inc).Item(6)
txtPhone.Text = ds.Tables("AddressBook").Rows(inc).Item(7)
txtEmail.Text = ds.Tables("AddressBook").Rows(inc).Item(8)
txtNotes.Text = ds.Tables("AddressBook").Rows(inc).Item(9)


End Sub


Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
If inc > 0 Then
inc = inc - 1
NavigateRecords()
ElseIf inc = -1 Then
MsgBox("No Records Yet")
ElseIf inc = 0 Then
MsgBox("First Record")
End If

End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If inc <> MaxRows - 1 Then
inc = inc + 1
NavigateRecords()
Else
MsgBox("No More Rows")
End If

End Sub

Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
If inc <> 0 Then
inc = 0
NavigateRecords()
End If


End Sub

Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
If inc <> MaxRows - 1 Then
inc = MaxRows - 1
NavigateRecords()
End If


End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

If inc <> MaxRows - 1 Then
inc = MaxRows - 1
NavigateRecords()

End If

Dim cb As New OleDb.OleDbCommandBuilder(da)
ds.Tables("AddressBook").Rows(inc).Item(0) = txtRecordID.Text
ds.Tables("AddressBook").Rows(inc).Item(1) = txtFirstName.Text
ds.Tables("AddressBook").Rows(inc).Item(2) = txtSurname.Text
ds.Tables("AddressBook").Rows(inc).Item(3) = txtAddress1.Text
ds.Tables("AddressBook").Rows(inc).Item(4) = txtAddress2.Text
ds.Tables("AddressBook").Rows(inc).Item(5) = txtAddress3.Text
ds.Tables("AddressBook").Rows(inc).Item(6) = txtPostcode.Text
ds.Tables("AddressBook").Rows(inc).Item(7) = txtPhone.Text
ds.Tables("AddressBook").Rows(inc).Item(8) = txtEmail.Text
ds.Tables("AddressBook").Rows(inc).Item(9) = txtNotes.Text
da.Update(ds, "AddressBook")

MsgBox("Data updated")


End Sub

Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click



btnCommit.Enabled = True
btnAddNew.Enabled = False
btnUpdate.Enabled = False
btnDelete.Enabled = False

If inc <> MaxRows - 1 Then
inc = MaxRows - 1
NavigateRecords()
End If


txtFirstName.Clear()
txtSurname.Clear()
txtAddress1.Clear()
txtAddress2.Clear()
txtAddress3.Clear()
txtPostcode.Clear()
txtPhone.Clear()
txtEmail.Clear()
txtNotes.Clear()


End Sub

Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
btnCommit.Enabled = False
btnAddNew.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True

inc = 0
NavigateRecords()

End Sub

Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
If inc <> -1 Then

Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow

dsNewRow = ds.Tables("AddressBook").NewRow()
dsNewRow.Item("ID") = txtRecordID.Text
dsNewRow.Item("FirstName") = txtFirstName.Text
dsNewRow.Item("Surname") = txtSurname.Text
dsNewRow.Item("Address1") = txtAddress1.Text
dsNewRow.Item("Address2") = txtAddress2.Text
dsNewRow.Item("Address3") = txtAddress3.Text
dsNewRow.Item("Postcode") = txtPostcode.Text
dsNewRow.Item("Phone") = txtPhone.Text
dsNewRow.Item("Email") = txtEmail.Text
dsNewRow.Item("Notes") = txtNotes.Text

ds.Tables("AddressBook").Rows.Add(dsNewRow)

da.Update(ds, "AddressBook")

MsgBox("New Record added to the Database")




btnCommit.Enabled = False
btnAddNew.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True

End If


End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

If MessageBox.Show("Do you really want to Delete this Record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
MsgBox("Operation Cancelled")
Exit Sub

End If


Dim cb As New OleDb.OleDbCommandBuilder(da)

ds.Tables("AddressBook").Rows(inc).Delete()
MaxRows = MaxRows - 1

inc = 0
NavigateRecords()
da.Update(ds, "AddressBook")


End Sub
End Class

Oh that is really too bad nobody answered that post, I had exactly the same issue

make a function or procedure to do autonumber. every you finish add,edit,delete or clear form call that procedure or function
the easiest logic of autonumber is select the last value of RecordID and add it by 1.

Thank you so much !

you're welcome my friend.
don't forget to mark this thread solved.

you have not to worry

i have solution of this problem

you have to do

on form load

1assign the value to a variable form your data base

on click on add new button

you have to increment the value of (assign database value var) to generate new value

use flag if required

for any kind of web design related and vb problem you can visit
http://www.webdesignforums.net