[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

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

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.

0
jammin140900
Asked:
jammin140900
  • 3
  • 2
1 Solution
 
peter57rCommented:
" 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.
0
 
jammin140900Author Commented:
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?
0
 
peter57rCommented:
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
0
 
jammin140900Author Commented:
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?
0
 
peter57rCommented:
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now