Add button on sub form

[Sparks54]

Kind of new at this game. I'm working on a form that lists pieces of equipment. That is the main form. The sub form has the daily inspection information and an add new record button on it. The sub sub form has the inspection items in a data sheet, 14 to be exact. What I would like to do is when the add record button on-click event is triggered, a new record is created on the sub form and the date field is updated to today's date. Then the 14 check items get added to the sub sub form. It all works until I try to add the second of the 14 checks. I've tried all kinds of things but the right way to do it escapes me. :banghead: I'm pretty sure the tables are correct with the relationships in place. All forms are query driven.
Any thoughts would be wonderful.
Thanks in advance,
Sparks54

 

[Pat Hartman]

Please post what you have so we don't have to guess what is wrong. If you can post the database, all the better. Otherwise, post the code and any queries used by the code.

 

[Ranman256]

if you have a table of the 14 checks, the user would click the button and it would run an append query ,adding the 14 to the table the sub form is using. The date field would have a default = date()

 

[Sparks54]

Here is the query

SELECT CheckItemDetails.CheckID, CheckItemDetails.ItemID, CheckItems.ItemName, CheckItems.ItemDescription, CheckItemDetails.Check
FROM CheckItems INNER JOIN ([Check] INNER JOIN CheckItemDetails ON Check.CheckID = CheckItemDetails.CheckID) ON CheckItems.ItemID = CheckItemDetails.ItemID;

Here is the code the "Add Record" button executes.

Private Sub AddNewSubRecord_Click()
DoCmd.GoToRecord , , acNewRec
Me!CheckDate.Value = Date
RunCommand acCmdSaveRecord
Me![CheckItemDetail subform].Form!ItemID.Value = 1
' works to this poimt
' what follows is me trying out different things
Forms!TowMotor.SetFocus
Forms![TowMotor].[DeptID].SetFocus
Forms![TowMotor].[towCheck subform].SetFocus
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acNewRec
Me![CheckItemDetail subform].Form!ItemID.Value = 2
'DoCmd.RunCommand acCmdSaveRecord
'Me!Form!ItemID.Value = 2

'Me![CheckItemDetail subform].Form!ItemID.Value = 1
'DoCmd.GoToRecord , , acNewRec

End Sub

Should have included in the first post. Sorry

 

[Sparks54]

Just tried the append query that was suggested. Get key violation error. Checked indexes and field type and can't seem to find anything wrong. Like the idea though.

 

[Pat Hartman]

If you have two levels of data to add, add the first one using DAO so that you can easily retrieve the autonumber PK. Then for the lowest level, you would run an append query that selects the 14 rows from the definition table and appends them to the inspections table. This query will need a FK argument that gets populated from the DAO code after the record is appended.

It might help if we saw your database.

 

[Sparks54]

Solved see below
Private Sub AddNewSubRecord_Click()
DoCmd.GoToRecord , , acNewRec
Me!CheckDate.Value = Date
RunCommand acCmdSaveRecord

Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from CheckItemDetails")
rec.AddNew
rec("CheckID") = Me.CheckID
rec("ItemID") = 1
rec.Update
rec.AddNew
rec("CheckID") = Me.CheckID
rec("ItemID") = 2
rec.Update