Opening an Access 2007 form on an existing record or a new record

Posted on 2014-07-15
Last Modified: 2014-07-15
I have an Access 2007 form that can either be opened from a main menu, in which case I want the form to open to a new record, or, it can open from someone clicking on another record in a subform of another form.  In the latter case, I want the form to open with the details associated with the record double-clicked on the subform.

I have tried using some sort of "global variable" to control whether or not the form should be opened to a new record, or the record associated with the line in the subform that is selected, but cannot seem to get this to work.

Can someone suggest a simple, correct way to do this operation please?

Question by:tpigielski
    LVL 20

    Expert Comment

    this is what I use.

    If existing record - and you know the PK of the desired record (click on it from a different form).

    dim PK as long (or whatever your Primary Key is).
    PK = ME.[YourPK]
    DOCMD.OPENFORM "frmYourForm",,, "[YourPK] = " & pk

    (hint: if you want to open in Modal Mode then add this to the syntax:

    Docmd.Openform "frmYourForm",,,"[yourPK] = " & pk,,ACDIALOG

    Now, to open to a new record:
    Docmd.openform "frmYourForm" ,,,,acFormAdd

    Scott C
    LVL 119

    Expert Comment

    by:Rey Obrero
    to open the form to new record

    docmd.OpenForm "formx", , , ,acFormAdd

    to open the record on double click of the record, you need to grab the unique record id and use it as a where condition when opening the form

    if record id is Number type
    docmd.OpenForm "formx", , ,"[NameOfRecorID field]=" & me.[recordId]

    if record id is TEXT type
    docmd.OpenForm "formx", , ,"[NameOfRecorID field]='" & me.[recordId] & "'"


    Author Comment

    Thank you for your suggestions, but when I open the form for a new record, I just want to have the form position the user at the end of the record set mapped to the form.  (I didn't think to mention this in my 1st post).  So, even though the user is positioned on a "new record", they can still scroll back to any record in the record set that is mapped to the form.

    Currently, I did this by creating a button on the main menu that just opened the form, with:


    Then, in the form being opened, put the following in the "load" property of the form:

    DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

    This last line is the one I want to "toggle" depending on whether I'm coming from the main menu, or from a specific record on another subform.

    Sorry I wasn't more clear in the 1st post.

    LVL 20

    Expert Comment

    Is your form a tabular or single record form?
    A single record form opening with the acnewrec will only allow additional records to be added.
    I just tried it.  I created a quick form (single record view) of an existing table - opened it up with acnewrec and it only allows me to add (and view any records I just added).  I cannot scroll to any other previously existing records.

    I'm not sure what you're asking (?)

    Scott C
    LVL 20

    Accepted Solution

    If this is your exact syntax - it's incorrect:

    DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

    Try this:  (notice the acFormAdd change)

    DoCmd.GoToRecord acDataForm, Me.Name, , , , acFormAdd

    Scott C

    Author Closing Comment

    Thanks Scott....this last suggestion solved my problem.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    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…

    779 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

    13 Experts available now in Live!

    Get 1:1 Help Now