Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Dec 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Adding foreign key to form

    I have struggled with this for way too long and finally bit the bullet to ask for help... I am very new to Access databases. I have the following tables: Clients & ProgressNotes. I have a form to enter new data into the Client table. At the bottom of that form I have a button to add a ProgressNote. When they click the button, a form opens to enter items into the ProgressNotes table.

    I am trying to copy the ClientID from the Client table into the fk_ClientID of the ProgressNotes table. Here is the code I have...

    '------------------------------------------------------------
    ' Enter_Progress_Note_Click
    '
    '------------------------------------------------------------
    Private Sub Enter_Progress_Note_Click()
    On Error GoTo Enter_Progress_Note_Click_Err
    DoCmd.OpenForm "ProgressNotes", acNormal, "", "", acAdd, acNormal
    Me.fk_clientID = Forms!Clients!ID
    Enter_Progress_Note_Click_Exit:
    Exit Sub
    Enter_Progress_Note_Click_Err:
    MsgBox Error$
    Resume Enter_Progress_Note_Click_Exit
    End Sub

    I have looked at a lot of things via Google searches and haven't been able to figure out my example. I'm sure it's easier than I am making it OR it's a lot harder than I think!

    Thanks for any assistance,
    Chuck

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Chuck,

    Welcome to the Lounge as a New Poster!

    This line: Me.fk_clientID = Forms!Clients!ID

    is what I believe is causing your problem. This line of code is in the ClientID form and the use of the ME refers to this form not the Progress Notes form.

    What you want is something like this: Form!NameOfProgressNoteForm!fk_clientID = Me.Clients!ID

    Since the ProgressNoteForm is not the Current Form you have to reference it explicitly. Note: the syntax I used may not be exactly right and is air code but you get the idea. Note: this is called BANG notation, you can google it.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Dec 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RetiredGeek - I hope to be there soon! Thanks for the welcome, I'm excited about learning more.

    OK, so even though I put the "Me.fk_clientID" after the line to open the ProgressNotes form it was in the code for the Client. that makes sense. I made the change, but now I get an "Object Required" popup message.


    Quote Originally Posted by RetiredGeek View Post
    Chuck,

    Welcome to the Lounge as a New Poster!

    This line: Me.fk_clientID = Forms!Clients!ID

    is what I believe is causing your problem. This line of code is in the ClientID form and the use of the ME refers to this form not the Progress Notes form.

    What you want is something like this: Form!NameOfProgressNoteForm!fk_clientID = Me.Clients!ID

    Since the ProgressNoteForm is not the Current Form you have to reference it explicitly. Note: the syntax I used may not be exactly right and is air code but you get the idea. Note: this is called BANG notation, you can google it.

    HTH

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Chuck,

    Ok I looked up some code so let's try it this way;

    Code:
    '------------------------------------------------------------
    ' Enter_Progress_Note_Click
    '
    '------------------------------------------------------------
    
    Private Sub Enter_Progress_Note_Click()
    
       On Error GoTo Enter_Progress_Note_Click_Err
    
      DoCmd.OpenForm "ProgressNotes", acNormal, "", "", acAdd, acNormal
    
      Forms.ProgressNotes.fk_clientID = Me.ID
    
      Enter_Progress_Note_Click_Exit:
    Exit Sub
    
    Enter_Progress_Note_Click_Err:
    MsgBox Error$
    Resume Enter_Progress_Note_Click_Exit
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Chuck,

    Here's a test database file you may want to take a look at.

    Open frmSyncTest1 and use the nav buttons to move through the records watching frmSyncTest2 (which will open automatically)
    Then take a look at the code behind frmSyncTest1.

    Test File: SyncTest.zip

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Chuck,

    Here's yet a better example as I've split the DB into Master/Child tables and added a New Comment button.

    Test File: SyncTest.zip

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I cannot understand why you don't use Form/SubForm logic instead.

  8. #8
    New Lounger
    Join Date
    Dec 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by patt View Post
    I cannot understand why you don't use Form/SubForm logic instead.
    Probably because I'm not sure what you are referring to...

    I started using the built-in macro's and then had to convert it to VBA. So, that's where I'm at with it. I'm not really familiar with Access and I am trying to learn. I don't need a form within a form if that is what the subform is.

    Feel free to enlighten me or point me to a resource.

    Thanks,
    Chuck

  9. #9
    New Lounger
    Join Date
    Dec 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RetiredGeek - that worked. Just had to reverse the logic I suppose! Thank you for the database examples to learn from. That issue has stumped me for awhile. I think the ME reference was a little confusing. I was thinking that if Form A opened Form B in the code that the ME reference after opening Form B would refer to Form B. Obviously, that is not the case.

    Thanks so much for you assistance! I hope you have a Merry Christmas and an awesome New Year!

    Chuck

  10. #10
    New Lounger
    Join Date
    Dec 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile

    Quote Originally Posted by RetiredGeek View Post
    Chuck,

    Ok I looked up some code so let's try it this way;

    Code:
      Forms.ProgressNotes.fk_clientID = Me.ID
    RetiredGeek - Once I made that connection I was able to add other fields from the Client Form to the Progress Notes Form from a list. I didn't have to add the code in order to add them. Thanks!

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Access provides a good solid solution in the form/subform way of doing things with virtually little VBA code required.

    I won't try and talk you out of the way you have chosen, I have never gone that way, but it works, so way to go.

Posting Permissions

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