VBA Code - Check if record exists

Hi,

I need to use If statement to find if the record exists, if it does not exits, then I will add a record, but if it does, then I will edit the record.

If RecordExists = True Then
MSGBox("Etc.......")
Else
Create a record

What is the best way to do it?

Thank you
Sub UpdateExcelToAccess(x As Integer, ByVal nw As Date)
Dim newCount As Long
Dim i As Long
Dim cn As New ADODB.Connection
Dim rs As Recordset
Dim sh As Worksheet
Dim WkDate As Date
Dim myData As String
Dim DT As String
Dim RowVal As Integer
DT = Format(nw, "Short Date")
i = x

Set sh = ActiveWorkbook.ActiveSheet
myData = "SS_Test.accdb"

With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Data Source") = myData
.Open
End With


Set rs = New ADODB.Recordset

 RowVal = Cells.Range("R15").Value
  rs.Open "select * from CCS_Table where CCS_Table.Row = " & RowVal & " AND Week_Start = #" & nw & "#", cn, adOpenKeyset, adLockOptimistic

 'If Not rs.EOF Then
        'Do While Not rs.EOF
            ' process
           ' rs.MoveNext
       ' Loop
    'Else
        'MsgBox ("No Record")
   ' End If
 'No record found so lets add it
 'If rs.EOF = True Then
    'rs.AddNew
'End If
  
 If Cells.Range("A" & i).Value <> Empty Then
 
    rs.Fields("Name") = Cells.Range("A" & i).Value
    rs.Fields("Note & Comments") = Cells.Range("B" & i).Value
    rs.Fields("Desk Sharing") = Cells.Range("D" & i).Value
    rs.Fields("Touch Spot") = Cells.Range("C" & i).Value
    rs.Fields("In_Use") = Cells.Range("E" & i).Value
    rs.Fields("Cube") = Cells.Range("F" & i).Value
    rs.Fields("Desk") = Cells.Range("G" & i).Value
    rs.Fields("Fri") = Cells.Range("H" & i).Value
    rs.Fields("Mon") = Cells.Range("I" & i).Value
    rs.Fields("Tues") = Cells.Range("J" & i).Value
    rs.Fields("Wed") = Cells.Range("K" & i).Value
    rs.Fields("Thur") = Cells.Range("L" & i).Value
    rs.Fields("Starting_Date") = Cells.Range("M" & i).Value
    rs.Fields("Ending_Date") = Cells.Range("N" & i).Value
    rs.Fields("Week_Start") = DT
    rs.Fields("Row") = Cells.Range("R15").Value
    rs.Fields("DateStamp") = Date
    rs.update
  Else
    MsgBox "Value is empty!"
 End If

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

Open in new window

RadshaykhoAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
Suggestion to improve your code a little. Do the check of empty first before opening the db
if record not found do addnew then
Sub UpdateExcelToAccess(x As Integer, ByVal nw As Date)

    Dim newCount As Long
    Dim i As Long
    Dim cn As New ADODB.Connection
    Dim rs As Recordset
    Dim sh As Worksheet
    Dim WkDate As Date
    Dim myData As String
    Dim DT As String
    Dim RowVal As Integer
    
    
    'If cell value
    If Cells.Range("A" & i).Value <> Empty Then
    
        DT = Format(nw, "Short Date")
        i = x
        
        Set sh = ActiveWorkbook.ActiveSheet
        myData = "SS_Test.accdb"
        
        With cn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Properties("Data Source") = myData
            .Open
        End With
        
        Set rs = New ADODB.Recordset
        
        RowVal = Cells.Range("R15").Value
        rs.Open "select * from CCS_Table where CCS_Table.Row = " & RowVal & " AND Week_Start = #" & nw & "#", cn, adOpenKeyset, adLockOptimistic
        
        'No record found so lets add it
        If rs.EOF = True Then
            rs.AddNew
        End If
      
        rs.Fields("Name") = Cells.Range("A" & i).Value
        rs.Fields("Note & Comments") = Cells.Range("B" & i).Value
        rs.Fields("Desk Sharing") = Cells.Range("D" & i).Value
        rs.Fields("Touch Spot") = Cells.Range("C" & i).Value
        rs.Fields("In_Use") = Cells.Range("E" & i).Value
        rs.Fields("Cube") = Cells.Range("F" & i).Value
        rs.Fields("Desk") = Cells.Range("G" & i).Value
        rs.Fields("Fri") = Cells.Range("H" & i).Value
        rs.Fields("Mon") = Cells.Range("I" & i).Value
        rs.Fields("Tues") = Cells.Range("J" & i).Value
        rs.Fields("Wed") = Cells.Range("K" & i).Value
        rs.Fields("Thur") = Cells.Range("L" & i).Value
        rs.Fields("Starting_Date") = Cells.Range("M" & i).Value
        rs.Fields("Ending_Date") = Cells.Range("N" & i).Value
        rs.Fields("Week_Start") = DT
        rs.Fields("Row") = Cells.Range("R15").Value
        rs.Fields("DateStamp") = Date

        rs.Update
    
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
    Else
        MsgBox "Value is empty!"
    End If
    
End Sub

Open in new window

0
 
RadshaykhoAuthor Commented:
This does it.

Thank you rockiroads!

Rad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.