Updating an Access database with a secondary form

[kristinaswan]

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.

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

[Fungus1487]

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.

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