Item Not in combobox add to seperate table

[bobjackson]

I am using a combobox CboLoopRef in Form frm_equipment to add the loop ref in frm_equipment based on the loop_ref's listed in table tbl_Loop_ref. The combobox is limited to list. The following code identifies if the new value is in the list, if it is not in the list it will ask the user if they want to add it. If select "Ok" it adds the Loop Ref to the table tbl_loop_ref. If you select cancel it will undo the change but then access brings up an error dialog box "The text you entered isn't an item in the list select an item from the list or enter text that matches one of the listed items. OK" Once you click on the "Ok" The value reverts to old value and everything is fine. How do stop the message coming up. Below is the code.

Private Sub CboLoopRef_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
On Error GoTo Err_CboLoopRef_NotInList
'Return Control object that points to combobox
Set ctl = Me!CboLoopRef
'Prompt user to verify they wish to add new value.
If MsgBox("Loop Ref is not on list. Add it?", vbOKCancel) = vbOK Then
'Set Response argument to indicate that data is being added
strSQL = "Insert into Tbl_Loop_Ref (Loop_Ref) Values ('" & NewData & "')"
CurrentDb.Execute strSQL
Else: Me!CboLoopRef.Undo
Response = acDataErrAdded
End If
Exit_CboLoopRef_NotInList:
Exit Sub
Err_CboLoopRef_NotInList: ' Error-handling routine.
MsgBox Err.Description
Resume Exit_CboLoopRef_NotInList: ' Resume execution at same line
' that caused the error.
End Sub

Thanks in advance

Rob



Never smile at work, they may think you're enjoying yourself too much and cut your pay.

 

[PHV]

Replace this:
Response = acDataErrAdded
with this:
Response = acDataErrContinue

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bobjackson]

PH Thanks

Regards,

Rob

Never smile at work, they may think you're enjoying yourself too much and cut your pay.

 

[bobjackson]

Found I need both response's for it to work fully

Response = acDataErrAdded for THEN

and

Response = acDataErrConitnue for Else

Never smile at work, they may think you're enjoying yourself too much and cut your pay.