Updating an Access database with a secondary form

Expand Post »

Sorry this is so long, but I don't know what would be needed to help me. I also aplogize for my messy coding and lack of notes. I am doing a project for a friend, but am having an issue with modfying an MS Access database with a windows form. I have a form designed and implemented into my main form (frmViewCustomers), but am not sure how to get the record updated with the second form. I know I could do it with the initial form, but I'd rather make it simple for them, as they don't know a thing about computers. A second form would make it easy for them to understand that they are updating, instead of "accidentally" changing something. below is the code for the main form I mentioned. It is just a record viewer and works perfectly the way I want it to work (except the SearchByFactor function). Does anyone have any ideas on how to implement the second form (frmAdd) to update it? I will add a "Delete Record" button later, but for now they just need to be able to add them in and view them. Thanks in advance for any help with this solution.
EDIT: I am using VisualStudio.NET 2003. Forgot to mention this earlier.

(Toggle Plain Text)

PublicClass frmViewCustomers
Inherits System.Windows.Forms.Form
Dim currmanager As CurrencyManager
Private Sub mnuExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuExit.Click
Me.Close()
End Sub
Private Sub frmViewCustomers_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) _
Handles MyBase.Closing
If MessageBox.Show("Are you sure you want to exit the program?", _
"Confirm EXIT", MessageBoxButtons.YesNo) = DialogResult.No Then
e.Cancel = True
Else
e.Cancel = False
End If
End Sub
 
Private Sub mnuAboutAbout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuAboutAbout.Click
MessageBox.Show(" v1.0" & ControlChars.CrLf & _
"A program by Kristina M Swanson" & ControlChars.CrLf & _
" Copyright 2007", MessageBoxIcon.Information)
End Sub
Private Sub mnuSearchByName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuSearchByName.Click
Dim sName As String
sName = InputBox("Enter a name to find.")
FindByName(sName)
If DsCustomer1.tblCustomer.Count = 0 Then
MessageBox.Show("No records matched. Pleae check your spelling and try again.", "Search results")
GetAllRecords()
End If
End Sub
Private Sub mnuSearchByPhone_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuSearchByPhone.Click
Dim sPhone As String
sPhone = InputBox("Enter a number to find.")
FindByPhone(sPhone)
If DsCustomer1.tblCustomer.Count = 0 Then
MessageBox.Show("No records matched. Pleae check your number and try again.", "Search results")
GetAllRecords()
End If
End Sub
Private Sub mnuSearchByAddress_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuSearchByAddress.Click
Dim sAddress As String
sAddress = InputBox("Enter a address to find.")
FindByAddress(sAddress)
If DsCustomer1.tblCustomer.Count = 0 Then
MessageBox.Show("No records matched. Pleae check your address and try again.", "Search results")
GetAllRecords()
End If
End Sub
Private Sub mnuSearchByCity_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuSearchByCity.Click
Dim sCity As String
sCity = InputBox("Enter a city to find.")
FindByCity(sCity)
If DsCustomer1.tblCustomer.Count = 0 Then
MessageBox.Show("No records matched. Pleae check your spelling and try again.", "Search results")
GetAllRecords()
End If
End Sub
Private Sub mnuSearchByDestination_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuSearchByDestination.Click
Dim sDest As String
sDest = InputBox("Enter a destination to find.")
FindByDest(sDest)
If DsCustomer1.tblCustomer.Count = 0 Then
MessageBox.Show("No records matched. Pleae check your spelling and try again.", "Search results")
GetAllRecords()
End If
End Sub
Private Sub mnuSearchByFactor_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuSearchByFactor.Click
Dim iFactor As String
iFactor = InputBox("Enter a Trip Factor to find.")
FindByFactor(iFactor)
If DsCustomer1.tblCustomer.Count = 0 Then
MessageBox.Show("No records matched. Please check your spelling and try again.", "Search results")
GetAllRecords()
End If
End Sub
Private Sub frmViewCustomers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
daCustomer.Fill(DsCustomer1)
currmanager = Me.BindingContext(DsCustomer1, "tblCustomer")
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If currmanager.Position < (currmanager.Count - 1) Then
currmanager.Position += 1
Else
MessageBox.Show("No more records.")
End If
End Sub
Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
If currmanager.Position > 0 Then
currmanager.Position -= 1
Else
MessageBox.Show("First record reached.")
End If
End Sub
Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
If currmanager.Position < (currmanager.Count - 1) Then
currmanager.Position = currmanager.Count - 1
Else
MessageBox.Show("This is the last record.")
End If
End Sub
Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
If currmanager.Position > 0 Then
currmanager.Position = 0
Else
MessageBox.Show("This is the first record.")
End If
End Sub
Private Sub GetAllRecords()
daCustomer.SelectCommand.CommandText = "Select * " & "From tblCustomer;"
DsCustomer1.Clear()
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByName(ByVal sName As String)
Dim sql As String
sql = "Select * From tblCustomer where fldName = '" & sName & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByPhone(ByVal sPhone As String)
Dim sql As String
sql = "Select * From tblCustomer where fldPhoneNumber = '" & sPhone & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByAddress(ByVal sAddress As String)
Dim sql As String
sql = "Select * From tblCustomer where fldAddress1 = '" & sAddress & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByCity(ByVal sCity As String)
Dim sql As String
sql = "Select * From tblCustomer where fldCity = '" & sCity & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByDate(ByVal sDate As String)
Dim sql As String
sql = "Select * From tblCustomer where fldDate = '" & sDate & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByDest(ByVal sDest As String)
Dim sql As String
sql = "Select * From tblCustomer where fldDestination = '" & sDest & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByFactor(ByVal sFactor As String)
Dim sql As String
sql = "Select * From tblCustomer where fldTripFactor = '" & sFactor & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub btnModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModify.Click
Dim addForm As New frmAdd
addForm.ShowDialog()
End Sub
EndClass

