Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2012
    Posts
    4

    Unanswered: Creating a form which save data in more than one table

    Hi, I am new here and I am new in the Access database programming too, so please dont be mad at me if there is something similar to my problem... I searched but didnot find...

    So here's the situation.
    I have to make a form for a bookstore where a user can input information about their books. The tables and the connections between them are already made. In the "Products" table is stored more of the information about their books, but there are some pieces of info which cannot be stored there. For example, the cover of the book is presented in the "Products" table only by an ID of the cover (1 or 2 for paperback or hard cover) and the other info about the covers is stored in "Covers" table. So the form, along with the fields from the "Products" table must have a drop-down menu which gets a list of one column of the "Covers" table. But there is more - the products must belong to one or more sections. For example one book might belong to the "History" and to "Archeology" sections. So that's why there are another two tables - "section-list" and "section-products". The first one contains ID-s and names of all of the sections (i.e. 1, History; 2, Archeology; etc.). The second table contains info about which ID of a product belongs to which ID of a section (i.e. 1, 1; 1, 2; 2, 1; 3, 2; etc.). So, the form in this case should be able to present a list with all of the sections from the "section-list" where the user can select one or more for a book and info must be stored in "section-producs". There are other tables as well, but the problems is the same as this one, so if you can help me with this one, I might be able to resolve the others too!

    I tried to make one but I can make only a form which does not get any fields from other tables, and also I have no idea how to make one form to store data in more than one tables

    Here are the tables which I mentioned above:

    Products
    Products.ID-product (Identifier)
    Products.Title
    Products.ID-cover

    Cover
    Cover.ID-cover (Identifier)
    Cover.Name-cover

    Section-products
    Section-products.ID-Sections (Identifier)
    Section-products.ID-product
    Section-products.ID-Section

    Sections
    Sections.ID-Section (Identifier)
    Sections.Name-section

    Thanks in advance!!!

  2. #2
    Join Date
    Aug 2012
    Posts
    31
    Sounds like you're going to need to use unbound a form and write to the tables using VBA. I always go out of my way to avoid doing this, so someone else would be able to better direct you.

    If you don't get a good answer try a search for something like "dao recordsets in VBA" If you're not comfortable with VBA or simple SQL you may want to try to think of a why to break your processs up into steps so you can use bound forms.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd suggest you use sub forms
    id there are multiple sub forms use a tabbed dialogue with one form per tab
    trying to write to multiple tables from one form is a frightmare. you loose most of the advantages of using access. its can be doen, it has to be doen if you are wanting to leverage Access with a server backend but its not for the faint hearted
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    It actually sounds like the db is setup pretty well.

    Seems that you need to be using combo boxes (or list boxes) which are linked to the first table field holding the "id" but display the human friendly values contained in the other tables.

    The combo (or list) boxes do the linking to the other tables for you.

    Another path to try is using a query to link the tables together and linking the form to that.

    Or, use subforms.

    I have no problem using code to write data (in many cases, it's better to do it that way) but it does require some coding skills and should be a real need - because there is no point in "reinventing the wheel".
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    May 2007
    Posts
    38
    you can't really make a form manipulate multiple tables at the same time.

    let's say a form is bound to tableA, or a query based on tableA. tableA has links to other tables, so for each record in tableA you can see the related information from those other tables.

    But this form can only really change the records in tableA.


    ---
    to put it another way - if you are editing a "book" - why would you expect to see and manipulate all the "covers" at the same time. All you can manipulate is the single cover realted to the active book.

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    which brings us back to code (which is kinda like cheating) or subforms (which technically is separate forms).

    Subforms is certainly an easier step to take when you are new ...
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  7. #7
    Join Date
    Sep 2012
    Posts
    4
    OK, guys, many thanks for the replays. So it seems that I should use sub-forms. I will try this method.

    Edit:

    So, I made one table with subform. Now I figure out what you have told me, but this is not what I have to do. Or it is probably that I have not understand you correctly. I have to make a form where the user to be able to select of one or more sections for a product.

    So, I was able to make one main form where to enter the basic info for a product - title, author etc. and then I made the subform, but there if a product belongs to more than one sections it should be done more than one records which will be harder for a users who input data.

    It will be nice if you know a way how to make this one to work: "The combo (or list) boxes do the linking to the other tables..."

    Also how to make the main form to give me as a drop-down list the Cover.Name-cover column so the user to select one option from there, but not Products.ID-cover (which is linked to Cover.ID-cover)? Please, if it is not a big problem for you, be more specific - you click there, then write that and so on. Thanks
    Last edited by edel.bp; 10-02-12 at 11:33.

  8. #8
    Join Date
    Sep 2012
    Posts
    4
    Guys, I think I managed to make it! I googled hot to make subforms and there were some lessons which helped me.

    So now the only problem which a have to solve is to make the user to be able to choose from a drop down menu but not typing ID-s (the example above - to not to type ID-Cover, but to be able to choose from an option from Cover.Name-cover) and Access to write the corresponding ID. I found how to make the list to appear, but it cannot be pointed anything from it. I have to enter the ID in another field and Access fill from the list the corresponding name. Any ideas? 10x

  9. #9
    Join Date
    May 2007
    Posts
    38
    adding items to a drop-down is not quite the same thing as manuipyualting multiple forms

    anyway ... just look at the notinlist event. that's the one you need.

  10. #10
    Join Date
    Sep 2012
    Posts
    4
    Quote Originally Posted by gemma-the-husky View Post

    just look at the notinlist event. that's the one you need.
    10x! Would you be more specific or would you give me an example how to create such an option by the notinlist event? I googled about it - it give results, but couldn't find specific on my issue...

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    create a combo box, use the wizrd, and it will do most of the grunt work for you
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •