Cannot add or change a record - after using Not in List procedures

[MSG56]

In my recipe database I have a subform where I add new ingredients to a recipe. The subform has an IngredientID field that is related to an Ingredients table. I want to allow the user to add new ingredients on the fly and I'm using the Not in List event to fire the sub routines. Everything seems to be working correctly, i.e the AddIngredient form opens, I can add the new ingredient, close the AddIgredient form, select the new ingredient in the subform field, and move to the next field in the subform. However, when I try to go to a new record I get a pop-up stating "You cannot add or change a record because a related record is required in table "Ingredients".



When I go into the Ingredients table I can clearly see that the newly added ingredient is there, so why the error message?

[ItsMe]

Simply, having a record is not enough. You need a value in the appropriate field with the Referential Integrity rule you established within the Relationship Window.

[MSG56]

Simply, having a record is not enough. You need a value in the appropriate field with the Referential Integrity rule you established within the Relationship Window.

I'm not experienced enough to know exactly what you are saying, but I think you've given me something I can work with. I'll try to figure it out tomorrow. Thanks for the help.

[MSG56]

So I've played with this for a while and I'm obviously doing something wrong, but I can't figure out what it is. Here are some screen shots showing the issue:

So here I'm adding an ingredient that isn't in the list.



Here's the code that got me here:

Private Sub Ingredient_NotInList(NewData As String, Response As Integer)    Dim ButtonClicked
    ButtonClicked = MsgBox("This ingredient isn't in the list.  Do you want to add """ & NewData & """ to the list?", vbYesNo)
    
    If ButtonClicked = vbNo Then
    
        'Cancel the edit.
        Ingredient.Undo
        
        'Tell Access not to show the error message.
        Response = acDataErrContinue
    
    Else
        
        'Tell Access not to show the error message.
        Response = acDataErrContinue
        
        'Open the AddIngredient form.
        DoCmd.OpenForm "AddIngredient", , , , , acDialog, NewData
        
        'Now, that the new ingredient has been added to the ingredients table,
        'cancel the edit to refresh the list.
        Ingredient.Undo
               
        'Refresh the list.
        Ingredient.Requery
                
        'Find the new Ingredient.
        Ingredient = DLookup("IngredientID", "Ingredients", "Ingredient = '" & NewDate & "'")
        'Refresh the Ingredient
            
    End If
    
End Sub

Next, after responding "Yes" the AddIngredient form opens and already has "aaww" in the ingredient field.



After clidking the "save and close form" button the AddIngredient form closes and the new ingredient is in the list.



After selecting "aaww" from the list I tab to the next fields and add a quantity and unit. But once I tab out of the Comments field I get the following:



At this point this is what is in the Ingredients table:



When I select help from the error message this is what it says: 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.

However, in my case I am trying to add a record to the "one" side. (I would show the relationships, but I'm only allowed five attachments to a post.)

I'm really stuck on this.

[ItsMe]

...and add a quantity and unit. But once I tab out of the Comments field...

Are you adding the Ingredient's Primary Key Field value (IngredientID '245') to the Foreign Key Field in the table where the Comments field resides, before tabbing out of the Comments field?

[MSG56]

Are you adding the Ingredient's Primary Key Field value (IngredientID '245') to the Foreign Key Field in the table where the Comments field resides, before tabbing out of the Comments field?

This helped to resolve my issue. It turns out that I was saving the list index item number instead of the IngredientID like I thought I was doing. Thanks for all the help. I'm sure I'll be back for more.

[ItsMe]

Glad you were able to get things resolved.