Results 1 to 13 of 13
  1. #1
    EricEricsson is offline Novice
    Windows 10 Access 2019
    Join Date
    Jun 2023
    Posts
    5

    Create add 1 parent record and several child records with Create button

    Hi everyone!



    Please help me with your Access and VBA knowledge.

    Situation:
    I have 2 tables:
    • Parent Table - "Shops". 2 columns: "Shop_Id" (Autogenerated Number), "Shop_Name" (Text)
    • Child Table - "Sales". 4 columns: "Sale_Id" (Autogenerated Number), "Shop" (Number - Foreign key to Shop_Id), "Year" (Number), "Value" (Number).

    I.e. for each shop I have many Sales.

    I want to create a button that will:
    • Add new Shop record,
    • Add several Sales records. Number of added Sales records is equal to number of different "Year" values.
    • For each of these Sales records will:
      • relate it with parent Shop record, i.e. put Shop_Id of created Shop record into "Shop" value,
      • put 0 as "Value" value.

    • Opens particular form where I want to display both parent and child records (in subform).


    Problems:
    1. I don't know how to create child records and give them Shop_Id of parent record. This Shop_Id is auto-generated. I don't know how to retrieve this number.
    2. I don't know how to dynamically calculate amount of child records I need to create.
    3. I don't know how to make a loop that will create as many records as needed.


    Will be super grateful for your help and / or guidance about which topics / methods to look at.
    Thank you in advance!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This will show you how to do #1, or at least the retrieval of the key value:

    http://allenbrowne.com/ser-57.html

    Either executing SQL or using the AddNew method of a recordset within a For/Next loop will let you create the requisite number of child records. I don't understand how you expect to come up with that number, so perhaps more info on that will help.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    More than one way to accomplish.

    Can use VBA to run SQL statements.

    CurrentDb.Execute "INSERT INTO Shops ..."

    Then autonumber ID primary key for that new parent record can be retrieved with:

    x = CurrentDb.OpenRecordset("SELECT @@IDENTITY")(0)

    Use that value to add multiple related child records. Really need more info about data to advise further. Where would different 'year' values come from? If you don't know how many records are needed, I certainly wouldn't.

    Open form/subform with main form filtered for this new ShopID.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    EricEricsson is offline Novice
    Windows 10 Access 2019
    Join Date
    Jun 2023
    Posts
    5
    Thank you!

    Regarding the number... I tried to split bigger problem into several smaller ones.
    Overall I'm trying to create database and user interface so that different users could create / open / update information about Shops and their Sales. To do that they should follow one of several cases:

    1. Case 1. To open a table / list of all shops with their sales by years. Shops should be in rows, Years should be in columns, value - in cells.
    2. Case 2. By a double click on row for one Shop - you can open it and see all the sales in a table view (years by columns). Important thing here - I want to be able to update those Savings in this subform.
    3. Case 3 (focus of this thread) By clicking some additional button - to open form for adding a new shop with subform with Sales per year (ideally - an editable table view).

    Now let's imagine I want this database to live longer than I expected. And at some point I need to add another year. And I want this new year to appear in Cases 1,2,3.
    I thought that it can be done by additional library-table - "Years". And records "Sales" will refer to it. So if I need, I simply will add one more Year here and my User interface should start displaying it in Cases 1,2,3.

    So If we return to Case 3. Let's imagine we somehow calculated number of years. Maybe by using arrays are somehow else.
    The problem is that I want to:
    -- (Problem 1 - related with adding variable number of records) Dynamically create number of "Sales" records and relate them with parent Shop record,
    -- (Problem 2 - related with displaying a variable number of columns) Display them in subform / table / list with number of columns (years) depending on calculated number of years.

    Hope that brings more clarity. The overall task looks a bit tricky for me now. That's why I'm trying to solve problems one-by-one.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What's tripping you up is having years as columns (fields). In a properly normalized database you don't do this type of thing. Maybe a intro into databases would help:

    http://allenbrowne.com/casu-22.html

    Number 3, normalizing data, is probably going to help you. Start with a properly normalized database, then the forms and reports come much easier.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Years in columns is not normalized data structure. You hit on the core issue - adding fields for new years and having to modify table, queries, forms, reports, code.

    Suggest you re-think this schema.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    EricEricsson is offline Novice
    Windows 10 Access 2019
    Join Date
    Jun 2023
    Posts
    5
    Thanks for a great link!

    Quote Originally Posted by pbaldy View Post
    What's tripping you up is having years as columns (fields). In a properly normalized database you don't do this type of thing. Maybe a intro into databases would help:

    http://allenbrowne.com/casu-22.html

    Number 3, normalizing data, is probably going to help you. Start with a properly normalized database, then the forms and reports come much easier.
    I agree. But I don't want to store data like that. I only want to show it like that in User interface. To store it I created "Sales" table that keeps all the data as a set of records with reference to Shop and Year.
    The narrative behind that is because most of my users are people from financial departments. Most of the time they work with Excel sheets and tables. And for them it's much easier if they see yearly data in columns rather that show them as it is stored in database. Another argument is pure usability: in most cases if you update the data you simply copy several cells from Excel / table and copy somewhere else. So ideally I would like it to be possible for users to update data like that.

    Hopefully it makes sense.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    A CROSSTAB query can display data as you describe. However, this is not an editable query so cannot do data entry with it.

    To pull/save normalized data to a non-normalized form for data entry/edit would require using UNBOUND form and lots of code to pull/save data.

    Suggest your people can adapt to normal data entry/edit interface and look to a report for non-normalized display.

    Review https://www.fmsinc.com/MicrosoftAcce...ort/index.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    ChrisO has an updateable crosstab form example here:

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

    I've never used it, but his stuff is very solid.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    EricEricsson is offline Novice
    Windows 10 Access 2019
    Join Date
    Jun 2023
    Posts
    5
    Quote Originally Posted by June7 View Post
    A CROSSTAB query can display data as you describe. However, this is not an editable query so cannot do data entry with it.

    To pull/save normalized data to a non-normalized form for data entry/edit would require using UNBOUND form and lots of code to pull/save data.

    Suggest your people can adapt to normal data entry/edit interface and look to a report for non-normalized display.

    Review https://www.fmsinc.com/MicrosoftAcce...ort/index.html
    Yes. I think that will be the choice if I don't find any other way. Just wanted to understand if there are other options.

  11. #11
    EricEricsson is offline Novice
    Windows 10 Access 2019
    Join Date
    Jun 2023
    Posts
    5
    Thank you! Will look into that.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 10 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Also, can easily display a CROSSTAB query by setting a subform container SourceObject property to query. Keep in mind forms and reports are limited to 22 inches in width and length.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 20
    Last Post: 05-13-2020, 02:49 PM
  2. Replies: 1
    Last Post: 04-17-2018, 09:38 AM
  3. Replies: 5
    Last Post: 05-10-2014, 12:25 PM
  4. Replies: 4
    Last Post: 09-13-2013, 05:26 PM
  5. Delete child records with parent record
    By jpkeller55 in forum Access
    Replies: 8
    Last Post: 09-07-2010, 08:52 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