Returning back to current record in recordset (1 Viewer)

[ssmith001]

I have a form that has a subform in which users can update a field called APPROVED_COUNT. If the count in this field does not equal another field called ROW_COUNT, I pop up a message to alert the user of the issue and if they answer YES to the message, I want to return right back to the record in question to allow them to fix the count. I've tried various things (movenext, movelast, etc) but I can't seem to figure out how I can return to the current record in the recordset.

Private Sub APPROVED_COUNT_AfterUpdate()

    ' when the manager has updated the APPROVED_COUNT field,
    ' we update the APPROVAL_DATE and APPROVING_MGR fields
        
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    Msg = "Approved Count <> DFU/SKU Count for location: " & Me.DEALER & "  " & _
            vbCrLf + vbCrLf + "Do you want to change the approved count ?"
    Style = vbYesNo + vbInformation + vbDefaultButton2
    Title = "Record Counts Don't Match"
        
    If Not IsNull(Me.APPROVED_COUNT) Then
        
        If Me.SKU_COUNT.Value = Me.APPROVED_COUNT.Value Then
            Me.OK_TO_PROCESS.Value = "Y"
            Me.APPROVAL_DATE = Now()
            Me.APPROVING_MGR = GetCurrentUserName()
        Else
            Me.OK_TO_PROCESS.Value = "N"

            Response = MsgBox(Msg, Style, Title)
            
            If Response = vbYes Then
                'Form.Recordset.MoveNext
                Me.Requery
            Else
                Exit Sub
            End If

        End If

    Else
    
        Me.APPROVAL_DATE.Value = ""
        Me.APPROVING_MGR.Value = ""
        Me.OK_TO_PROCESS.Value = "N"
    
    End If
        
End Sub

 

[rainman89]

have u tried setfocus?

 

[ssmith001]

Yes I did. No luck.

 

[rainman89]

IS the problem finding the correct record?
id use dlookup to find the exact record, and then go from there
ie
docmd.gotorecord blah

 

[Rich]

Use the BeforeUpdate event in conjunction with Cancel = True

 

[ssmith001]

Are you saying that I should change this AfterUpdate event to BeforeUpdate?

 

[rainman89]

Are you saying that I should change this AfterUpdate event to BeforeUpdate?

yeah, all validation should be done before the update.
what i think hes saying is

            Me.OK_TO_PROCESS.Value = "N"
            Response = MsgBox(Msg, Style, Title)            
            If Response = vbYes Then
               cancel=True 
            Else
                Exit Sub

this will stop the updating and leave u at the current record. you could put a setfocus in there to make it go to the exact field

 

[ssmith001]

Thanks, that did it.

 

[rainman89]

glad to help, even though rich did all the thinking