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

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

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