PublicClass frmViewCustomers
Inherits System.Windows.Forms.Form
Dim currmanager As CurrencyManager
Private Sub mnuExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuExit.Click
Me.Close()
End Sub
Private Sub frmViewCustomers_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) _
Handles MyBase.Closing
If MessageBox.Show("Are you sure you want to exit the program?", _
"Confirm EXIT", MessageBoxButtons.YesNo) = DialogResult.No Then
e.Cancel = True
Else
e.Cancel = False
End If
End Sub
 
Private Sub mnuAboutAbout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuAboutAbout.Click
MessageBox.Show(" v1.0" & ControlChars.CrLf & _
"A program by Kristina M Swanson" & ControlChars.CrLf & _
" Copyright 2007", MessageBoxIcon.Information)
End Sub
Private Sub mnuSearchByName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuSearchByName.Click
Dim sName As String
sName = InputBox("Enter a name to find.")
FindByName(sName)
If DsCustomer1.tblCustomer.Count = 0 Then
MessageBox.Show("No records matched. Pleae check your spelling and try again.", "Search results")
GetAllRecords()
End If
End Sub
Private Sub mnuSearchByPhone_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuSearchByPhone.Click
Dim sPhone As String
sPhone = InputBox("Enter a number to find.")
FindByPhone(sPhone)
If DsCustomer1.tblCustomer.Count = 0 Then
MessageBox.Show("No records matched. Pleae check your number and try again.", "Search results")
GetAllRecords()
End If
End Sub
Private Sub mnuSearchByAddress_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuSearchByAddress.Click
Dim sAddress As String
sAddress = InputBox("Enter a address to find.")
FindByAddress(sAddress)
If DsCustomer1.tblCustomer.Count = 0 Then
MessageBox.Show("No records matched. Pleae check your address and try again.", "Search results")
GetAllRecords()
End If
End Sub
Private Sub mnuSearchByCity_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuSearchByCity.Click
Dim sCity As String
sCity = InputBox("Enter a city to find.")
FindByCity(sCity)
If DsCustomer1.tblCustomer.Count = 0 Then
MessageBox.Show("No records matched. Pleae check your spelling and try again.", "Search results")
GetAllRecords()
End If
End Sub
Private Sub mnuSearchByDestination_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuSearchByDestination.Click
Dim sDest As String
sDest = InputBox("Enter a destination to find.")
FindByDest(sDest)
If DsCustomer1.tblCustomer.Count = 0 Then
MessageBox.Show("No records matched. Pleae check your spelling and try again.", "Search results")
GetAllRecords()
End If
End Sub
Private Sub mnuSearchByFactor_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles mnuSearchByFactor.Click
Dim iFactor As String
iFactor = InputBox("Enter a Trip Factor to find.")
FindByFactor(iFactor)
If DsCustomer1.tblCustomer.Count = 0 Then
MessageBox.Show("No records matched. Please check your spelling and try again.", "Search results")
GetAllRecords()
End If
End Sub
Private Sub frmViewCustomers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
daCustomer.Fill(DsCustomer1)
currmanager = Me.BindingContext(DsCustomer1, "tblCustomer")
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If currmanager.Position < (currmanager.Count - 1) Then
currmanager.Position += 1
Else
MessageBox.Show("No more records.")
End If
End Sub
Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
If currmanager.Position > 0 Then
currmanager.Position -= 1
Else
MessageBox.Show("First record reached.")
End If
End Sub
Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
If currmanager.Position < (currmanager.Count - 1) Then
currmanager.Position = currmanager.Count - 1
Else
MessageBox.Show("This is the last record.")
End If
End Sub
Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
If currmanager.Position > 0 Then
currmanager.Position = 0
Else
MessageBox.Show("This is the first record.")
End If
End Sub
Private Sub GetAllRecords()
daCustomer.SelectCommand.CommandText = "Select * " & "From tblCustomer;"
DsCustomer1.Clear()
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByName(ByVal sName As String)
Dim sql As String
sql = "Select * From tblCustomer where fldName = '" & sName & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByPhone(ByVal sPhone As String)
Dim sql As String
sql = "Select * From tblCustomer where fldPhoneNumber = '" & sPhone & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByAddress(ByVal sAddress As String)
Dim sql As String
sql = "Select * From tblCustomer where fldAddress1 = '" & sAddress & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByCity(ByVal sCity As String)
Dim sql As String
sql = "Select * From tblCustomer where fldCity = '" & sCity & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByDate(ByVal sDate As String)
Dim sql As String
sql = "Select * From tblCustomer where fldDate = '" & sDate & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByDest(ByVal sDest As String)
Dim sql As String
sql = "Select * From tblCustomer where fldDestination = '" & sDest & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub FindByFactor(ByVal sFactor As String)
Dim sql As String
sql = "Select * From tblCustomer where fldTripFactor = '" & sFactor & "';"
DsCustomer1.Clear()
daCustomer.SelectCommand.CommandText = sql
daCustomer.Fill(DsCustomer1)
currmanager.Refresh()
End Sub
Private Sub btnModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModify.Click
Dim addForm As New frmAdd
addForm.ShowDialog()
End Sub
EndClass

Last edited by kristinaswan; Jul 8th, 2007 at 10:56 pm. Reason: Added information

Similar Threads

Writing to an Access Database in Visual Basic 4 / 5 / 6
load data from access database into form in VB.NET
Two Issues With Access Database App in MS Access and FileMaker Pro
I hava trouble with Microsoft Access database, Pls help me. in Java
Saving information from .NET webcontol into access(database) in ASP.NET

kristinaswan

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

4 posts
since Dec 2006

Permalink

Jul 9th, 2007

Re: Updating an Access database with a secondary form

im guessing your db is set up correctly with primary keys etc. if so just pass your primary key to your form if you are updating the record else if not. create a recordset add your user entered details to it and add this to the database as follows.

VB.NET Syntax (Toggle Plain Text)
On Error GoTo Exit_RaiseError
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        Dim sql As String
        Dim nid As Long
 
        cnn = New ADODB.Connection
        cnn.ConnectionString = "your connection string"
        cnn.Open()
        sql = "SELECT * FROM tablename WHERE tablekey = " & nid
        With rst
            rst.Open(sql, cnn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
            .AddNew() ' add new record
                .Fields("fieldname").Value = "add some stuff here" ' edit the field with fieldname 'fieldname'
            .Update() 'update the db
        End With
 
        rst.Close()
        rst = Nothing
        cnn.Close()
        cnn = Nothing
 
     Exit_RaiseError:
        ' close the sub as an error has occurred
        Exit Sub
On Error GoTo Exit_RaiseError
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        Dim sql As String
        Dim nid As Long

        cnn = New ADODB.Connection
        cnn.ConnectionString = "your connection string"
        cnn.Open()
        sql = "SELECT * FROM tablename WHERE tablekey = " & nid
        With rst
            rst.Open(sql, cnn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
            .AddNew() ' add new record
                .Fields("fieldname").Value = "add some stuff here" ' edit the field with fieldname 'fieldname'
            .Update() 'update the db
        End With

        rst.Close()
        rst = Nothing
        cnn.Close()
        cnn = Nothing

     Exit_RaiseError:
        ' close the sub as an error has occurred
        Exit Sub

this code is used in a vb project for asp.net i know that you will not need to use ADODB reference as you are doing it locally but the rest should work fine. just google search for your connection string

Fungus1487

Reputation Points: 66

Solved Threads: 56

Posting Pro in Training

Offline

459 posts
since Apr 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: Vb.net Project

Next Thread in VB.NET Forum Timeline: I'm new to this game

DaniWeb Message

Cancel Changes

User Name
Password