Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: Adding new record through combo box doesn't work

    Hi everyone!
    I have two tables.
    Table1 has got three fields: filmcode (AutoNumber), title (Text), genre (Text)
    Table2 has got one field: genre (Text)
    The two tables have relationship between them through the genre fields.
    I have a form in which I'm using a combo box with records from Table2 to fill in Table1, but if I type in a record that is not included in the combo box I want it to be added in Table2 as a new record.
    I'm using this code but it doesn't work.
    Code:
    Private Sub Combo1_NotInList(NewData As String, Response As Integer)
    Dim strsql
        MsgBox "This record does not exist.  Creating new record."
        strsql = "Insert Into Table2 ([genre]) values ('" & NewData & "')"
        'MsgBox strsql
        CurrentDb.Execute strsql ', dbForwardOnly
        Response = acDataErrAdded
    End Sub
    It says " You cannot add or change a record because a related record is required in table 'Table2'. "
    What code should I use instead of this one?
    Thank you in advance!

  2. #2
    Join Date
    Nov 2005
    Posts
    113
    The RowSource Property in the Combo1 box is SELECT Table2.genre FROM Table2 ORDER BY Table2.genre;

    Private Sub Combo1_NotInList(NewData As String, Response As Integer)

    Dim strSql As String
    Dim i As Integer
    Dim Msg As String

    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "This record does not exist." & vbCr & vbCr
    Msg = Msg & " Creating new record?"

    i = MsgBox(Msg, vbQuestion + vbYesNo)
    If i = vbYes Then
    strSql = "Insert Into Table2 ([genre]) " & _
    "values ('" & NewData & "');"
    CurrentDb.Execute strSql, dbFailOnError
    Response = acDataErrAdded
    Else
    Response = acDataErrContinue
    End If
    end Sub

  3. #3
    Join Date
    Apr 2011
    Posts
    3
    Tank you for the code!
    Unfortunately it still doesn't work. It still says that " You cannot add or change a record because a related record is required in table 'Table2'. " I tried every method I know but still nothing. I added in the Control Source the genre field from Table1 because this is where I want to add the new genre record after it's added to the genre field in Table2. These 2 fields have Referential Integrity between them. What might help to solve the problem?
    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •