Return to Next Record On Datasheet

To do this:

generally (read the inline comments in the example below) -

' In the double-click event, declare a holder for your record ID (assuming you have an identifying field such as an autonumber)


Dim lngID as Long

'Set the value of that field to one past the current record:

lngID = DMin("YourIDField", "YourTable", "yourIDField > " & Me.YourIDField

'Open your customer form in dialog mode:

Docmd.OpenForm "CustForm",,, {your criteria goes here},,,acDialog

' Immediately  After the Open Form, add code to go to the correct record:

dim rs as DAO.recordset
set rs = Me.RecordsetClone
rs.FindFirst "YourIDField = " & lngID
Me.Bookmark = rs.Bookmark

Select allOpen in new window

That's it in its simplest form... you can/should embellish that with error handling and handling for special cases like no exisitng "next" record.

Thanks for responding.

However, I am still getting the same result. When the customer form closes, the record pointer goes to the first record on the datasheet.

Here is the code on the double-click event on the datasheet subform:

    Dim lngId As Long
    Dim rs As DAO.Recordset
   
    lngId = DMin("NoEmailId", "qryNoEmail", "NoEmailId > " & Me.NoEmailId)
       
    DoCmd.OpenForm "frmCustomers", , , "CustomerId = " & Me.CustomerId    
    Set rs = Me.RecordsetClone
    rs.FindFirst "NoEmailId = " & lngId
    Me.Bookmark = rs.Bookmark

Here is the code on the close event for the customer form:

    Dim CustId As Long
   
    CustId = Me.CustomerId

    DoCmd.Close acForm, "frmCustomers"
    If CurrentProject.AllForms("frmNoEmail").IsLoaded Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL "Update dbo_tblNoEmail Set ProcessedBy = '" & fOSUserName() & _
            "', Processed = True, DateProcessed = #" & Date & "# Where CustomerId = " & _
            CustId & ";"
        DoCmd.SetWarnings True
        Forms!frmNoEmail!frmNoEmailSF.Form.Requery
    End If

Doug

--- >> DoCmd.OpenForm "frmCustomers", , , "CustomerId = " & Me.CustomerId    

Take another look at my earlier post :-)

You need to open the form in dialog mode.  This temporarily suspends code execution in the calling block of code... and importantly resumes code execution when the form closes.

Without using dialog mode, the code following the OpenForm is ignored.  

So change that line to this:

DoCmd.OpenForm "frmCustomers", , , "CustomerId = " & Me.CustomerId,,, acDialog

Actually - I think I'm messing up with the number of commas.  The window Mode might be the fifth argument:

DoCmd.OpenForm "frmCustomers", , , "CustomerId = " & Me.CustomerId ,, acDialog


But my preference is to use named arguments to avoid the confusion with the commas:

DoCmd.OpenForm "frmCustomers", WhereCondition := "CustomerId = " & Me.CustomerId, WindowMode := acDialog  

Select allOpen in new window

Thanks,  that worked. Sorry I missed the "acDialog" as I already had the code to open the form.

One more question, I have a processed button on the parent form as an alternative to opening the form.  The existing code is:

    DoCmd.SetWarnings False
    DoCmd.RunSQL "Update dbo_tblNoEmail Set ProcessedBy = '" & fOSUserName() & _
        "', Processed = True, DateProcessed = #" & Date & "# Where CustomerId = " & _
        CustId & ";"
    DoCmd.SetWarnings True
    Me.frmNoEmailSF.Requery


What code would I use on the on-click event for that control that would accomplish the same result?

Thanks again. You went the extra mile on this one. If I could award you double points I would.

:-)

Glad to help,