CODEWITHSUNDEEP
sqlms-accessms-access-2007vba
N.Balauro
N.Balauro

Reputation: 151

Fail to Append due to Key Violations

This is my first time developing a database and first time using MS Access! I am trying to append 1 new record to a table via a form linked to a query. 

Private Sub cmdAdjustStock_Click()
'Declare Vars
Dim newqty As Long
Dim Qty As Control
Dim change As Control
Dim BoxType As Control
Dim sql As String

'Set form controls to vars
Set Qty = Forms!formMain!txtQty
Set change = Forms!formMain!txtQtyChange
Set BoxType = Forms!formMain!txtBoxType

'Arithmetic and SQL
newqty = Qty + change
sql = "INSERT INTO tblHistory (BoxType, QtyChange, NewQty) VALUES ('&BoxType&','&change&','&newqty&')"

MsgBox "New Quantity = " & newqty & ", Box Type = " & BoxType 'For Debugging
DoCmd.RunSQL sql

End Sub

"tblHistory" has the following fields: PID, logDate, BoxType, QtyChange, NewQty. All fields required. "logDate" default value = Date() and PID is autonumber. "tblHistory" currently has no records and this append would be the first!

"BoxType" is on the many end of a 1-many relationship to a table "tblBoxList" containing master list of BoxType (Primary) and their corresponding quantities.

My MsgBox displays correct quantity values and a BoxType from the form (ie. 'RMA-834') matching a Primary BoxType ('RMA-834') on the "tblBoxList".

After verifying the data via the MsgBox the Append fails due to 1 key violation. Im assuming this violation has to do with either the PID (which is an auto increment number) or the BoxType im passing from the form somehow isnt matching up to the primary in tblBoxList. Which is confusing because MsgBox is displaying a value that looks identical to the Primary.

FYI: Tables in this project are linked. I have the 3 tables in a "back-end" on the network. This is the "Front End" with just forms.

Upvotes: 1

Views: 1425

Answers (3)

HansUp
HansUp

Reputation: 97101

Inspect the INSERT statement you're asking Access to execute.

sql = "INSERT INTO tblHistory (BoxType, QtyChange, NewQty) VALUES ('&BoxType&','&change&','&newqty&')"
Debug.Print sql

Run the code and examine the INSERT statement in the Immediate window. You can go there with Ctrl+g.

And you can copy the statement text and paste it into SQL View of a new Access query for testing, which should help you diagnose the problem. Paste the statement text into your question if you need more help from us.

Also consider converting the INSERT to a parameter query. You can use that named query in your VBA code and supply the parameter values at run time.

Assuming you created and tested a parameter query similar to the one below, you named it qryHistoryAppend, and it includes pBoxType, pQtyChange, and pNewQty as the named parameters, you can open that query (QueryDef object) in VBA, supply the parameter values and execute it.

Const cstrQuery As String = "qryHistoryAppend"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs(cstrQuery)
qdf.Parameters("pBoxType") = Forms!formMain!txtBoxType
qdf.Parameters("pQtyChange") = Forms!formMain!txtQtyChange
qdf.Parameters("pNewQty") = Forms!formMain!txtQty
qdf.Execute dbFailOnError ' <- always use Execute instead of DoCmd.RunSQL

Note if that code is contained in formMain, you can reference the text boxes like Me!txtBoxType instead of Forms!formMain!txtBoxType

My idea for qryHistoryAppend looks like this. Adjust as needed.

INSERT INTO tblHistory (BoxType, QtyChange, NewQty)
VALUES (pBoxType, pQtyChange, pNewQty);

Upvotes: 0

Johnny Bones
Johnny Bones

Reputation: 8402

This isn't going to work:

sql = "INSERT INTO tblHistory (BoxType, QtyChange, NewQty) VALUES ('&BoxType&','&change&','&newqty&')"

Try changing it to:

sql = "INSERT INTO tblHistory (BoxType, QtyChange, NewQty) VALUES ('" & BoxType & "','" & change & "','" & newqty & "')"

Upvotes: 1

DAG
DAG

Reputation: 931

Based on the error message, it sounds like the VB code you show here is irrelevant. Open the database file in MS Access and make the query work within Access first.

Upvotes: 0

Related Questions