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.AccessConne ction
Set rst = New ADODB.Recordset
With rst
.Open "SELECT * FROM TblUSHSFileStatus", conn, adOpenDynamic, adLockOptimistic
.AddNew
.Fields("Status").value = NewData
.Fields("Lastupdate").valu e = Date
.Fields("ModifyRecord").va lue = 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
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.AccessConne
Set rst = New ADODB.Recordset
With rst
.Open "SELECT * FROM TblUSHSFileStatus", conn, adOpenDynamic, adLockOptimistic
.AddNew
.Fields("Status").value = NewData
.Fields("Lastupdate").valu
.Fields("ModifyRecord").va
.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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?