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

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Rey Obrero (Capricorn1)Commented:
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] & "'"

tpigielskiAuthor Commented:
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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tpigielskiAuthor Commented:
Thanks Scott....this last suggestion solved my problem.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.