Solved

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

Posted on 2014-03-12
2
697 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

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 37

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

690 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