title: MS SQL 2008/Access 2002 VBA - Check DB for current record, if does not exist then enter it
body: <p>I'm trying to create an SQL string that will check the table Notification for the current record criteria (two different ID values). If it finds a record with both of these values, it won't enter this record into the table. If it doesn't find it, it will.</p>
  
  <p>I've tried using VBA to solve this, however it seems the only way I'm going to be able to do this is to use SQL because of constant Type Mismatch errors that result from Access field types not being the same as SQL field types (IE: Integer being ok in SQL, but the value causing an overflow in Access VBA).</p>
  
  <p>I've been trying to find some sort of WHERE statement that will let me check the table to see if AssetID and NotificationTypeID are already in the table.  If they are, then ignore the insert and move on.</p>
  
  <p>I've seen examples of other types of SQL that answer this question, but I can't get them to work in VBA.</p>
  
  <p>UPDATED CODE(NOTE:'I know, AssetID SHOULD be a LONG. It's an Int in SQL, but when set as an Int in vba for Access, I get an overflow message
  'When I try to set it to long, there's a type mismatch.  String seems to work in SQL at the moment for putting values into the database )</p>
  
  <p>This still isn't working at the .AddNew. It should, but for some reason returns an Invalid Operation error.</p>
  
  <pre><code>Dim Response As Integer
  Dim strSQL As String
  Dim delSQL As String
  Dim NotTypeID As String
  Dim NotDate As Date
  Dim AssetId As String
  Dim rcdCount As Integer
  Dim i As Integer 
  Dim rst As DAO.Recordset
  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim rcd As DAO.Recordset
  Dim strSelect As String
  
  strGroup = ReturnGroup
  
  'Check user credentials before showing notifications
  
  If InStr(1, strGroup, "Not_admins") Or InStr(1, strGroup, "Admins") Then
  
  
          DoCmd.SetWarnings True
  
      'Check the Storage Location query, see if there is a reason to notify the user
      If DCount("*", "qry_UnknownStorageLoc") &gt; 0 Then
  
          'Getting the record count from the query
          rcdCount = DCount("*", "qry_UnknownStorageLoc")
  
          'This is the popup message box that is shown to the user when Fassetrack loads
          'Response = MsgBox("Notice: " &amp; DCount("*", "qry_UnknownStorageLoc") &amp; " record(s) which contain an unknown storage location", vbInformation + vbOKOnly, "UnknownStorage")
  
          strSQL = "SELECT AssetID FROM qry_UnknownStorageLoc"
  
          Set db = CurrentDb
          Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
          i = 1
  
          'Loop through to gather all the records for this notification type
          'and add them to the Notifications table
  
          With rst
              Do Until .EOF
  
                  'Set the AssetID value, then move to the next record in the query
                  AssetId = rst!AssetId
  
                  'NotTypeID is the id of the notification type in the NotificationType table
                  NotTypeID = 1
  
                  rst.MoveNext
  
                  'Setting the notification date to the last date the record was modified with
                  'the logic being the last edit triggered the notification. When the record is
                  'corrected and/or acknowledged, it will no longer trigger a notification.
  
                  'Null checking to ensure no errors occur
  
                  If (IsNull(DLookup("modifiedon", "qry_UnknownStorageLoc"))) Then
                      NotDate = 0
                  Else
                      NotDate = DLookup("modifiedon", "qry_UnknownStorageLoc")
                  End If
  
                  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                  strSelect = "SELECT n.NotificationTypeID, n.NotificationDate, n.AssetID" &amp; vbCrLf &amp; _
                      "FROM Notifications AS n" &amp; vbCrLf &amp; _
                      "WHERE n.NotificationTypeID = [pType] AND n.NotificationDate = [pDate] AND n.AssetID = [pID];"
                  Debug.Print strSelect
  
                  Set qdf = db.CreateQueryDef(vbNullString, strSelect)
                  With qdf
                      .Parameters("pType").Value = NotTypeID
                      .Parameters("pDate").Value = NotDate
                      .Parameters("pID").Value = AssetId
                      Set rs = .OpenRecordset(dbOpenDynaset, dbSeeChanges)
                  End With
  
                  With rs
                      If .BOF And .EOF Then
                          .AddNew
                          !NotificationTypeID.Value = NotTypeID
                          !NotificationDate.Value = NotDate
                          !AssetId.Value = AssetId
                          .Update
                      End If
                      .Close
  
                  End With
                  i = i + 1
  
              Loop
          End With
  
          'Close and clear the recordset
          rst.Close
          Set rst = Nothing
      End If
  </code></pre>