Solved

VBA Code - Check if record exists

Posted on 2010-11-12
2
1,718 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
2 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now