Inserting current forms record 'primary id' number into table so I can open a new form linked on current forms id

Posted on 2011-05-11
Last Modified: 2013-11-27
Hi there,

I have a form with a subform in datasheet view. What I want to be able to do, is to double click on a subforms "SiteSubmittedID" (Primary key- autonumber) record and open a new form (frmComments), filtered on the current SiteSubmittedID that I double clicked on.

This is where I need code I guess? Obviously, I need to insert all the SiteSubmittedID's into the table behind frmComments so that it can then open to the right record through vba? The second part of the code needs to ensure that this vba routine doesn't duplicate the record every time the user double clicks that particular line item.

Once in the comments form though, a user should be able to add more than 1 comment for each SiteSubmittedID in the table behind frmComments.

Question by:jammin140900
    LVL 77

    Expert Comment

    " What I want to be able to do, is to double click on a subforms "SiteSubmittedID" (Primary key- autonumber) record and open a new form (frmComments), filtered on the current SiteSubmittedID that I double clicked on. "

    In response to this specific question, you use the double-click event procedure for the SiteSubmittedID field and open the form using the 'Where' parameter..

    docmd.OpenForm "frmcomments", , ,"sitesubmittedid= " & me.sitesubmittedid

    This will not create a new record containing the id; it just limits the records viewed to those that match.
    If there are no matching records then you will be placed on a new record because that is only place Access can go, but it will not have the id entered.

    I can't comment (!!!) on the 'Comments' form because I don't know what it looks like.

    Author Comment

    Thanks Peter.

    I tried doing something similar above but it opens the Comments form on a blank record because obviously no SiteSubmittedID exists as yet. Is there a way to insert the current SiteSubmittedID into the field tblComments.SiteSubmittedID so that when it opens, it has a record to link to?
    LVL 77

    Expert Comment

    You can pass the id value agin in the openargs parameter.

    docmd.OpenForm "frmcomments", , ,"sitesubmittedid= " & me.sitesubmittedid,,,sitesubmittedid

    Then in the form current event procedure of frmcomments you do..

    If me.newrecord and not isnull(me.openargs) then me.sitesubmittedid = me.openargs

    Author Comment

    where abouts is the openargs parameter that you can insert the code into? I checked the events property options for the subform but couldn't see it? Is that the 'on double click' property?
    LVL 77

    Accepted Solution

    I have given you all the code you need- subject to you using your own form/field names.
    openargs is the final parameter in the Openform command.
    You just use the code as indicated, there is nothing else to do.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now