Solved

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

Posted on 2014-03-12
2
687 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

792 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