INSERT INTO: two queries, nearly identical, only one works

Question

UPDATED I am building a key inventory management database, and have built an INSERT INTO query to log Key-OUT transactions. I have tried building a Key-IN query to do virtually the same thing, but I am getting a key violation.

The following SQL works:

INSERT INTO KeyTransactions (Request, Door, Quantity) 
SELECT KeyRequests.RequestID, KeyRequests.Door, KeyRequests.Quantity
FROM KeyRequests
WHERE ((KeyRequests.RequestID)=4);

But this does not:

INSERT INTO KeyTransactions (Request, Door, Quantity) 
SELECT KeyReturns.ReturnID, KeyReturns.Door, KeyReturns.Quantity
FROM KeyReturns
WHERE ((KeyReturns.ReturnID)=2);

It should be noted: both run fine as a simple Query, only the second when run as an Update Query renders the key violation error.

For the record, the following was the original post, before I narrowed down the culprit:

I've been building MS Access application to track keys inventory, and I am trying to build the "inventory IN" function. My "inventory OUT" function works great, but when I attempt to replicate almost the exact same procedure, but with a slight twist, I get nothing. No errors, no inserted records, no discernible behavior of any kind, which makes debugging or researching my answer very difficult.

For reference, here is the "inventory IN" sql that works:

sqlDoorToMark = "SELECT Spaces_Table.Door, Keys_Table.Mark FROM Keys_Table INNER JOIN Spaces_Table ON Keys_Table.[ID] = Spaces_Table.[AssignedKey] WHERE Spaces_Table.[Door] = " & Me.Door & ";"
Set rs = myDB.OpenRecordset(sqlDoorToMark)
sqlIssuedInventoryUpdate = "INSERT INTO IssuedInventory (RequestID, Door, KeyMark, Quantity, IssueDate) VALUES (" & Me.RequestID & ", " & Me.Door & ", '" & rs!mark & "', " & Me.Quantity & ", Date());"
myDB.Execute sqlIssuedInventoryUpdate

Obviously there is more code around this. This is called after a drop-down box is updated. Works perfect. Love it.

The following is the entirety of the "inventory IN" code, called with the click of a Submit button:

Private Sub Submit_btn_Click()
Dim myDB As Database
Dim rs As DAO.Recordset

On Error Resume Next
On Error GoTo PROC_ERR

Set myDB = CurrentDb

sqlGetReturnValues = "SELECT KeyReturns.ReturnID, Keys_Table.Mark, KeyReturns.Quantity FROM KeyReturns INNER JOIN Keys_Table ON KeyReturns.Mark = Keys_Table.ID;"
Set rs = myDB.OpenRecordset(sqlGetReturnValues)

sqlInsertReturn = "INSERT INTO IssuedInventory (RequestID, Door, KeyMark, Quantity, IssueDate) VALUES (" & rs!ReturnID & ", '0000', '" & rs!mark & "', " & rs!Quantity & ", Date());"
myDB.Execute sqlInsertReturn

Set rs = Nothing
Set myDB = Nothing

PROC_EXIT:
    Exit Sub

PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT
End Sub

Running Debugger, I can roll through each line and monitor the values that come through. Everything appears fine. As far as I can tell, the .Execute sqlInsertReturn command simply just doesn't do anything here.

Can you see something I'm not seeing?

In `the "inventory IN" sql that works` you used `Me.` while in the other code `rs!`... I do not really understand the latter syntax, but might be the cause... — Usagi Miyamoto, Oct 24 '17 at 02:27
[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) — Andre, Oct 24 '17 at 06:22
rs! is the DAO RecordSet variable that contains the data I queried in the SELECT statement. It's getting the values directly from a query, rather than from the form objects as in the first code section (I'm learning as I go, so some of the older stuff I wrote differently). In any case, when Debugging, I can see the values of these variable and they appear fine either way, so I don't get why this would be the problem... I'll try it anyway... — JoshuaM, Oct 24 '17 at 17:29
Thanks @Andre, I just tried that and the SQL works fine. The resulting query pulls up exactly the info I want. INSERT INTO still does nothing, and provides no error information. — JoshuaM, Oct 24 '17 at 22:52

score 1 · Answer 1 · answered Oct 24 '17 at 05:19

1

The second parameter of the first method, the one that works, doesn't not use single quotes. This leads me to believe that Door must be a numeric value and not a string value.

INSERT INTO IssuedInventory (RequestID, Door, KeyMark, Quantity, IssueDate) VALUES (    ,       ,''  ,    ,  Date());

INSERT INTO IssuedInventory (RequestID, Door, KeyMark, Quantity, IssueDate) VALUES (    ,'0000', ''  ,    , Date());

I recommend creating a subroutine specifically for inserting the new record.

Sub InsertDoor(lRequestID As Long, lDoor As Long, sKeyMark As String, sQuantity As Single, dIssueDate As Date)
    On Error GoTo PROC_ERR
    CurrentDb.Execute "INSERT INTO IssuedInventory (RequestID, Door, KeyMark, Quantity, IssueDate) " & _
                      "VALUES (" & lRequestID & ", lDoor, '" & sKeyMark & "', " & sQuantity & ", dIssueDate);"
PROC_EXIT:
    Exit Sub

PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT
End Sub

answered Oct 24 '17 at 05:19

I just tried your solution above, and I somehow get the same result. The function works for the "inventory IN" but not "OUT". I then tried to use INSERT INTO... SELECT instead to eliminate any issued of variable type incompatibility. Again, it runs through the code as if it works properly, but actually inserts nothing into the table. Absolutely bizarre. – JoshuaM Oct 24 '17 at 22:29
One of the fields is probably Null. Consider using 'NZ(Control.Value, "")' or 'NZ(Control.Value, 0)' to replace the Null with the correct default datatype value. – Oct 24 '17 at 23:12
You should also 'Debug.Print ' the SQL to the immediate window and compare both results. – Oct 24 '17 at 23:14
I've been using the Debug.Print, actually, and after trying to run the resulting Query directly out of MS Access, I finally got an error I can work with. Apparently I'm hitting some sort of key violation, though I have no idea how. The only field in the destination table that has a "No Duplicates" is the Primary Key field, which I am not trying to overwrite (called "transID"). I'm continuing to investigate. I returned to the working SQL query and stripped it down to inserting just one field, Quantity, and now I'm getting the same error. Baffling. – JoshuaM Oct 25 '17 at 00:16

INSERT INTO: two queries, nearly identical, only one works

1 Answers1