Link to home
Start Free TrialLog in
Avatar of jbakestull
jbakestull

asked on

Not in List (goes to next record after update)

When I apply the following code to not in list (bound form), the code does add new data to table and field but also moves to next record. How do I keep from moving to next record?

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim Msg As String
Dim NewID As String
'On Error GoTo Err_Provider_NotInList

       ' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

    ' Confirm that the user wants to add the new Sku.
Msg = "File Status" & space(1) & " '" & NewData & "' does not exist." & vbCr & vbCr
Msg = Msg & "Do you want to create status?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        ' If the user chose not to add a Sku, set the Response
        ' argument to suppress an error message and undo changes.
Me.Undo
Response = acDataErrContinue
    Else
        ' If the user chose to add a new Sku, open a recordset
        ' using the tblSku table.
    Set conn = CurrentProject.AccessConnection
    Set rst = New ADODB.Recordset
    With rst
   .Open "SELECT * FROM TblUSHSFileStatus", conn, adOpenDynamic, adLockOptimistic
   .AddNew
   .Fields("Status").value = NewData
   .Fields("Lastupdate").value = Date
   .Fields("ModifyRecord").value = fOSUserName
  .Update
  ' .Requery
  .Close

MsgBox "File status has been updated"

Response = acDataErrAdded

End With

End If

'Exit_Provider_NotInList:
' '      Exit Sub
Err_Provider_NotInList:
       ' An unexpected error occurred, display the normal error message.
  '     MsgBox Err.Description
       ' Set the Response argument to suppress an error message and undo
       ' changes.
   '    Response = acDataErrContinue
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You have remarked out your exit and error processing code, why did you do that?

Aside from the exit/error processing code, I don't see anything here that would cause your main form to move to the next record after processing this code.

Are you sure there isn't something else going on here, after this code is processed?