CODEWITHSUNDEEP
sqlms-access
JoshuaM
JoshuaM

Reputation: 43

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

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?

Upvotes: 0

Views: 64

Answers (1)

user6432984
user6432984

Reputation:

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

Upvotes: 1

Related Questions