asked on

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?

Thanks...Tom

clarkscott

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)

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] & "'"

.

tpigielski

ASKER

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:

=OpenForms("ClassInstance")

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.

Tom

clarkscott

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

ASKER CERTIFIED SOLUTION

clarkscott

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tpigielski

ASKER

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

Tom