Results 1 to 10 of 10

New Record Requires a Related Record

  1. #1
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100

    New Record Requires a Related Record

    Hi, I'm building my own CRM database and when trying to add a new Contact record I get the following error shown below. Just wondering how to solve this. Thanks.

    Click image for larger version. 

Name:	Access_2019.06.14_08h59m51s_002_.png 
Views:	9 
Size:	35.1 KB 
ID:	38741
    Attached Files Attached Files
    Last edited by DigitalAdrenaline; 06-13-2019 at 05:41 PM. Reason: Added database file

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,162
    Sounds like relationship is backwards. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,713
    note the pencil icon in top form. This record is either in edit mode or not saved yet. Which one is the parent record and which is the child? Bottom looks like child to me, so you're trying to enter a child record before the parent record has been saved.

    EDIT -
    Sounds like relationship is backwards.
    That makes sense.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  4. #4
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Hi Guys, I've added the database file to my initial post for review. Thanks.

  5. #5
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Hi Guys, I'm trying to achieve a simple process. Open a contact form, create a new contact, then click a button to open an event form to create a new contact event. For some reason I'm just missing the connect to achieve this. Any help would be appreciated. Thanks.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,162
    Okay, relationship is fine if each contact can have only one event.

    However, tblEventLog cannot be a subform for tblContact. You need a combobox on main form to select an event. tblEventLog would be lookup source for combobox. If you need to enter a new event then that must be done on an independent form. If event is not listed in combobox, use NotInList event code to create new event record on-the-fly during data entry for contact.

    If each event can have only one contact and each contact can have many events, then the relationship is backwards - store ContactID into event record.

    If each contact can associate with multiple events and each event can have multiple contacts, that is many-to-many relationship and requires a third 'junction' table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Hi June7, I actually realised and amended the relationship and managed to display the relative events to the selected contact. I'm just trying to work out in the event form how to automatically display the contact name rather than it's FK number. The new data base is attached.

    Click image for larger version. 

Name:	CRM_2019.06.14_11h54m47s_003_.png 
Views:	6 
Size:	53.5 KB 
ID:	38744
    Attached Files Attached Files

  8. #8
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Guys, is it possible from the contacts form to open the event form, automatically select the FK matching the PK of the current selected record, rather than me manually search and select it in the text/combo box? Thanks.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,162
    Can have a combobox on tblEventLog form for the contact.

    What is Person field for?

    Remove EventFK from tblContacts table and form.

    If you mean open to a specific EventID, subform container would have to hold a form, not a table. Then code behind that form can reference its EventID.

    DoCmd.OpenForm "fmEvents", , , "EventID = " & Me.EventID

    These would have to be two different forms for events.

    If you mean open event form filtered by ContactID

    DoCmd.OpenForm "fmEvents", , , "ContactFK=" & Me.ContactID
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    The person field is for the person's name I'm speaking with for that event, either by phone, in person or by correspondence. This is more related to calling a contact card who's a company or business. Thanks for the VBA too. Will try that.

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

Similar Threads

  1. Replies: 17
    Last Post: 01-25-2018, 08:03 PM
  2. Replies: 4
    Last Post: 10-21-2017, 09:56 AM
  3. Replies: 6
    Last Post: 07-28-2016, 06:07 PM
  4. Replies: 15
    Last Post: 03-05-2015, 03:30 PM
  5. Replies: 40
    Last Post: 08-20-2013, 11:38 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
  •  
Tech Forums: Microsoft Office Forums