Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    ScottishFi is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    28

    Creating multiple records from one form - do I use a subform?

    Hi there

    I'm trying to work out the best way to structure this -

    I have three tables:
    tblNames
    tblVisits
    tblVisitsTo

    'VisitsT' links to the other two by one-to-many relationships, as each 'Name' will get multiple 'Visits',and each visit is to multiple 'Names'.

    I want one data entry form for a Visit where I can enter all the Names who are visited, ideally using combo boxes, each creating a separate record in my VisitsTo table.

    Can this be done with a subform datasheet with combo boxes for selection or am I on completely the wrong track here? Any pointers would be hugely welcome before I spend hours messing about with bound/unbound controls..



    Many thanks, and apologies if I'm being a complete numpty. Be gentle with me!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Either,
    make subforms for the 1 person to enter multiple visits (or anything)
    or
    enter items on an unbound form and run a macro to run multiple append queries to add the records.

  3. #3
    ScottishFi is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    28
    Great, thanks for speedy reply. Re subform, do you mean multiple subforms on one parent form? One for each new record? I wondered if that was the way to go...

  4. #4
    ScottishFi is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    28

    Still trying to add multiple names to form for separate records

    I'm completely lost, thanks for the pointers but I'm struggling. Have tried lots of things and getting nowhere. Have attached the DB here, if anyone can give me an idea where to start with this I'd be hugely grateful.

    I have frmNewVisits for the user to create a new visit which saves to table Visits. I need a subform on there (or separate form) where they can select multiple clients for each visit from combo boxes (names from table Clients) and enter details which will save to table VisitsToClients, creating a new record each time. I started a continuous form thinking that might work as a subform but will that work for data entry for multiple entries? I didn't really know where to start with the previous suggestions sorry.

    Thanks again for readingVisitor Stats testing.zip

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you defined/described ( in a sentence or 2) what each table represents? That's a good place to start.
    What is VisitsTo?
    Can you describe to readers in plain English what is the underlying business that this database is intended to support?

    A Person Visits.....(the Doctor, the market, the car dealer....???)

    You will get more focused responses when your issue/problem/opportunity is clearly communicated.

    Think of the post from the point of view of the reader:
    -we don't know you or your environment (you did say you were new to (the forum, Access, database, programming????)
    -we don't know the subject matter involved, nor the business you are dealing with
    -we only know what you tell us

    Good luck with your project.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    See if this will do what you want.
    It uses side-by-side listboxes to add/delete the assignments. Each assignment would be a record in the link table (your tblvisitsTo)
    Last edited by davegri; 06-11-2018 at 03:19 PM. Reason: add

  7. #7
    ScottishFi is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    28
    Ah OK, sorry. I was leaning towards not being a pest with too much detail but I take your point, clarity is more helpful. I'm not completely new to Access but I don't know VBA and struggle a bit with complicated forms.

    So this is a record of visits to folk who are in homes and I need to record who is visited each time and the details of their visit. They are visited in a group so the most efficient way of entering the data is to be able to enter all the names for a single visit via one form to save to a joining table that holds a new record each time a client is visited. But I don't know how to create the form.

    I'll have a look at the code that davegri has posted, thanks very much! Hopefully that will give me some pointers.

    Thanks for your patience

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was leaning towards not being a pest with too much detail
    You are far, far away from that - still. Actually, what could limit responses is when getting the needed details is like pulling teeth.

    Your table names are not that descriptive - unless they don't really represent your db, in which case, that's often another problem in posts (examples don't reflect reality). Since both the visited (are these patients? residents? inmates?) and the visitors likely have names, tblNames doesn't provide much info. Take another stab at explaining the "business" if you are looking for design advice. You say you're not new to Access, but I have to say I don't agree with your table designs. F'rinstance,
    - visit data doesn't belong in Client records.
    - actionID PK belongs in VisitsToClients.VisitAction as a FK, not the text value [VisitAction] from tblVisitAction - unless maybe you are going to employ table relationships with cascading updates.
    - you name the same things differently from one table to another - will confuse you sooner or later
    - frmVisitedNew will not work because it's missing one or more key fields. This suggests you designed the form before the query, or you didn't test the query first.

    Respectfully, maybe you should research normalization, or at least ensure you understand Entity versus Attribute. You seem to have made some basic design errors.
    Last edited by Micron; 06-11-2018 at 06:35 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    ScottishFi is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    28
    Oh god, I'm sorry you are absolutely right - what I posted was nonsense. Should have waited until my brain was less fried. The DB I posted has the tables as I was given them, with no link and the user was updating the data in the tables separately, directly into the tables. That's what I trying to help them fix. Basically I'm trying to use the 'VisitstoClients' table to create a link between the tables 'Visits' and the table 'Clients' so she only puts the data in once, as a DB should work! I had taken all the visit data out of the clients table as of course it shouldn't be there.

    It's a couple of people who go and visit a range of people and they need data to show who was visited, when and why. I wanted a form which will create a new 'Visit' (easy enough) but then another form or subform or whatever's best to select the clients from a combo and enter the visit action and comments which will go into the VisitstoClient table. But there will be multiple people visited each time, that's what's causing me problems about the best way to approach it. I'm toying with a button on the new visit form which pops up a dialog box and they can just open it multiple times until they've entered all the clients for that visit.

    I'll have a look at the links people have posted and see if I can make sense, I just wanted to apologise as I can see now why you wondered what I was on about and am feeling bad about wasting your time...

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You didn't waste anyone's time if you learned something. Better yet, you've acknowledge the learning. I suggest that you clearly explain what process, activities or business effort that the db needs to support. Forget everything about HOW you see this happening, which is what you are focused on now. Deal with the details and be explicit and reveal as much as possible if there are privacy constraints. At this point I can't tell if the multiple visitors visit one person at a time or if there can be multiple "visitees" during 1 visit. This could be a very important design consideration, along with others that you're not focused on. Maybe have someone review your expose' for clarity before you post it. You could do this after reviewing design concepts if you prefer, and include details of your proposed tables design. However if we don't clearly understand the process it diminishes the accuracy of the advice given.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to micron's comments, you are asking about a form, but we know very little about what business you are trying to support. Perhaps you could describe " the business" in 4-5 lines of plain English--just as you would tell a 10 year old who knows nothing of database, nor you nor your environment.

    You are not wasting anyone's time. We are advising you that clarity of communication is important. And you will get more focused responses once readers understand your issue in context.

    Good luck with your project ---we are here to help.

  12. #12
    ScottishFi is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    28
    Hi again, sorry for the delay. I'm not sure how I'm failing to communicate effectively, other than accidentally posting the wrong version of my DB and being stressed. Will try again.

    This is for an organisation that visits people who are in detention. Between one and five people do the visiting but I don't need to record any more than the group size for that. They will visit multiple people on each visit. I have three relevant tables:

    tblClients are the individuals who receive the visits
    tblVisits is a record of the visit dates
    tblVisitstoClients links the two, creating a new VisitRecord for each individual within each visit.

    What I need is a data entry method where the user can create the new visit (date and no of visitors) then open up a screen where they enter all the individuals visited each time which will create several new records in tblVisitstoClients. But I need to use a combobox for the client names for selection rather than them being retyped each time. I couldn't decide if a subform would work for that but have got part way with a NewVisit form, using a command button which saves the visit, creating the new VisitID from the autonumber field, then opening a NewVisitRecord form to enter the clients one by one. The logic works in my head but I can't get past an error message 'You cannot add or change a record because a related record is required in table Clients'. I'm sure it's something really obvious, but I can't see what's wrong at all. I couldn't get the VisitID value to transfer either.

    I hope this makes more sense, I'm teaching myself Access through trial and error and have lost hours to this stuff. I''ve attached the up-to-date version of my database here which should make a lot more sense than the last one. Thanks again for taking the time to help. I'm hugely conscious of just how little I know about Access!

    Visitor Stats testing.zip

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    In post #6 I provided a db to match members and events, with a dropdown for the member, then showing all available events in a list box.
    This version has a dropdown for the event, and shows all members in the listbox available to be assigned to the event.
    Think of members as clients, and events as visits.
    If this does what you want, and you are unable to change the nomenclature from members/events to clients/visits, let me know and I can to that.

  14. #14
    ScottishFi is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    28
    I saw this, thanks davegri. I don't think I can adapt it to exactly what I need though because I need to be able to enter additional fields alongside the names for each visit. There's an action and a comment linked to each name. Am I reading that correctly? Thanks so much for replying though, it's a nifty layout if i had just wanted to enter the names.

  15. #15
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    There's an action and a comment linked to each name.
    Can you post a relationship jpg so that we can see what your table setup is and where you plan to put the additional fields?
    Perhaps the example db could be adapted so that a double click on a name in the right listbox would open a popup form to enter additional data for that name...
    Last edited by davegri; 06-14-2018 at 03:38 PM. Reason: suggestion

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

Similar Threads

  1. Replies: 2
    Last Post: 05-29-2018, 07:39 PM
  2. Replies: 8
    Last Post: 05-18-2018, 12:38 PM
  3. Replies: 1
    Last Post: 01-10-2013, 08:33 AM
  4. Replies: 3
    Last Post: 10-18-2012, 02:25 PM
  5. Creating multiple records from a single form
    By secretary in forum Forms
    Replies: 8
    Last Post: 07-18-2011, 04:03 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