Can someone help me in my source code...
my problem is, i need to check if there is an existing record on my current database(ms access 2007)

here is my connection that i've place on module

Public con As ADODB.Connection
    Public rs As ADODB.Recordset

    Public Sub connection()
        con = New ADODB.Connection
        With con
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DB/HousingDB.accdb"
            .CursorLocation = ADODB.CursorLocationEnum.adUseClient

        End With
    End Sub

here is my code on save...pls help me how to identify if there is an existing record on my database(ms access 2007)

 Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click

        rs = New ADODB.Recordset

        With rs

            'check if important item is null '

            If txtappln.Text = "" Or txtappfn.Text = "" Or txtappmn.Text = "" Or txtclass.Text = "" Or txtcnum.Text = "" Or txtaddr.Text = "" Or txtbrgy.Text = "" Then
                MsgBox("Some object in the Applicant Personal Information or Classification or Ctrl Number is not filled up", MessageBoxIcon.Warning)



                .Open("Select * from Applicant", con, 2, 3)

                .Fields("LAST_NAME").Value = txtappln.Text
                .Fields("FIRST_NAME").Value = txtappfn.Text
                .Fields("MIDDLE_NAME").Value = txtappmn.Text
                .Fields("ADDRESS").Value = txtaddr.Text
                .Fields("CLASSIFICATION").Value = txtclass.Text
                .Fields("CONTROL_NO").Value = txtcnum.Text
                .Fields("BARANGAY").Value = txtbrgy.Text

                MsgBox("Record has been save !!", vbInformation)

            End If

        End With
2 Years
Discussion Span
Last Post by Reverend Jim

the easy and fast way for me is to load the access database into a datagrid and search through it.


One field in your table should be declared as being the PRIMARY KEY. What you can do is execute the query

 WHERE PKField = somevalue

If the query returns the value 0 then that record does not exist and it is safe to add it. You could package this in a Function named RecordExists and have it return the value True or False so your code looks like

If Not RecordExists(PKFieldValue) Then
    'add the record
    MsgBox("Record already exists")
End If

If you don't have a Primary key field (and you should) you will have to modify the query accordingly.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.