Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-13-2018, 01:53 PM   #1
Sparks54
Newly Registered User
 
Join Date: Mar 2018
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Sparks54 is on a distinguished road
Add button on sub form

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. 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

Sparks54 is offline   Reply With Quote
Old 03-13-2018, 04:22 PM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,084
Thanks: 13
Thanked 1,340 Times in 1,279 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Add button on sub form

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 03-13-2018, 04:22 PM   #3
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,882
Thanks: 0
Thanked 629 Times in 614 Posts
Ranman256 will become famous soon enough
Re: Add button on sub form

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()


Last edited by Ranman256; 03-13-2018 at 04:28 PM.
Ranman256 is offline   Reply With Quote
Old 03-14-2018, 03:14 AM   #4
Sparks54
Newly Registered User
 
Join Date: Mar 2018
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Sparks54 is on a distinguished road
Re: Add button on sub form

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 is offline   Reply With Quote
Old 03-14-2018, 10:34 AM   #5
Sparks54
Newly Registered User
 
Join Date: Mar 2018
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Sparks54 is on a distinguished road
Re: Add button on sub form

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.
Sparks54 is offline   Reply With Quote
Old 03-14-2018, 03:53 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,084
Thanks: 13
Thanked 1,340 Times in 1,279 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Add button on sub form

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-04-2018, 12:52 PM   #7
Sparks54
Newly Registered User
 
Join Date: Mar 2018
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Sparks54 is on a distinguished road
Re: Add button on sub form

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


Sparks54 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Button to click button on another form Adriano85 Macros 0 02-22-2013 02:55 AM
Question How i create a Save Button and A Clear Form Button? Amenofhis General 8 08-16-2012 09:51 AM
Button to open new form and submit data relating to the form the button is located on YNWA Forms 3 02-02-2010 06:08 AM
Delete record button - move from one form to the other button annietn34 General 7 04-11-2004 07:18 AM




All times are GMT -8. The time now is 06:41 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World