Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Unanswered: Check for record existance...

    Hi, everyone,

    I am trying to do the code to check whether the record is already in the table before I add a new record (Since the record only can be unique!)

    How can I write the code to check? I am using form in Access 97. Please help! THANK YOU!!

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    If you DB is set up correctly (with Primary Keys), If you try to add a record where the PK already exists, you will get a error from the database stating that this record can't be added becuase of Duplicate PK

    However if you wish to look before trying, add code (Using DAO) that will query the table first looking for that record and add it if it is not found.

    If you need copy of the DAO syntax let me know.

    S-

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    Originally posted by sbaxter
    If you DB is set up correctly (with Primary Keys), If you try to add a record where the PK already exists, you will get a error from the database stating that this record can't be added becuase of Duplicate PK

    However if you wish to look before trying, add code (Using DAO) that will query the table first looking for that record and add it if it is not found.

    If you need copy of the DAO syntax let me know.

    S-
    Thank you very much!!

    I am trying to write the DAO code, if I can see your copy as a reference would be great!!! Thank you again!!!!

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    The following is some basic DAO code

    It show who to dim the variables and how to add a new record(check1 = "ADD"), how to search for an existing record and update it (check1="MOD"), and how to delete an existing record check1 = "DEL"

    This code is used for unbound forms, but can be adpated for a bound form if necessary

    Good luck

    S-


    On Error GoTo DAOErrHandler

    Dim DAOdb As Database
    Dim DAOrs As Recordset
    Dim sSQL As String

    If Check1 = "ADD" Then

    Set DAOdb = CurrentDb() 'Databse exists in the same DB where you are running code from
    s = "tblcustomer"
    Set DAOrs = DAOdb.OpenRecordset(s)

    With DAOrs

    .AddNew
    .Fields("CustKey") = Me.txtCustKey
    .Fields("CustName") = Me.txtCustName
    .Fields("CustAdd1") = Me.txtCustAdd1
    .Fields("CustAdd2") = Me.txtCustAdd2
    .Fields("CustCity") = Me.txtCustCity
    .Fields("CustST") = Me.txtCustST
    .Fields("CustZip") = Me.txtCustZip
    .Fields("CustPhone") = Me.txtCustPhone
    .Update
    End With
    MsgBox Me.txtCustName & " Added"
    DAOrs.Close
    DAOdb.Close


    ElseIf Check1 = "MOD" Then

    Set DAOdb = CurrentDb()
    s = "Select * from tblCustomer where tblcustomer.custkey = '" & txtCustKey & "';"
    Set DAOrs = DAOdb.OpenRecordset(s)

    With DAOrs

    .Edit
    .Fields("CustName") = Me.txtCustName
    .Fields("CustAdd1") = Me.txtCustAdd1
    .Fields("CustAdd2") = Me.txtCustAdd2
    .Fields("CustCity") = Me.txtCustCity
    .Fields("CustST") = Me.txtCustST
    .Fields("CustZip") = Me.txtCustZip
    .Fields("CustPhone") = Me.txtCustPhone
    .Update
    End With
    DAOrs.Close
    DAOdb.Close
    MsgBox Me.txtCustName & " Edited"



    ElseIf Check1 = "DEL" Then
    Set DAOdb = CurrentDb()
    s = "Select * from tblCustomer where tblcustomer.custkey = '" & txtCustKey & "';"
    Set DAOrs = DAOdb.OpenRecordset(s)

    DAOrs.Delete

    DAOrs.Close
    DAOdb.Close
    MsgBox Me.txtCustName & " Deleted"

    Else
    MsgBox "An Error Occurred. The system doesn't know which action to take"

    End If

    Call cmdClear_Click
    Exit Sub
    DAOErrHandler:

    Select Case Err.Number


    Case Else

    MsgBox Err.Number & vbCrLf & Err.Description
    End Select

  5. #5
    Join Date
    Nov 2003
    Posts
    5

    thx

    THANK YOU VERY MUCH!! I'LL WORK ON IT!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •