?
Solved

VBA Code - Check if record exists

Posted on 2010-11-12
2
Medium Priority
?
2,244 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:Radshaykho
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 34126195
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
 

Author Closing Comment

by:Radshaykho
ID: 34126392
This does it.

Thank you rockiroads!

Rad
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question