Hi All

I have an unbound form called form1 which has a series of combo boxes on them, for this problem i am trying to allow the user to add data to a combo box via the NotInList Method. i don't wish to just add the new record to the table as there are multiple fields the user shoud fill in so i am opening a second form (if the NotInList occurs) "frmColours" to allow the user to add the new colour.
Problem, when the user add the new data via the frmColours and closes it i get error on the accmdsaverecord code then i get an error 2118 on requery of cboColour, This code work everywhere else in the database, the only difference is this form (Form1) is unbound. I have added my code in this post.

Any Help would be great

Thanks in advance

Darren

Private Sub cboColour_NotInList(NewData As String, Response As Integer)
'Exit this sub if the combo box is cleared

    If NewData = "" Then Exit Sub


    Response = acDataErrContinue
    DoCmd.RunCommand acCmdUndo

        Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
        Msg = Msg & "Do you want to add it?"
        Style = vbYesNo + vbInformation + vbDefaultButton2 ' buttons
        Title = "New Colour!" 'define title

        Response = MsgBox(Msg, Style, Title, Help, Ctxt)
        If Response = vbYes Then ' If Yes Selected
        MyString = "Yes"
        DoCmd.OpenForm "frmColours", acNormal, , , acFormAdd, acDialog
        MsgBox "Select from the your new Colour"
        DoCmd.RunCommand acCmdSaveRecord
        Me.cboColour.Requery
        Else 'If No Selected
        MyString = "No" 'What to do
        MsgBox "Please Select A Colour From the List"


        End If
End Sub

Hi Again,

After a bit more head scratching and cursing i have a solution to which i will post in case anyone else has a simular problem, the full code for the NotInList event is

Private Sub cboColour_NotInList(NewData As String, Response As Integer)
'Exit this sub if the combo box is cleared

    If NewData = "" Then Exit Sub


    Response = acDataErrContinue
    DoCmd.RunCommand acCmdUndo

        Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
        Msg = Msg & "Do you want to add it?"
        Style = vbYesNo + vbInformation + vbDefaultButton2 ' buttons
        Title = "New Colour!" 'define title

        Response = MsgBox(Msg, Style, Title, Help, Ctxt)
        If Response = vbYes Then ' If Yes Selected
        MyString = "Yes"
        DoCmd.OpenForm "frmColours", acNormal, , , acFormAdd, acDialog
        MsgBox "Click the Drop Down List and select you new Colour"

        'Repopulate cboColour and filter list for NewData
        Dim sSubColourSource As String

    sSubColourSource = "SELECT [tblColourCodes].[ColourID],[tblColourCodes].[ColourCode],[tblColourCodes].[ColourDesc] " & _
                        "FROM tblColourCodes " & _
                        "WHERE [ColourDesc] Like '" & NewData & "*'"
    Me.cboColour.RowSource = sSubColourSource

        Else 'If No Selected
        MyString = "No" 'What to do
        MsgBox "Please Select A Colour From the List"


        End If
End Sub


The Bold text is what i added to make it work,

Cheers All

Darren
:D