Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2022
    Posts
    8

    Table error when creating a form and subform

    Ive created a form with a subform, as i have a one to many situation, IE...customers who have several leaflets delivered to each of them. i complete the data for one customer and then put in several leaflets against that one customer.
    However when it goes back to the table its only completing one row with the customer on and the following rows only have the leaflets on.....is this a relationship problem ?
    Example of what is happening in the table;-

    This is how the table should look;-

    ID Venues Town Area Attraction Leaflet No Delivered Date Delivered Month Delivered Driver
    7 Admiral house Alby 1 Colchester Zoo Summer 50 08/01/2022 January Amanda Whiting
    9 Admiral house Alby 1 Amazona Zoo 25 08/01/2022 January
    10 Admiral house Alby 1 Bewilderwood 50 08/01/2022 January Amanda Whiting

    This is how it is looking

    Venues Town Area Attraction Leaflet No Delivered Date Delivered Month Delivered Driver
    Admiral house Alby 1







    Colchester Zoo Summer 50 08/01/2022 January Amanda Whiting



    Amazona Zoo 25 08/01/2022 January Amanda Whiting



    Bewilderwood 50 08/01/2022 January Amanda Whiting
    As you can see...the main form information is recording ion a separate line to the subform information

    regards


    Robert

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Looks like the construction of the Subform is wrong.
    Can you upload the db?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Firstly you should have at least two tables. One for customers and one for leaflets for customer. Likely another for all the leaflets.?

    Plus if you can change your username. Not reccomended to use an email address (if that is a valid email address).
    If you cannot change, ask an admin to do it for you.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Join Date
    Jan 2022
    Posts
    8
    Quote Originally Posted by mike60smart View Post
    Looks like the construction of the Subform is wrong.
    Can you upload the db?
    Database file attached(zipped)....the bottom two forms are the ones Ive tried using and the table is called Delivery Input Data

    regardss

    robert
    Attached Files Attached Files

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Robert

    You have based BOTH the Deliveries Main Form and the Subform on the 1 table "Delivery Input Data"

    This is all wrong
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Robert

    See the revised database attached.

    You need to Google Normalisation, Naming Objects and Relationships.

    I renamed all of the tables so that they had the prefix "tbl"
    I did the same for Forms (frm)

    I removed all of the Lookup Fields you had in all of your tables.
    I removed all spaces from Field Names
    I added a name for the Primary Key - Autonumber in each table. Instead of using ID in all of your tables you should always give the ID the same name as the tablename.

    See the attached Relationsships and MainForm/Subform Example of 1 way of doing this.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  7. #7
    Join Date
    Jan 2022
    Posts
    8
    Many thanks for your help, thats brilliant, and thanks for the tip on googling those particular subjects....its been over 20 years since i used ACCESS....so its a bit of a wake up call for me........once again THANK YOU

    REGARDS

    ROBERT

  8. #8
    Join Date
    Jan 2022
    Posts
    8
    Thanks again for your help with database....brilliant...the only thing i dont be able to do now is actaully see the actual data in a table....only the ID numbers....i need to see the actual names for the venues, attractions and area numbers in a table or a query, so i can create a report. Sorry to be cheeky
    regards

    robert

  9. #9
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Robert

    You should not be allowing anyone to look at data in tables.

    To see the data you would either create a query or a Report based on a query.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  10. #10
    Join Date
    Jan 2022
    Posts
    8
    Quote Originally Posted by mike60smart View Post
    hi robert

    you should not be allowing anyone to look at data in tables.

    To see the data you would either create a query or a report based on a query.

    many thanks for assistance

    robert

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Be careful with queries, as they can still change the data if not careful.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 2
    Last Post: 04-19-2017, 08:09 AM
  2. Creating a subform for input to a table
    By MacMan in forum Forms
    Replies: 5
    Last Post: 10-06-2015, 05:30 PM
  3. Error in creating subform control source
    By Lou_Reed in forum Access
    Replies: 14
    Last Post: 05-22-2015, 09:20 AM
  4. Creating a subform which displays values from a table
    By marcvanderpeet12 in forum Access
    Replies: 1
    Last Post: 03-03-2014, 01:04 PM
  5. Replies: 1
    Last Post: 07-22-2013, 12:00 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