MS SQL 2008/Access 2002 VBA - Check DB for current record, if does not exist then enter it

Question

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.

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).

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.

I've seen examples of other types of SQL that answer this question, but I can't get them to work in VBA.

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 )

This still isn't working at the .AddNew. It should, but for some reason returns an Invalid Operation error.

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") > 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: " & DCount("*", "qry_UnknownStorageLoc") & " 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" & vbCrLf & _
                    "FROM Notifications AS n" & vbCrLf & _
                    "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

Answer 1

First off huge thanks to cars10 and HansUp, you both got me rolling in the right direction.

I decided to back away from the QueryDefs only because I'm not familiar enough with them yet. Here is the working result.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rs As DAO.Recordset

Dim response As Integer
Dim strSQL As String
Dim strSelect As String
Dim notTypeID As Integer
Dim notItemcode As String
Dim notDate As Date
Dim notAssetId As Long
Dim rcdCount As Integer
Dim i As Integer

'Check the Storage Location query, see if there is a reason to notify the user
    If DCount("*", "qry_UnknownStorageLoc") > 0 Then

        'Set warnings to true to catch any potential errors
        DoCmd.SetWarnings True

        'Getting the record count from the query
        rcdCount = DCount("*", "Notifications", "NotificationTypeID = 1")

        'Set db to the current database and rst to the current recordset from the query qry_OilSolvActNotification
        strSQL = "SELECT AssetID, BarcodeNumber  FROM qry_UnknownStorageLoc WHERE (AssetID NOT IN (SELECT AssetID From Notifications Where NotificationTypeID = 1))"
        Set db = CurrentDb
        Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

        Debug.Print rst.RecordCount

        i = 1

        'Loop through to gather all the records for this notification type
        'and add them to the Notifications table

        With rst
            Do Until rst.EOF

                'Set the AssetID value, then move to the next record in the query
                notAssetId = rst!AssetId
                notItemcode = rst!BarcodeNumber

                '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 * from Notifications WHERE CLng(AssetID) = '" & notAssetId & "' AND ItemCode = '" & notItemcode & "' AND CInt(NotificationTypeID) = '" & CInt(notTypeID) & "'"

                'Set the rs recordset with the records from the table Notifications that match the SQL statement criteria
                Set rs = db.OpenRecordset(strSelect, dbOpenDynaset, dbSeeChanges)

                'Loop the rs recordset. If there is a record to be entered into the Notifications table, insert it.
                With rs
                    If rs.BOF And rs.EOF Then

                        'Set Warnings to false so the user is not presented with a confirmation to add a record every time there is one available.
                        DoCmd.SetWarnings False

                        strSelect = "INSERT INTO Notifications (NotificationTypeID, NotificationDate, AssetID, ItemCode) VALUES('" & notTypeID & "','" & notDate & "','" & notAssetId & "', '" & notItemcode & "');"

                        DoCmd.RunSQL strSelect

                    End If

                    'Close the recordset
                    rs.Close

                End With

                'Clear the recordset
                Set rs = Nothing

                i = i + 1

            Loop
        End With

        'Close and clear the recordset
        rst.Close
        Set rst = Nothing
    End If

    'This is the popup message box that is shown to the user when Fassetrack loads
    response = MsgBox("Notice: " & DCount("*", "Notifications", "NotificationTypeID = 1") & " record(s) which contain an unknown storage location", vbInformation + vbOKOnly, "Fassetrack")

Answer 2

Consider loading a recordset from a temporary QueryDef based on a parameter query. If the recordset is empty, no matching record exists, so you can add the record.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSelect As String

strSelect = "SELECT n.NotificationTypeID, n.NotificationDate, n.AssetID" & vbCrLf & _
    "FROM Notification AS n" & vbCrLf & _
    "WHERE n.NotificationTypeID = [pType] AND n.NotificationDate = [pDate] AND n.AssetID = [pID];"
'Debug.Print strSelect
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strSelect)
With qdf
    .Parameters("pType").Value = NotTypeID
    .Parameters("pDate").Value = NotDate
    .Parameters("pID").Value = AssetId
    'Set rs = .OpenRecordset
    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