Solved

VBA Code - Check if record exists

Posted on 2010-11-12
2
1,783 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

862 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

23 Experts available now in Live!

Get 1:1 Help Now