Results 1 to 10 of 10
  1. #1
    Stuartjacks is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2021
    Posts
    5

    Fill field from other record in same table

    Hi
    I'm a newbie so please bear with me.

    I have a table which contains two relevant record fields,(among others)
    1. PersonID (autonumber & key field)
    2. OtherID. (number type field)

    So say record one has PersonID of 1 and no entry in OtherID.


    when another record is created say Record 5 PersonID is 5 and OtherID then has 1 entered into it.

    what I want is for record1 (personID1) to have the value of 5 entered into OtherID
    this would happen because PersonID 5's OtherID equals the Person1's PersonID of 1

    I am trying to match Partners in a database of people if that helps and want the older entry updating when a new entry is made.

    Sorry if its long winded but don't know how else to explain my problem. - I as yet don't do much coding just simple VB from the "properties" tab.

    Thanks in advance.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may want to research self-joins in MS Access. Here's a video.

  3. #3
    Stuartjacks is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2021
    Posts
    5

    Thanks.

    Quote Originally Posted by orange View Post
    You may want to research self-joins in MS Access. Here's a video.
    Thank you, I will watch and hopefully learn.

  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
    Post 3 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Stuartjacks is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2021
    Posts
    5

    Doesn't update

    Quote Originally Posted by Stuartjacks View Post
    Thank you, I will watch and hopefully learn.
    I have run the video and built a self-join which does relate the pairs of records but only where the data is in the table.
    I was looking for a way to automatically fill in the last of the four items - The OtherID field on the first record , where the second record was not created at the time the first was filled with data.

    Hope that's clear

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Rather than describing the input and output, which is always subject to interpretation, why not post sample data and expected outcome here as a table or two? Either build in Excel and post here or Go Advanced button below the posting window and build your own html tables - one with inputs, the other with outputs. One record example may not be enough to dispel all questions.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Stuartjacks is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2021
    Posts
    5
    Quote Originally Posted by Micron View Post
    Rather than describing the input and output, which is always subject to interpretation, why not post sample data and expected outcome here as a table or two? Either build in Excel and post here or Go Advanced button below the posting window and build your own html tables - one with inputs, the other with outputs. One record example may not be enough to dispel all questions.
    Click image for larger version. 

Name:	Picture1.png 
Views:	10 
Size:	106.5 KB 
ID:	45891
    I have posted a picture of what I am trying to achieve.
    Please note
    1. Both records are in the same table
    2. New record (shown as 5) is in the process of being created so its PersonID, (auto number), will not be known until the record is saved.
    3. Record 1 was created before the data for record 5 was known.
    4. I am trying to do this automatically.
    Attached Thumbnails Attached Thumbnails Picture1.png  

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    And what happens when 3rd record (e.g PersonID = 6) is created? Or when 1000ths record is created? Or does your table always have only 2 records?

  9. #9
    Stuartjacks is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2021
    Posts
    5

    reply to ArviLaanemets

    Quote Originally Posted by ArviLaanemets View Post
    And what happens when 3rd record (e.g PersonID = 6) is created? Or when 1000ths record is created? Or does your table always have only 2 records?
    Only two records will ever match - this is not a case of adding another figure to an existing one.
    The person adding the new record selects the OtherID input by selecting a persons name from a combobox, not by inputting the numerical value, the numerical value is not be shown on the add new record form.
    The table will have may records but the idea is match two, (person and partner/wife/husband), only.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You've asked for something that seems quite specific and is a one time thing. Why you'd want to automate this at all is a mystery as it will only happen once - when the record number is 5. Seems far more practical to just type in the number 5.

    Anyway, here's the automation: with the form in design view and the property sheet visible, select the sheet Event tab and then the PersonID control (assuming that's what it is called). Then click on the Before Update line in the sheet but at the far right of the line. Choose the ... (code builder) and the vb editor will open and it will start a procedure. Should look like

    Private Sub PersonID_BeforeUpdate()

    End Sub

    Put this line in between those two.
    Code:
      If Me.PersonID = 5 AND IsNull(Me.OtherID) Then Me.OtherID = 5
    Now you have your automation. When record id is 5 (or more accurately the PersonID is showing 5) the other field will become 5 when the cursor is moved off of the PersonID control.

    EDIT - Make sure you use your own form control names. The above assumes 5 is in a number field. If it is text put quotes around it - "5".
    Last edited by Micron; 08-02-2021 at 09:02 AM. Reason: code correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 14
    Last Post: 03-15-2017, 08:33 PM
  2. Replies: 8
    Last Post: 10-24-2014, 01:11 PM
  3. Replies: 3
    Last Post: 03-07-2014, 10:39 AM
  4. Replies: 1
    Last Post: 05-10-2013, 06:26 AM
  5. Auto fill field upon New Record selected
    By NOTLguy in forum Programming
    Replies: 27
    Last Post: 12-05-2010, 05:12 PM

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