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.
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.
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 SubAttachments
Last edited:
