[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

VBA Code - Check if record exists

Posted on 2010-11-12
2
Medium Priority
?
2,405 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

649 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