Hi I placed the following code in the not in list event i would like to stop a problem. Currently on my form the user enters text in the field then decides no then don't want to save this text and clicks on the cancel button on my form.
The message would you like to add the text to the combo box is displayed.
if the user clicks on cancel they are returned to the main form. Now there is another cancel button on the main form used to close it. If the user first clicks outside the form then the cancel button without selecting a value already within the list the following error message is displayed 3022 run time error a duplicate value will be created in table etc.
First of all i tried to set the combo value to null but then i realised it is has the required field set within the table design and this also generates an error message saying a value must be entered. Does any one know how i could handle these two errors?
Private Sub cboStatusName_NotInList(NewData As String, Response As Integer)
'Provides user with a choice of adding a value to the commo box or not
Dim blnOK As Boolean 'Represents the ok button for the message box displayed.
Dim strMessage As String ' holds new message value
Dim dbshos As Database
Dim rstStatus As DAO.Recordset 'the name of this value can be anything
strMessage = "Would you like to add '" & NewData & _
"' to this list?" 'This is the question being asked.
blnOK = Confirm(strMessage) ' This calls the global message Confirm
'blnok represents the ok return value
'Open the table and add the NewData value
Set dbshos = CurrentDb 'The name of the database to be modified
Set rstStatus = dbshos.openrecordset("tlkPaymentStatus") ' This is the table
rstStatus.AddNew
rstStatus!StatusName = NewData 'This is the field in table that will contain the new data
rstStatus.Update
Response = acDataErrAdded ' requery the list
If blnOK = False Then
Response = acDataErrContinue
End If
End Sub
The message would you like to add the text to the combo box is displayed.
if the user clicks on cancel they are returned to the main form. Now there is another cancel button on the main form used to close it. If the user first clicks outside the form then the cancel button without selecting a value already within the list the following error message is displayed 3022 run time error a duplicate value will be created in table etc.
First of all i tried to set the combo value to null but then i realised it is has the required field set within the table design and this also generates an error message saying a value must be entered. Does any one know how i could handle these two errors?
Private Sub cboStatusName_NotInList(NewData As String, Response As Integer)
'Provides user with a choice of adding a value to the commo box or not
Dim blnOK As Boolean 'Represents the ok button for the message box displayed.
Dim strMessage As String ' holds new message value
Dim dbshos As Database
Dim rstStatus As DAO.Recordset 'the name of this value can be anything
strMessage = "Would you like to add '" & NewData & _
"' to this list?" 'This is the question being asked.
blnOK = Confirm(strMessage) ' This calls the global message Confirm
'blnok represents the ok return value
'Open the table and add the NewData value
Set dbshos = CurrentDb 'The name of the database to be modified
Set rstStatus = dbshos.openrecordset("tlkPaymentStatus") ' This is the table
rstStatus.AddNew
rstStatus!StatusName = NewData 'This is the field in table that will contain the new data
rstStatus.Update
Response = acDataErrAdded ' requery the list
If blnOK = False Then
Response = acDataErrContinue
End If
End Sub
