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
