Solved

How open form for new record but allow user to get an existing record with a combobox

Posted on 2014-03-12
2
686 Views
Last Modified: 2014-03-13
Is this possible?

On a main menu form I have a command button that opens a 2nd form using:

DoCmd.OpenForm "frmImages", acNormal, , , acFormAdd, acWindowNormal

But on the 2nd form I have a combobox which is supposed to allow the user to find an existing record.  But it doesn't work probably because I'm opening the form for new record.

Here is the code for the afterupdate event of the combobox:

    Dim RS As Object

    Set RS = Me.Recordset.Clone
    RS.FindFirst "[ImageID] = " & Str(Nz(Me![cboFindRecord], 0))
    If Not RS.EOF Then Me.Bookmark = RS.Bookmark
   
    Me.cmdCloseForm.SetFocus

Is it even possible to have a form open for new but also go to an existing record?

--Steve
0
Comment
Question by:SteveL13
2 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39924543
acFormAdd essentially opens the form in "data entry" mode, which means you can only add new records.

You could instead open the for as normal, and take the user to a New record like this:

DoCmd.RunCommand acRecordsGoToNew

This would place the user on a New record, but the rest of the recordset would be available for your Bookmark code.
0
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 39926553
I do this with only one line of code.  The form's RecordSource query refers to the combo on the form for selection criteria.
WHERE somefield = Forms!myform!somefield

When somefield is empty as it would be when you opened the form initially, the form opens to a new record.  You can then pick something from the combo and the AfterUpdate event of the combo requeries the form.
Me.Requery

When I want the form to also be opened by a different form and accept a where argument, I have to make an adjustment.  In this case, in the form's Open event, I check for an OpenArgs value and assume that was passed by the calling program and place it into the combo.  That way the form will open to the specified record.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

831 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