2

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
user3608243
  • 63
  • 6
  • One of the problems preventing me from using VBA to solve this issue is the fact that the ID's in question are Integers in the SQL database. The problem arises when it goes through Access and because the values are in excess of 32000+, I have Overflow errors showing up. I can't change these types as they are preexisting and autoincrementing. – user3608243 Jul 23 '15 at 16:19
  • 1
    Look at this previous post http://stackoverflow.com/questions/19573198/insert-if-no-exists-in-access . It looks like you will have to check for existence first and only then run the insert statement. – Carsten Massmann Jul 23 '15 at 16:25

2 Answers2

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
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Working on this as we speak. One question related to this: Can you have multiple DAO recordsets open to the same database but different tables? – user3608243 Jul 23 '15 at 17:58
  • Yes, that is possible. I don't see how that applies to this question though. – HansUp Jul 23 '15 at 17:59
  • I have a recordset directed to a query that does a loop through and provides the records for NotTypeID, NotDate and AssetId. I'm trying to create a table that will update with new query entries everytime the database is loaded up. This is why I want it to check for dupliates. If it's already there, skip it, if it's a new query entry that isn't in Notification, enter it. I was just worried that having a second DAO recordset active would mess things up. – user3608243 Jul 23 '15 at 18:02
  • With regard to the code above, everything seems to work fine until I hit The "With rs" loop. Both .BOF and .EOF come back true (which is good), but then as soon as it hits .AddNew, I get an error saying Invalid operation. – user3608243 Jul 23 '15 at 18:08
  • 1
    I got the table name wrong: *Notifications* instead of *Notification*. And since that is a link to a SQL Server table, try with the options I added to `OpenRecordset` – HansUp Jul 23 '15 at 18:15
  • Ok, going to update the code with the other recordset loop around it. Still getting an error when I go to add a new record. – user3608243 Jul 23 '15 at 18:44
  • What is the error message and which line triggers it? Does the error occur when you run just the code I offered, or does it appear only when you use my code with the rest of what you added to your revised question? – HansUp Jul 23 '15 at 20:11
  • Error 3219: Invalid Operation. It's being triggered at the line .AddNew in the With rs loop. It's occurring when I run the code I reposted in my original post. – user3608243 Jul 24 '15 at 12:01
  • When I change: Set rs = .OpenRecordset(dbOpenDynaset, dbSeeChanges) to Set rs = .OpenRecordset(dbOpenSnapshot, dbSeeChanges), I get this error: Error 3251: Operation is not supported for this type of object. Not sure if this helps. – user3608243 Jul 24 '15 at 12:41
  • 1
    *dbOpenSnapshot* will give you a read-only recordset, so you don't want that. If you locate that *Notification* table in the Navigation pane, and open in Datasheet View from there, can you manually add a record? – HansUp Jul 24 '15 at 13:11
  • Gotcha. I have it switched back to dbOpenDynaset. I can enter records freely in the table in the db itself, but it still gives the 3219 error when running the code. Been trying to debug what's going on. It recognizes the fact that there are three parameters. And the If statement with .BOF and .EOF both return true fine, but as soon as it hits the .AddNew line it's like it almost doesn't recognize rs as a recordset. – user3608243 Jul 24 '15 at 13:21
  • Did my code work correctly when you tested it by itself? In other words, did it work before you integrated it with your other code? – HansUp Jul 24 '15 at 13:29
  • No, it kept stopping at the .AddNew line. I know the recordset rst code works. Could it be the Set rs = .OpenRecordset(dbOpenDynaset, dbSeeChanges) line? I understand why it has to be dbOpenDynaset and dbSeeChanges. One thing I was curious about was the lack of the first parameter where normally I'd put in the string variable for the needed SQL statement. Given what you have here, I'm guessing it pulls the QueryDef parameters automatically? I've never used QueryDef's before so please forgive my ignorance. – user3608243 Jul 24 '15 at 13:51
  • 1
    I doubt `.OpenRecordset(dbOpenDynaset, dbSeeChanges)` is the problem. I tested that code with my own linked SQL Server table, and it worked as intended. I don't understand why it's not working for you, but I can't invest more time on this. I can delete this answer, you can revert your question to its original state, and then maybe someone else can figure out how to help you. – HansUp Jul 24 '15 at 14:19
  • I appreciate your time. Honestly I don't understand why it's not working either. 3219 is generally when the table is read only, but I'm able to write to it. I think your answer is awesome, I wouldn't delete it, especially if it helps someone else. – user3608243 Jul 24 '15 at 14:25
  • I've narrowed it down to the AssetID and the NotificationTypeID value types. Both are type Int in the SQL database. The difficulty lies with VBA. The linked table in Access sees these types as a Number, with the property of Long Integer. VBA has difficulty differentiating between the types. If the variables that hold the values from the current recordset are type Integer, there's an overflow error because the values are over 32k. If type Long or String, then when it comes time to create the recordset for comparing the values in the Notifications table, it throws a datatype mismatch. – user3608243 Jul 24 '15 at 18:29
0

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")
user3608243
  • 63
  • 6