need help! Append to table from the working form

[Niranjeen]

I have an open form, where the combo box gathers data from a 'Traveldetail'. If the user doesn't find the item in the combobox, he should enter the new item in the combo box, where the notinlist is triggered and should allow the user to add the new item in the 'Traveldetail'.

code:
Private Sub Destination_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
' Return Control object that points to combo box.
Set ctl = Me!Destination
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
DoCmd.OpenForm [TravelDetails], acFormDS, , , acFormAdd
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub


i am not able to do this. I tried many option and failed. please help!

 

[ansentry]

I am using Access 2003.

Try this code (it's not mine I just have it in a sample - it works fine).

Change the name of the table and field to suit yours.
Change the name of the combo box to suit yours.

If you still have problem I will post the sample for you.

Private Sub cboUnbound_NotInList(NewData As String, Response As Integer)
    Dim cnn As New ADODB.Connection
    Dim strSQL As String
    Dim bytResponse As Byte
    
    Set cnn = CurrentProject.Connection
    bytResponse = MsgBox("Do you want to add this new item " _
    & "to the list?", vbYesNo, "New Item Detected")
    If bytResponse = vbYes Then
    strSQL = "INSERT INTO [B]tblColors(Colors)[/B] VALUES('" _
    & NewData & "')"
    Debug.Print strSQL
    cnn.Execute strSQL
    Response = acDataErrContinue
    Me![B]cboUnbound.Undo[/B]
    End If
    Me.[B]cboUnbound[/B].Requery
End Sub

 

[Niranjeen]

Hi John A:
Thank you for your assistance.
I couldn't use your code. I modified select option and used the existing code. Not very satisfied with it. I am still looking a way to add the item <New Data> in the combo box rather than double clik on the combo box.

Will still look around for that code.

 

[ansentry]

Why do you have to double click to add an item to the list?

I am still looking a way to add the item <New Data> in the combo box rather than double click on the combo box.

Have a look at the attached sample, just type the new data into the combo and it will ask you if you want to add it to the list, select Yes and that's it.