Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

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
0
tpigielski
Asked:
tpigielski
  • 3
  • 2
1 Solution
 
clarkscottCommented:
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
0
 
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] & "'"


.
0
 
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:

=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
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
clarkscottCommented:
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
0
 
clarkscottCommented:
Also,
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
0
 
tpigielskiAuthor Commented:
Thanks Scott....this last suggestion solved my problem.

Tom
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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