Check if there is an existing record on ms access 2007

[jeffersonalomia]

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
            .Open()

        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)
                .Cancel()

            Else


                'Save'


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




                .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)
                .Update()
                .Close()


            End If

        End With

[oussama_1]

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

[Reverend Jim]

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

SELECT COUNT(*) FROM Applicant
 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
Else
    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.