Results 1 to 7 of 7
  1. #1
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30

    CmdButton opens form to individual record. User needs to view AND/OR add new records

    I have an AC2007 database which is split frontend/backend. Users access data via forms. I Use a linked one-to-many table to allow users to add ongoing series of comments about each record listed on the main form. Hope to avoid using tabbed subform so that the linked comments only open when needed, not every time the main form is opened.



    A command button was added to the main form. It opens a second form which is filtered to the ID of the main form. This works great for viewing existing data. But, now I want the user to also have the option of adding new records on the second form. But, I don't know how to pass the PK to the second form for new records. The form opened with the command button will currently allow users to enter a new record into the second table, but it does not record the PK and the new record is just orphaned.

    I know this would work great as a subform on a tab. But, there are so many controls on the mainform and various subforms that I would like to go the route of a command button. that way the related records are only loaded when needed. Is it possible to tell the second form to pass on the current ID when the user tabs to a new record?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    See if this link helps: http://www.baldyweb.com/OpenArgs.htm
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30

    strOpenArgs placement

    Quote Originally Posted by RuralGuy View Post
    See if this link helps: http://www.baldyweb.com/OpenArgs.htm
    Ruralguy,
    I had read that baldyweb reference during my searches, but wasn't sure what it was adding.
    Where should that be placed? Does that go in one of the events of the form that is opened by the command button? Or is it part of the original command button that opens the second form? Very inexperienced with this....

  4. #4
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30
    Quote Originally Posted by RuralGuy View Post
    See if this link helps: http://www.baldyweb.com/OpenArgs.htm
    Ok, wait....that last response was pretty dumb. Sorry...

    I think I only need to pass the PK value to the form when I open it, but in keeping it the same as the example I tried to pass two fields(pk and last)......so....
    In the OnClick event of the command button, I have "DoCmd.OpenForm "nameofform",,,,,,me.PK & ";" & me.last

    Should the second set of code go in the OnLoad or OnOpen event of the form that is opened by the command button? Or a different event?
    Also, I'm not sure what the "me.txtotherinfo" is referring to?

    Thanks for any guidance.....

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    Use the OnLoad event of the 2nd Form as it allows you to reference controls on the 2nd Form. In Paul's example he was simply showing how to pass more than one value in the OpenArgs argument. If you only need one then only pass one value. You may wish to download the example db he provided and see it in action.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  6. #6
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30
    Quote Originally Posted by RuralGuy View Post
    Use the OnLoad event of the 2nd Form as it allows you to reference controls on the 2nd Form. In Paul's example he was simply showing how to pass more than one value in the OpenArgs argument. If you only need one then only pass one value. You may wish to download the example db he provided and see it in action.
    Ruralguy, does this work when two tables are involved?
    Basically, I have a contact database I'll call "tblContacts" with an autonumber PK called "ContactID"
    "ContactID" is a FK in a table I'll call "tblHistory" that is used to store a history of comments about each of the records in the main table. Each new comment is a new record in the "tblHistory". tblContacts is in a one-to-many relationship with tblHistory.

    I have a main form I'll call "frmContactDetails". On that main form is a CmdButton which opens a form I'll call "frmNoteHistory" The following code is inserted in the OnClick event of the CmdButton:

    Private Sub ViewDetailEditButton_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmNoteHistory"

    stLinkCriteria = "[contactid]=" & Me![ContactID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End Sub

    This works great for viewing a list of historical notes about each contact. But, if I try to add a new note to that form, it will accept the note, but does not include the linked PK, "contactID". So, the tblHistory will get a new record, without a contactID linked to it.....

    Everything is fine when I was doing all of this through a basic tab with a subform. But, I want to accomplish the same thing through a command button. If OpenArgs is the way to go, I'll keep exploring. But, I keep getting errors and feel like I'm going backwards.....

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    You would need to fill in the ForeignKey value yourself with code as Access will not do it for you unless you are using the Form/SubForm arrangement as you have noted. Since you already have the FK value in a control on your next form visible or invisible then I would suggest using the code in this link: http://access.mvps.org/access/forms/frm0012.htm
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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

Similar Threads

  1. Replies: 2
    Last Post: 03-31-2012, 01:28 AM
  2. Replies: 3
    Last Post: 05-28-2011, 09:54 PM
  3. Form opens table in datasheet view
    By franklbl in forum Forms
    Replies: 9
    Last Post: 03-23-2011, 09:43 PM
  4. Replies: 1
    Last Post: 03-07-2011, 10:48 AM
  5. Replies: 3
    Last Post: 01-25-2011, 09:50 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 - Senior Forums