Solved

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

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

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now