Page 1 of 4 1234 LastLast
Results 1 to 15 of 52
  1. #1
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43

    Add selected records from one table to another table

    First of all i want to salute all members!
    I have made a simpe database to input medical exams.
    As you Can see at the sample(mylab) One Patient has many exam orders and orders many exams, adde to the order_result table.Of course there are not only 19 exams but nearly 65.
    Whats the problem. Many times users add the same exam twice and spend a lot of time to find the exams from the dropdown list. My knolege is limmited just above the basics.
    So.. what i want to do. Create something like a form (probably from a query), from which the user can select exams and the selected exams will be added to the order_result table.


    I ve tried the append query .. but i end up with headache and no result.
    What i have in mind is like a form with all the exams (from table exams) with a selection box.
    Those who are selected will add as records to order_result table. I hope i was clear ....
    Thank you and have a nice day!!!!
    Attached Files Attached Files
    Last edited by TONYWALKER; 10-02-2017 at 01:46 PM.

  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
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    pbadly, thank you for the fast reply.
    I have seen your post before mine...
    Why you must put a numeric value?(raises question from the users . they prefer something more formal .. like "do you want to add the exams" y/n...) Its quite close to what i have in mind, but there is no link with the fk.(exam_order)
    Anyway thank you very much!!!!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The numeric value is often (normally?) hidden from users, but if you want to store a text value, feel free.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    pbaldy.... you are great! (...and i m a noob...)
    my knolege of vb is zero... but...(correct me if i do something wrong)
    1)First i must change the table names,
    2) add to to Order_result the field with the numeric
    3) to hide the value is easy (default=1)...or not?
    4) How can i link the selected exams with the exam_order?
    and... (don't laugh)... how i put the vb command?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    1) Yes, you'd change to your table/field names.
    2) Not sure I understand
    3) In the listbox? Yes, hide with a 0 column width
    4) Exam_Order doesn't appear to have a field for exams. Not sure if there should be one there or a related table to hold multiple exams ordered per "order"

    http://www.baldyweb.com/FirstVBA.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    2) order_result is my target table, in your target table -tblothertable (is the asked numeric field)
    4)The exam_order is one level up and linked with order_result with FK as you have probably see at relationships. The added exams must be under the id of exam_order ( one order to many exams- those i want to add)
    i ve made the changes (mylab2) "Item not found in this colection" is the message... ok solved... stupidity as usual(mylab4.zip is ok)
    I have one problem, how can i run this and the selected exams go "under' the Order_exams?(no FK, .... its empty) They are not linked with any Order_exam.
    And one question, is it possible to add a counter with the selected exams (depending on the exams selected)... i am asking too much ...???
    Attached Files Attached Files
    Last edited by TONYWALKER; 10-02-2017 at 02:42 PM.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The code doesn't actually care if the "other value" is text or numeric. If you need it to be you can test as you are. To associate with an exam, I'd either have the desired exam open so the code can get it from there, or you need the user to input it (obviously less desirable). Not sure how you want things to flow, but I'd either have this listbox on a form that listed the exam, or open it from another form where you can grab the ID.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    pbadly... i couldn't make it... i must do something wrong... the last six hours...

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post a db with your effort? Hard to know what's going wrong without seeing it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    Take a look at mylab4.zip... its how far i have gone.. You can find it on a older reply

  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
    That doesn't address how you want it to flow. If they enter on a stand-alone form, you need to get the exam from someplace. I don't know where that place should be.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    Quote Originally Posted by pbaldy View Post
    That doesn't address how you want it to flow. If they enter on a stand-alone form, you need to get the exam from someplace. I don't know where that place should be.
    pbbadly, What i want to do is use your code to add reocords to tbl order_results. I ve made the changes and the selected records added. I dont know to make it work so the selected records are linked with exam_order. Order_results takes fk from Exams (the selection i want to make), a fk from exam_order (the one missing)- many to many . Sorry to bother you, thank you for your time.Fell free to make changes, ot tell my how i could make it work. With great respect for time spend, Tony

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm trying to understand how you envision the process working. I'll make one up. Add this to the double-click event of the ID field in the exam order subform:

    DoCmd.OpenForm "frmOpenReport", , , , , , Me.ID

    That will open the form and pass the exam ID you double-clicked on. Add this line to the loop code you already had:

    rs!EX_ORDER_ID = Me.OpenArgs

    That will add that data to the table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    TestLab.zip

    You can see the form, to see how i imagine it. Selection works fine all i need is a button to add the selected records to the table Patient_order_exams, linked with patiend_order_ID. It think will help other users, its helpfull when you have quite a lot records to select from(scroll add)and faster for the user.

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 11-27-2014, 10:18 AM
  2. Replies: 3
    Last Post: 01-24-2013, 02:38 PM
  3. Replies: 14
    Last Post: 12-06-2012, 11:25 AM
  4. Replies: 6
    Last Post: 05-10-2012, 08:20 PM
  5. Replies: 2
    Last Post: 09-18-2010, 07:52 AM

Tags for this Thread

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