Hi,

just have another problem with one of my continuous forms...

What happens is that on a regular form, when I want to chose some data in the combo box and it is not in the combo box, I write it anyway, because the next code asks me if I want to add this entry to the table form the combo box. This is the code.

Private Sub Naam_personeelslid_NotInList(newdata As String, response As Integer)
    On Error GoTo Naam_personeelslid_NotInList_Err
    Dim intantwoord As Integer
    Dim strsql As String
    intantwoord = MsgBox("Het personeelslid " & Chr(34) & newdata & _
        Chr(34) & " staat nog niet in de lijst." & vbCrLf & _
        "Wenst U deze persoon toe te voegen?" _
        , vbQuestion + vbYesNo, "Ongeldige invoer personeelslid")
    If intantwoord = vbYes Then
        strsql = "insert into tblPersoneelsledenIKEA([Naam personeelslid]) " & _
            "values ('" & newdata & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strsql
        DoCmd.SetWarnings True
        MsgBox "Het personeelslid werd toegevoegd aan de lijst." _
            , vbInformation, "Personeelslid toegevoegd"
        response = acDataErrAdded
    Else
        MsgBox "Gelieve een personeelslid uit de lijst te kiezen." _
                , vbExclamation, "Ongeldige invoer personeelslid"
        response = acDataErrContinue
    End If
Naam_personeelslid_NotInList_exit:
    Exit Sub
Naam_personeelslid_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Fout"
    Resume Naam_personeelslid_NotInList_exit
End Sub

But I use the same code on a continous form and it does not work... Is it because the code needs to be changed, or is it impossible to do this on a continuous form?

Can anyone help me?

Instead of turning off your errors, replace:

DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

With:

CurrentDB.Execute strsql, dbFailOnError

It should report what is going on so you can fix it.



HTH RuralGuy (RG for short) acXP WinXP Pro

If I do this, it actually doesn't change a thing. I get the error:

You cannot add or change a record because a related record is required in table <name>. (Error 3201)
You tried to perform an operation that would have violated referential integrity rules for related tables. For example, this error occurs if you try to change or insert a record in the "many" table in a one-to-many relationship, and that record does not have a related record in the table on the "one" side.

If you want to add or change the record, first add a record to the "one" table that contains the same value for the matching field.

Thanks for posting back. Do you understand the error? If you need additional assistance then we'll need more details on the structure of your tables and the form.

HTH RuralGuy (RG for short) acXP WinXP Pro

I do understand the message, but what I don't understand is that the message appears, because normally it should display me a message asking if I want the add this 'unknown' data to that table...

I use the same code on another form (with regular form view) and it works fine. It displays me a message asking me if I want to add this data to the table when I try to exit the field.

What happens on the continuous form is that it shows me this error when I leave the record... instead of asking me if I want to add the new data to the related table of this combo box...

I try to understand why this happens, but I have no idea...

I admit I have not tried what you are attempting but it seems to me that it should work. If I understand correctly, you are typing in a non-existant name in the ComboBox and the NotInList code does not execute the MsgBox code. Have you single stepped the code in the debugger to see what code is actually executed? Put a break point in the NotInList and see what is happening.

HTH RuralGuy (RG for short) acXP WinXP Pro

I did not single stepped the code, because I don't know how to do this. I'm still new in the VBA...

What do you mean with this break point and how should I add it?

thanks

Do you have the ComboBox's "LimitToList" set to yes? Setting a break point is easy, just view the code and click your cursor in the left margin. A dot appears where there is a break point. I would pick the MsgBox line.

HTH RuralGuy (RG for short) acXP WinXP Pro

I forgot to put the limit to list property to 'yes'. I changed it and I get an error when the code runs... I get a 'compile error, variable not defined' error. Here is the code again(red = error)

Private Sub Naam_personeelslid_NotInList(newdata As String, response As Integer)
    On Error GoTo Naam_personeelslid_NotInList_Err
    Dim intantwoord As Integer
    Dim strsql As String
    intantwoord = MsgBox("Het personeelslid " & Chr(34) & newdata & _
        Chr(34) & " staat nog niet in de lijst." & vbCrLf & _
        "Wenst U deze persoon toe te voegen?" _
        , vbQuestion + vbYesNo, "Ongeldige invoer personeelslid")
    If intantwoord = vbYes Then
        strsql = "insert into tblPersoneelsledenIKEA([Naam personeelslid]) " & _
            "values ('" & newdata & "');"
        CurrentDb.Execute strsql, dbFailOnError
        MsgBox "Het personeelslid werd toegevoegd aan de lijst." _
            , vbInformation, "Personeelslid toegevoegd"
        response = acDataErrAdded
    Else
        MsgBox "Gelieve een personeelslid uit de lijst te kiezen." _
                , vbExclamation, "Ongeldige invoer personeelslid"
        response = acDataErrContinue
    End If
Naam_personeelslid_NotInList_exit:
    Exit Sub
Naam_personeelslid_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Fout"
    Resume Naam_personeelslid_NotInList_exit
End Sub

The first line also appear in yellow, while the line I put in red here receives a blue background in the code. Do you know what I have to change?

thanks for your help so far...

Do you have a reference to DAO?
http://www.mvps.org/access/bugs/bugs0031.htm

HTH RuralGuy (RG for short) acXP WinXP Pro