Primary key violations

[Lol999]

Hi, I'm having some problems with a form and getting data into some tables.
I have a form, some of whose controls are bound, some are not, because I couldn't figure how to get a from to accept data entry so I opted for some vba and sql statements.

Okay, so what I'm trying to do is write to 3 tables, 1 bound to the form, 2 through sql commands executed in vba.

It enters into the bound form no problems but when I try to write to the other two tables with an Insert query I get the primary key violations which I do not understand at all.

Attached is a copy of the relationships table, the database and below is the code.

' btnAddItem_Click
'
'------------------------------------------------------------
Private Sub btnAddItem_Click()
'DoCmd.SetWarnings False
Dim strSQL1 As String
Dim strSQL2 As String
Dim Bookdate As Date
Dim UserName As String
Dim IDPart As Integer
Dim Comments As String
Dim IDType As Integer
Dim InspectionDate As Date
Dim InspectionDateDue As Date
InspectionDate = Format(Me.txtDate_Inspection, "dd/mm/yyyy")
InspectionDateDue = Format(Me.txtDate_InspectionDue, "dd/mm/yyyy")
Bookdate = Me.txtDate_Commissioned
IDPart = Me.ID_Product
IDType = Me.cboToolCategory1
Me.txtUser.SetFocus
UserName = Me.txtUser

On Error GoTo btnAddItem_Click_Err

    On Error Resume Next
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SetWarnings True
    strSQL1 = "INSERT INTO Tbl_Inspection (Date_Inspection, Date_InspectionDue, ID_Product) VALUES (#" & InspectionDate & "#,#" & InspectionDateDue & "#," & IDPart & ");"
    strSQL2 = "INSERT INTO Tbl_Current_Location (Date_Loaned, ID_Location_Type, Comments, User, ID_Product) VALUES (#" & Bookdate & "#," & IDType & ", 'Yard' ,'" & UserName & "'," & IDPart & ");"
DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
'Debug.Print InspectionDate
'Debug.Print InspectionDateDue
'Debug.Print IDPart
Me.cboToolCategory1 = ""
Me.txtPart_No = ""
Me.txtDetails = ""
Me.txtPurchase_Order = ""
Me.txtUser = ""
Me.txtDate_Commissioned.Value = ""
Me.txtDate_Inspection.Value = ""
Me.txtDate_InspectionDue.Value = ""
Me.cboToolCategory1.SetFocus
Me.btnAddItem.Enabled = False
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
            End If


btnAddItem_Click_Exit:
    Exit Sub

btnAddItem_Click_Err:
    MsgBox Error$
    Resume btnAddItem_Click_Exit

End Sub

 

[pbaldy]

Worked for me, but I'd use this:

http://www.baldyweb.com/ImmediateWindow.htm

to get the SQL and test in a new query to see if you get a better error message.

 

[Lol999]

It worked and actually wrote to the tables?

 

[Lol999]

I tried printing the strings to the immediate window and got these results:
INSERT INTO Tbl_Inspection (Date_Inspection, Date_InspectionDue, ID_Product) VALUES (#09/04/2018#,#09/04/2018#,50);

INSERT INTO Tbl_Current_Location (Date_Loaned, ID_Location_Type, Comments, User, ID_Product) VALUES (#09/04/2018#,3, 'Yard' ,'PAUL',50);

I'm not sure about the # symbols but I manually entered the data into the tables and all was well so I'm confused here.

 

[The_Doc_Man]

Do you not know the meaning of the octothorpe (#)? Well, then, I'll tell you!

The syntax of literal values in SQL and anything that might eventually make its way to SQL is that number need no delimiters; text needs paired quotes; and dates need paired octothorpes. Other things exist that aren't paired, such as hexadecimal constants that are proceeded by &H. I'll leave up to you to look for other literal "flags" that must be used in SQL to clarify a constant's interpretation.

 

[pbaldy]

You feel okay Doc? That's a short post for you!

By the way, just passed you in the grandkid department.

 

[Lol999]

Interestingly 9/4/18 is going in the table as 4/9/18 which is most infuriating!

 

[Lol999]

I ran the string queries nd despite the date going back to front they wrote just fine so I don't understand this Primary key violation problem.

 

[Lol999]

Further investigation reveals it is the code for strSQL2 that is causing the problems, if run in isolation strSQL1 works fine.
is it because the child table to Tbl_Product is Tbl_Current_Location that strSQL1 works but strSQL2 doesn't?

Do I need to change the relationship before running strSQL2?

 

[pbaldy]

What is the SQL when it fails? It would have to be passing a valid ID_Product.

 

[Lol999]

Hi Paul, just done a dummy run and here is the debug.print for both strings, they are showing the exact same values for ID_Product:

INSERT INTO Tbl_Inspection (Date_Inspection, Date_InspectionDue, ID_Product) VALUES (#09/04/2018#,#09/04/2018#,53);

INSERT INTO Tbl_Current_Location (Date_Loaned, ID_Location_Type, Comments, User, ID_Product) VALUES (#09/04/2018#,3, 'Yard' ,'John',53);

I've run a separate query with both strings and both have written fine to their respective tables so I don't know what is going wrong.

 

[pbaldy]

That's the same SQL that threw the error? It would appear to have valid values.

 

[The_Doc_Man]

You feel okay Doc? That's a short post for you!

Yeah, but I got to use a big and somewhat rare word, so I'm OK.

lol999 - the PK violations have nothing to do with ID_product. According to the relation diagram from the first post, which puts little key symbols next to the respective PKs:

The PK for TBL_Inspection is something called ID_Pat_Test.

The PK for TBL_Current_Location is ID_Current_Location.

Your SQL inserts make NO reference to either field. Therefore, they will be zero or null (and I don't remember or care which of those two because I'll bet that neither of those values is valid in that context.)

 

[pbaldy]

The PK for TBL_Inspection is something called ID_Pat_Test.

The PK for TBL_Current_Location is ID_Current_Location.

Your SQL inserts make NO reference to either field. Therefore, they will be zero or null (and I don't remember or care which of those two because I'll bet that neither of those values is valid in that context.)

Those are both autonumber fields, so wouldn't need to be included in the SQL.

 

[The_Doc_Man]

OK, I didn't open the DB because of having been burned a couple of times. But the fields in question still aren't the PKs of the tables to which insertions are occurring. How often does an autonumber field create a key violation?

When the queries work when run separately but fail when run in sequence claiming a PK violation, and yet the code says they are based off the same value, unless there is a bizarre event interaction, then the error isn't about ID_Product (and IDPart is just a copy of ID_Product.)

The only other thing I can think of to do is put a breakpoint on each SQL execution and use the Locals window or Immediate window to examine the members of the VALUES lists to see what will be written.

 

[Lol999]

Solved it.
The reason was I had assigned the incorrect value to a variable to be entered into the FK link field between Tbl_Current_Location and Tbl_Location_Type.
One of the values being submitted was 4 and the range was 1-3, hence a Primary Key violation, not in Tbl_Current_Location but in Tbl_Location_Type!

Thanks everyone for your help.

Regards, Lol

 

[pbaldy]

Glad you got it sorted. I figured it was something like that, which is why I asked if the SQL in post 11 was throwing the error. That SQL has a 3 in it.

 

[Lol999]

Hi Paul I misunderstood your post, I was so frazzled as to why the "should work" wouldn't!

It was actually looking towards the Tbl_Location_Type and wondering if it was connected when I fell across the FK problem.

Bit aggravating but at least it got sorted