Results 1 to 8 of 8
  1. #1
    gigian is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    3

    Access form - Single row table and multirow table via one form.

    Hello,

    I am trying to achieve the following.
    Table 01
    Name
    Surname
    Invoice Number

    Table 02


    Data 01 (product name)
    Data 02 (product weight)
    Data 03 (product size)

    What i need to do is the following...
    One invoice can have more than one products on it, thus, more than one rows off DATA 1 / DATA 2 / DATA 3.
    So what i need to do is create the correct relationships so that, i can create a form that will accept a name/surname/invoice number and then the field data1/data2/data3 multiple times.

    And then to print via reports the invoice and all the corresponding product rows for that particular invoice.

    CAN SOMEONE PLEASE HELP.
    Thank you.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    both tables need a primary key - for table 01, this might be invoice number, but better to have a separate autonumber primary key. And table02 also needs a family key to link back to table 01

    Table 01
    InvPK autonumber
    Name
    Surname
    Invoice Number

    Table 02
    InvLinePK autonumber
    InvFK long
    Data 01 (product name)
    Data 02 (product weight)
    Data 03 (product size)

    then create two forms, one for table1 and one for table2

    form for table1 will be a single form, form for table2 will be a continuous (or multiple item) form or a datasheet

    in the form for table1, drag the form for table2 into the detail section.

    providing you have set the relationships in the relationships window, that is all you should need to do, otherwise in the the subfrom control for table2 set linkchild property to InvFK and the linkmaster property to InvPK

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also, "Name" is a reserved word and shouldn't be used for object names.

    Don't use spaces in object names.......

  4. #4
    gigian is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    3
    I've done what you said, but i still have a problem.
    Form1 is (simple) and Form2 (Multiple).
    When i embed Form2 into Form1 and try to use it i have the following problem.

    data input on form1 part are OK ... but when i create a new record on form1, it only resets data on form1 (data on form 2 are still there from the previous record).

    For Example,

    Record 01
    ----------
    Name1/Surname1/Invoice1
    Data01=1 / Data02=2 / Data03=3


    Record 02
    ----------
    Name2/Surname2/Invoice2
    Data01=1 / Data02=2 / Data03=3 (its the same as record 1, they are not reset so i can enter new values from scratch)

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not at all clear what you are doing - suggest look at some of the access templates - Northwind is a good all-rounder or perhaps one of the invoicing templates

  6. #6
    gigian is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    3
    Let me present an example.

    Person 1 details (Name/Surname) and Invoice Number are given into Table1 via Form1.
    Person 1 also purchases some items with details.
    Item1/Quanity1/Weight1
    Item2/Quanity2/Weight2
    Item3/Quanity3/Weight3

    When i create a new record i need to create a new entry like so:
    Person 2 details (Name/Surname) and Invoice Number that are given as a new record on table 1 via form 1
    BUT IN THIS CASE
    I need to add the items for person 2 this time. For example
    Person 2 purchases some items with details.
    Item4/Quanity4/Weight4
    Item5/Quanity5/Weight5

    My problem is that when i create new record on FORM 1, I can enter the new persons details and invoice number but the embeded form 2 still holds the details from person 1.
    Item1/Quanity1/Weight1
    Item2/Quanity2/Weight2
    Item3/Quanity3/Weight3

    What i need to do is when i add a new record in form one to have a new record in the embeded form 2 also.

    Is this clear ???

    Thank you.
    G

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    What i need to do is when i add a new record in form one to have a new record in the embeded form 2 also.
    if you follow the templates and/or the suggestion in my original post that is what it will do =- you just need to go to a new record in the main form

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It sounds like you didn't set the fields for the "Link Master field/Link Child field" between the main form and the sub form.

    Click image for larger version. 

Name:	Link1.jpg 
Views:	9 
Size:	94.0 KB 
ID:	25951

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

Similar Threads

  1. Replies: 1
    Last Post: 02-15-2016, 08:02 PM
  2. Replies: 3
    Last Post: 11-16-2011, 11:53 AM
  3. Replies: 4
    Last Post: 11-07-2011, 07:25 PM
  4. Replies: 1
    Last Post: 09-27-2011, 09:42 PM
  5. Replies: 1
    Last Post: 05-17-2011, 05:19 AM

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