Results 1 to 6 of 6

Many to Many Issues

  1. #1
    chrisdogg is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    6

    Many to Many Issues

    Hello,



    I'm having a hard time setting up my form so that the users input writes to the database. Here's the situation, I'm creating a database where I'm track the employees that volunteer for events at work. When I'm in the form I want to be able to go to an employee and select one or more events to be associated with them. A screenshot of my join table is attached. Currently when I make a selection, the event ID displays in the employee table but does not write to my joint table "Employee_Events". Can someone tell me what I'm doing wrong? Hopefully I've given enough information. Any guidance would be greatly appreciated. Thanks
    Attached Thumbnails Attached Thumbnails Join.PNG  

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,095
    Although your Employee-Event relationship is many-to-many, what you are describing is actually a one-to-many part of it: One employee to many events.

    You could use a main form - sub form arrangement to do this, with the main form being the employee and the subform entering the events selected. The subform would have tblEmployee_Events as its recordsource.

    You could also use a multi-select list box to select the events, but you would then need to use a bit of VBA to append records to tblEmployee_Events.

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    3,981
    I agree with John - with the subform, hide or remove the Employee_ID control, the user does not need to see it. The field will be populated automatically if the subform control has the linkchild and link master properties populated and you have defined a one to many relationship (which you have). The change the control for the Events_ID field to a combobox with a rowsource of the tblEvents ID and Name fields, plus perhaps date and description if relevant to the user making a choice

  4. #4
    chrisdogg is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    6
    Thanks for the responses! I think I'm still doing something wrong though. Should I keep the relationship that I have in my initial post as is? Also, when I created my subform it's doing something funky where it's replacing an event name with a number. Then I made a change and im getting the screenshot attached. I'm feeling super slow at the moment. lolClick image for larger version. 

Name:	error.JPG 
Views:	13 
Size:	32.9 KB 
ID:	26989

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,095
    when I created my subform it's doing something funky where it's replacing an event name with a number.
    That's fairly normal, actually. You are probably selecting the event from a combo box, in which the first column is the Events_ID, but is hidden, and you see only see the event name. The combo box has events_ID (hidden column 1) as its bound column, and it is saving that numeric value to the table.

    Then I made a change and im getting the screenshot attached
    Nothing wrong there, either. The table tblEmployee_Events has Employee_ID+Events_ID as its PK, and since the PK must be unique (by definition), it means that you cannot assign any one employee to the same event more than once. Access is operating as it should, alerting you to a data entry error.

  6. #6
    chrisdogg is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    6
    Thanks for the explanation John_G. This should be my last question, how do I go about fixing my form so that the user doesn't see the message?

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

Similar Threads

  1. If Statment Issues
    By Wasp1 in forum Modules
    Replies: 3
    Last Post: 04-30-2014, 10:43 AM
  2. Subform issues
    By spqr in forum Forms
    Replies: 3
    Last Post: 10-14-2013, 07:53 AM
  3. DLookup issues...
    By Fish218 in forum Forms
    Replies: 2
    Last Post: 03-09-2012, 10:57 AM
  4. VBA debuggin issues....
    By sjl in forum Programming
    Replies: 1
    Last Post: 08-29-2011, 12:47 PM
  5. calendar issues
    By davidcousins in forum Access
    Replies: 1
    Last Post: 08-04-2011, 11:55 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
  •  
Tech Forums: Microsoft Office Forums