Return to Next Record On Datasheet

I have a form containing a datasheet sub form used for processing records. When the user double-clicks on a record, it loads the corresponding customer form. Once processing has been completed, the underlying record from the datasheet is deleted and the sub form is refreshed. However, once refreshed, the pointer goes to the first record. I want it to go to the record just after the one just processed.

There are two triggers that cause the record in the datasheet to be deleted. If certain values are entered in the customer record, it deletes the record in the datasheet upon closing the customer form. The other method is clicking a 'Processed' button on the form containing the datasheet.

So, I need to add code in both locations or at the point that the form regains focus. Can someone help me with the VB code to accomplish this?

Thanks.
LVL 3
dwcummingsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
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

Open 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.
0
dwcummingsAuthor Commented:
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
0
mbizupCommented:
--- >> 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
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

mbizupCommented:
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  

Open in new window

0
dwcummingsAuthor Commented:
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?
0
mbizupCommented:
Basically the same principle, but using subform references (replace "MySubformName" with the actual name of your subform control in the code below):

    Dim lngID as long
    Dim rs as DAO.Recordset

     lngId = DMin("NoEmailId", "qryNoEmail", "NoEmailId > " & Me.MysubformName.Form.NoEmailId)

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

    With Me.MySubformName.Form 
        Set rs = .RecordsetClone
        rs.FindFirst "NoEmailId = " & lngId
        .Bookmark = rs.Bookmark
   End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dwcummingsAuthor Commented:
Thanks again. You went the extra mile on this one. If I could award you double points I would.
0
mbizupCommented:
:-)

Glad to help,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.