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

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
SteveL13Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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
 
PatHartmanConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.