Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2017
    Posts
    7

    Answered: Copy ID from one table to another with command button

    Hello all,
    I need to grab the ID from one table and copy it to another when a command button is clicked. The button is located on a form that is bound to a table called tblINS, the button name is "Create Rework". When the button is clicked, it opens another form bound to a table named tblRwk. I need to grab the number from DAV_ID from tblINS and populate a field called DAV_ID in tblRWK. I have a 1 to Many relationship set up as there could be multiple instances of rework created for each item in the tblINS table.

    Any help would be greatly appreciated.

    Thanks
    Dave

  2. Best Answer
    Posted by weejas

    "I didn't do anything to the forms as they are. I'll give you some pointers in the first instance - have a try, and then let me know if you get stuck.

    And I know I didn't have a spot on the form for those but it was in the tables and I didn't think it needed to be on the form
    If there's nowhere to put the parent ID on the rework form, how is it going to get into the underlying table? It doesn't need to be on the initial form - there seems to be a 1:1 relationship between NCR_ID and NCR_Num, so a DLookup will get the former from the latter. Try the following:
    • Add [Rework Instructions].NCR_ID to the data source for [NCR Rework Instructions]
    • Create a control on your rework form that is bound to the NCR_ID field of the rework table. You can set it to invisible if you don't want the users playing with it. For the sake of this example, call it "NCR_ID".
    • Replace the DoCmd instruction in the cmdRework button's On Click event as follows

    Code:
    DoCmd.OpenForm "NCR Rework Instructions", acNormal, "", "",acFormAdd , acWindowNormal, Me.NCR_Num
    • In the On Load event of the rework form, add the following code:

    Code:
    Me.NCR_Num = Me.OpenArgs
    Me.NCR_ID = DLookup("[NCR_ID]", "Current NCR's", "[NCR_Num] = '" & Me.NCR_Num & "'")
    To quote an old saying,
    "I hear, and I forget.
    I see, and I remember.
    I do, and I learn.

    Oh, and work on a copy of the database, just in case something goes very wrong..."


  3. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,095
    Provided Answers: 17
    Add code to the command button after the line that opens the rework form to copy the relevant control's value on the first form to the relevant control on the second form.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #3
    Join Date
    Feb 2017
    Posts
    7
    That's just it, I don't know what code to enter to garb what I want and populate the second table

    Dave

  5. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,095
    Provided Answers: 17
    Code:
    Forms![ReworkFormName].DAV_ID = Forms![INSFormName].DAV_ID
    Replace the brackets with the actual names and have a play with this syntax.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  6. #5
    Join Date
    Feb 2017
    Posts
    7
    That gives me a "Run-time error '2465' Application-defines or object defined error"
    Last edited by Dave 14867; 02-08-17 at 12:41.

  7. #6
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,095
    Provided Answers: 17
    Well, it's been a while since I tried that trick.

    You could instead pass the ID value to the rework form as the OpenArgs value, and then use the form's On Load event to populate its ID field with the OpenArgs value.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  8. #7
    Join Date
    Feb 2017
    Posts
    7
    I have attached a copy of the file below, I need to have it populate the NCR_ID field when the cmdCreateRework" command button is selected.

    Dave.zip

    Thanks

    Dave

  9. #8
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,095
    Provided Answers: 17
    Okay, a few things:
    • Wherever possible, avoid spaces, apostrophes and other special characters in object or control names. It'll make it a lot easier to write and understand your code.
    • Your command button didn't have the correct name for your rework form.
    • Your NCR form's Data Entry property was set to Yes - this will prevent the form from being used to display existing record.
    • Most importantly, neither of the forms captures or displays the field NCR_ID! It's difficult to pass on a piece of data that doesn't exist, especially when there's nowhere to put it.


    Once these are resolved, you'll be in a better place to start making this change.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  10. #9
    Join Date
    Feb 2017
    Posts
    7
    weejas, can you zip and attach the file so I can see what you did so next time I won't have to ask. (hopefully). And I know I didn't have a spot on the form for those but it was in the tables and I didn't think it needed to be on the form

    Thanks

    Dave

  11. #10
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,095
    Provided Answers: 17
    I didn't do anything to the forms as they are. I'll give you some pointers in the first instance - have a try, and then let me know if you get stuck.

    And I know I didn't have a spot on the form for those but it was in the tables and I didn't think it needed to be on the form
    If there's nowhere to put the parent ID on the rework form, how is it going to get into the underlying table? It doesn't need to be on the initial form - there seems to be a 1:1 relationship between NCR_ID and NCR_Num, so a DLookup will get the former from the latter. Try the following:
    • Add [Rework Instructions].NCR_ID to the data source for [NCR Rework Instructions]
    • Create a control on your rework form that is bound to the NCR_ID field of the rework table. You can set it to invisible if you don't want the users playing with it. For the sake of this example, call it "NCR_ID".
    • Replace the DoCmd instruction in the cmdRework button's On Click event as follows

    Code:
    DoCmd.OpenForm "NCR Rework Instructions", acNormal, "", "",acFormAdd , acWindowNormal, Me.NCR_Num
    • In the On Load event of the rework form, add the following code:

    Code:
    Me.NCR_Num = Me.OpenArgs
    Me.NCR_ID = DLookup("[NCR_ID]", "Current NCR's", "[NCR_Num] = '" & Me.NCR_Num & "'")
    To quote an old saying,
    "I hear, and I forget.
    I see, and I remember.
    I do, and I learn.

    Oh, and work on a copy of the database, just in case something goes very wrong...
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  12. #11
    Join Date
    Feb 2017
    Posts
    7
    weejas,

    That seemed to work great, I have some clean up to do because of where I placed the fields as you suggested but it appears that I have what I want.

    Can you explain what the code is doing that you suggested I add at the locations? I am new at this and still learning the code behind the curtain so to speak.

    Thank you very much for your assistance.

    I totally agree with you quote also. I prefer doing things myself because that way I learn more but we all need guidance from time to time.

  13. #12
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,095
    Provided Answers: 17
    You're welcome!

    The OpenForm method takes seven arguments, the last of which is OpenArgs. This exists as a means of passing a piece of information to a form upon opening (https://msdn.microsoft.com/en-us/lib.../ff820845.aspx). Putting the name of the control that contains the current NCR number into the command passes the value of the current NCR number to the rework form as soon as it opens.

    The OpenArgs property of a form is read-only within the form, assuming that you've populated as above. The two lines work in combination - the initial form picks up the NCR number and uses it in the OpenForm method to populate the rework form's OpenArgs property, and then the rework form's On Load event takes that value and uses it to populate its own NCR number control.

    Finally, the DLookup command uses the value in the NCR number control to find the parent NCR ID in the named table. DLookup is a bit of a Marmite function - people either love it or hate it. I like it, because it's fairly reliable and generally doesn't give problems as long as you either use key (or other unique) values in the third argument, and remember to handle the possibility of getting a Null back if it can't find what you're looking for.

    Apologies if this is a bit rambling - after 17 years of using Lotus Notes, I've just been forcibly migrated to Outlook.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  14. #13
    Join Date
    Feb 2017
    Posts
    7

    Smile

    I will use this method in the future and hopefully won't have the same problem.

    Thanks for explaining it. You have been a huge help.

    And Outlook works very well BTW.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •