Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Add New Record With Same Foreign Key


    I want to make a table for notes and I'm having many different problems achieving this.

    I need the new form that pops up to already be referencing the record I was on when I pressed the Cmd button.

    If I relate the button to the current record it automatically shows all the notes for the record. I only want the ID so I can add more.

    any tips appreciated. Andy

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You can store values in global variables or in a form's Open Args property.

    To create a variable that is available globally, declare it in a standard module.
    Public glngMyNumber as Long

    To open a form and include a value in the other form's open args property.
    DoCmd.OpenForm "frmTheOtherForm", , , , , , Me.ImportantID

    In the other form, you can access the Open Args property
    Dim lngValue as Long
    lngValue = Me.OpenArgs

  3. #3
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    rather than having a separate form, consider using a subform (this is the sort of situation they are for) if your notes table is displayed in a subform and the table it relates to in the main form, then providing you complete the linkchild and linkmaster properties of the subform, it will populate the ID field automatically e.g.

    maintable
    recordPK autonumber
    recordname text
    ...
    ...
    ...

    notestable
    notesPK autonumber
    RecordFK long (links to RecordPK in maintable)
    Notes text/memo as required
    DateEntered datetime (default now())
    EnteredBy text (optional)
    ...
    ...

    So the recordsource for your main form is maintable
    and the recordsource for your notes subform is notestable
    then the linkchild property will be recordFK
    and the linkmaster property will be recordPK

    if you have already created a relationship between recordPK and recordFK in the relationships window, then the linkchild and linkmaster properties will have been completed automatically

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Thanks a lot. I'm making some progress... slowly

    the solutions to the problems I'm having can be applied throughout my project so I do really appreciate it!
    Andy.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Cheers, I have it working. One step closer.

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

Similar Threads

  1. Selecting record in foreign table using VBA
    By Jmeyering in forum Programming
    Replies: 3
    Last Post: 11-12-2012, 04:29 PM
  2. My first access database - new foreign key record
    By michal_milena in forum Access
    Replies: 1
    Last Post: 10-21-2012, 03:30 PM
  3. Replies: 2
    Last Post: 06-06-2012, 09:35 AM
  4. Replies: 2
    Last Post: 04-19-2012, 11:29 AM
  5. Replies: 2
    Last Post: 10-29-2011, 02:07 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
  •  
Other Forums: Microsoft Office Forums