Results 1 to 10 of 10
  1. #1
    NickCarroll is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    8

    Create a record in a 2nd table when I create record in the 1st.

    I have two tables: tblJobs (this is the parent table) and tblWagesTable

    [tblJobs]
    The primary key of the parent table [tblJobs] is a unique auto-number field called jbsID

    [tblWagesTable]
    The primary key of the child table [tblWagesTable] is a unique auto-number field called wgtID
    There's another field in the child table called wgtJobID - this is the field which connects child to parent.



    When I create a new record in the parent table [tblJobs], I want to automatically create a 'placeholder' record in the child table [tblWagesTable] which the user will return to at a later stage to complete.
    The form I'm using to create the new record in [tblJobs] has a subform for [tblWagesTable].

    I've tried all sorts of bits of code to take the value of the primary key on the new record in the parent table and create a new record in the child table but had not joy.
    Can anyone help me with this?
    Many thanks.
    Click image for larger version. 

Name:	Capture.JPG 
Views:	21 
Size:	35.1 KB 
ID:	31204

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If you have created a relationship for the two tables that uses the tblJobs.jbsID and the tblWagesTable.wgtJobID fields and they are also the LinkMaster/ChildFields of your SubForm, then Access will fill in that value for you.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can put a 'save' button on the form. when user clicks it, it can run an append query to add your needed record.
    the user cannot leave the form without 'saving'.
    or
    make a subform that automatically produces the sub record upon the master record save. (a passive save rather than a user forced one)

    I think your tWages table design is wrong, it should be:
    wgtID
    wgtJobID
    wgtQty
    wgtDate
    wgtHrs
    wgtRate

  4. #4
    NickCarroll is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    8
    Hello

    I understand the basics of related tables and subforms and have dozens of them working as you describe.
    However, what I'm seeking to do here is something slightly different....
    When the user creates a record in the form of the parent table, I want a record to be created "automatically" in the subform (the child table) without the user doing anything so that for every record that exists in the parent table, at least one record will exist in the child table.

  5. #5
    NickCarroll is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    8
    Thanks for than Ranman - I was hoping for more specific guidance on how achieve this.

    With regard to the table design - why is it wrong?

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by NickCarroll View Post
    Hello

    I understand the basics of related tables and subforms and have dozens of them working as you describe.
    However, what I'm seeking to do here is something slightly different....
    When the user creates a record in the form of the parent table, I want a record to be created "automatically" in the subform (the child table) without the user doing anything so that for every record that exists in the parent table, at least one record will exist in the child table.
    Were you using the AfterInsert Event of the Main Form when attempting to create the New Record in the SubForm?

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    Quote Originally Posted by NickCarroll View Post
    With regard to the table design - why is it wrong?
    Because you have repeating fields/groups:
    wgtMoHrs
    wgtMoRate

    wgtTuHrs
    wgtTuRate

    wgtWeHrs
    wgtWeRate
    (etc)


    This table is designed like a spreadsheet, not a relational database table. (this actually has a name; it's called "Committing Spreadsheet")
    Your current table design violates 1st Normal Form.

    1st Normal Form Definition

    A database is in first normal form if it satisfies the following conditions:

    • Contains only atomic values
    • There are no repeating groups


    I agree with ranman256 on the structure:
    wgtID_PK (Autonumber)
    jbsID_FK (Number - Long Integer) / foreign key to table "tblJobs"
    wgtQty
    wgtDate
    wgtHrs
    wgtRate

  8. #8
    NickCarroll is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    8
    Hello Steve

    Thank you for your response. You probably guessed that I've no formal training in Db design (tho I've been creating applications [not in Ms Access] for my own company for 30 years now) so I'm grateful for your advice - tho I don't fully understand it!

    Because what you refer to as 'repeating groups' relate to 'days of the week', there's only ever going to be a maximum of 7 instances.
    What I'm trying to create is a table that shows the wages budget cost for a contract. Typically, our staff might work the same hours on Mon/Tue/Wed/Thu/Fri at the same rate of pay however, it's not unusual for people to work M/W/F only or for pay rates to be different on different days. In addition to recording the hours worked on different days of the week and the rates of pay for those shifts, the table also records the annual holiday entitlement for each person of this pay type and then goes on (via the query) to calculate the employees weekly pay, the payroll taxes, holiday pay and eventually a totally weekly contract cost.

    Whilst it may look like a spread-sheet in its current form, the table does exactly what it is intended to do in the application. That said, I'd be really interested to know how you'd make this scenario work in practice.....
    I know I could create a 'DayOfTheWeek_ID' and call the day of the week from another related table however I'm thinking doing this would massively complicate the overall design and would actually impede data entry by the people who use the application.

    Nick

    BTW: I like the idea of identifying the primary and foreign keys as you do. Whilst I obviously know my tables inside out, anyone stepping in to my shoes would be very much helped by this tweak. I'll be sure to include this in future tables.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Because MS Access SubForms do so much of the work for you in creating a new record, something like this code should take care of that issue for you.
    Code:
    Private Sub Form_AfterInsert()
    
    Dim ctlPrevious As Control
    Set ctlPrevious = Screen.PreviousControl
    
    Me.THE_NAME_OF_YOUR_SUB_FORM_CONTROL.SetFocus
    DoCmd.GoToRecord , , acNewRec
    '-- Dirty a field right here.
    ctlPrevious.SetFocus
    
    Set ctlPrevious = Nothing
    
    End Sub
    Edit: Actually, you may need to dirty one of the fields in the new record for it to take.
    Last edited by RuralGuy; 11-12-2017 at 09:56 AM. Reason: Added an edit

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by NickCarroll View Post
    Because what you refer to as 'repeating groups' relate to 'days of the week', there's only ever going to be a maximum of 7 instances.
    A spreadsheet it typically "short and wide".
    A relational database is typically "tall and narrow".

    I would guess that you also have a date field "WkBeginning"?

    Lets say that you have a job (the ID is 10) that began on Wed and ends on the following Wed. Your table looks like
    wgtID
    wgtJobID wgtJobDesc wgtQty wgtMoHrs wgtMoRate wgtTuHrs wgtTuRate wgtWeHrs wgtWeRate wgtThHrs wgtThRate wgtFrHrs wgtFrRate wgtSaHrs wgtSaRate wgtSuHrs wgtSuRate wgtSaRateWgBegin
    100 10 Not needed 1 8 25.00 8 25.00 8 25.00 10/15/2017
    154 10 is in tblJobs 1 8 25.00 8 25.00 10/22/2017


    A normalized table:
    wgtID wgtJobID wgtQty wgtDate wgtHrs wgtRate
    100 10 1 10/18/2017 8 25.00
    101 10 1 10/19/2017 8 25.00
    102 10 1 10/20/2017 8 25.00
    103 10 1 10/23/2017 8 25.00
    104 10 1 10/24/2017 8 25.00

    A query would look like:
    Code:
    SELECT [wgtHrs]*[wgtRate] AS TDailyAmt, tblWagesTable.wgtDate, tblWagesTable.wgtJobID_FK
    FROM tblWagesTable
    WHERE (((tblWagesTable.wgtDate) Between #10/18/2017# And #10/24/2017#) AND ((tblWagesTable.wgtJobID_FK)=10));

    A totals query to get the total cost would be
    Code:
    SELECT Sum([wgtHrs]*[wgtRate]) AS TDailyAmt, tblWagesTable.wgtJobID_FK
    FROM tblWagesTable
    WHERE tblWagesTable.wgtDate Between #10/18/2017# And #10/24/2017#
    GROUP BY tblWagesTable.wgtJobID_FK
    HAVING tblWagesTable.wgtJobID_FK = 10;
    Don't even want to think about what a query would look like with your design..


    Which table design do you think would be easier to calculate a total cost from? (Not sure how field "wgtQty" fits in )
    Now think about the job lasting 6 weeks.


    Quote Originally Posted by NickCarroll View Post
    Typically, our staff might work the same hours on Mon/Tue/Wed/Thu/Fri at the same rate of pay however, it's not unusual for people to work M/W/F only or for pay rates to be different on different days. In addition to recording the hours worked on different days of the week and the rates of pay for those shifts, the table also records the annual holiday entitlement for each person of this pay type and then goes on (via the query) to calculate the employees weekly pay, the payroll taxes, holiday pay and eventually a totally weekly contract cost.
    Maybe the table "tblWagesTable" need an "EmpID_FK" field also???

    Could also have another field for PayType - ST, OT, Holiday, etc.
    Would be easy to add another PayType with the normalized table. With your table(spreadsheet) design, adding another field would require changing the table, queries, form, report and code.


    Anyway.... that's the theory....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. create new record in other table
    By lockstock in forum Programming
    Replies: 2
    Last Post: 01-18-2016, 10:03 AM
  2. Can't create new record in a related table
    By acannon in forum Programming
    Replies: 8
    Last Post: 05-07-2015, 09:25 AM
  3. Create a form from table record
    By ande8698 in forum Forms
    Replies: 1
    Last Post: 04-24-2014, 09:40 AM
  4. Replies: 1
    Last Post: 11-20-2013, 10:04 AM
  5. Create one record for every field in another table
    By sailngsue in forum Programming
    Replies: 6
    Last Post: 09-27-2011, 03:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums