Combo box NotOnList Child Error

[rproactive]

Hi all-
I have a violate ref integrity error 3201 on form with 2 combo boxes bound to 2 separate tables. When I enter a Not On List values the "master" combo box works fine but the "child" gives errors.

I am using Aceman NotOnList function.

Any help appreceated

 

[TheAceMan1]

How are ya rproactive . . .

For the child combo:
[ol][li]Post the [blue]Name[/blue] property.[/li]
[li]Post the [blue]RowSource[/blue] property.[/li]
[li]Post the the code in the [blue]On Not In List[/blue] event.[/li][/ol]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[rproactive]

Hi TheAceMan1-

For the child combo:
1. Name property: cboDepositNumber
2. RowSource property:
SELECT L_RRSub.RRSub_ID, L_RRSub.RRSubName, L_RR.RR_ID
FROM L_RR
INNER JOIN L_RRSub
ON L_RR.RR_ID=L_RRSub.RRSubRRs_IDs
WHERE (((L_RR.RR_ID)=Forms!F_DepositSlip!cboRentRollMonth));
3. code in the On Not In List event

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

If AddToList(Me, "L_RRSub", "RRSubName") Then
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!cboDepositNumber.Undo 'Optional. Restores previous text.
End If

End Sub

 

[TheAceMan1]

rproactive . . .

You have to [blue]add to the same table that supplies the fields in the rowsource[/blue]. For you this is [blue]L_RR[/blue], however your trying to add to [blue]L_RRSub[/blue]! . . . hence the error.

Since the fields you want to display in the combo reside in a different table, I'm not sure how your gonna fix this as its indicitive of a [blue]table design problem.[/blue]

Also, where more than one table is involved in the query/sql of the combo you have to be careful of adding to the child table without a corresponding record in the parent table.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[rproactive]

Thanks for your thoughts. I tryed your suggestions but to no help. Yes, there is probably a table set up problem- back to basics. So I started fresh and created 3 tables and a form that duplicates the problem:
T_Master, T_Child and T_Sum;
with form XF_ComboBox.
T_master and T_Child are related.

TABLES>>
T_Master
	M_ID (pk) AutoNumber (one side rel to T_Child)
	MName
T_Child
	C_ID (pk) AutoNumber
	CName  Text
	CMs_IDs Long Integer (many side rel to T_Master)
T_Sum
	Sum_ID (pk) AutoNumber
	SData  Text
	SMasters_IDs  Long Integer
	SChilds_IDs  Long Integer

The form XF_ComboBox has only 2 combo boxes: cboMaster and cboChild
Form’s Record Source: T_Sum

FORM ComboBox>>> 
cboMaster 
ControlSource:  SMasters_IDs 
RowSourceType:  Table/Query 
RowSource: SELECT DISTINCT T_Master.M_ID, T_Master.MName 
FROM T_Master 
ORDER BY T_Master.MName;
BoundCol:  1  
ColCount:  2  
ColWidths:  0;1

Event After Update
Private Sub cboMaster_AfterUpdate()
Refresh
End Sub

Event On Not In List
Private Sub cboMaster_NotInList(NewData As String, Response As Integer)
'Basic Module NotInList using Aceman AddToList..
    If AddToList(Me, "T_Master", "MName") Then
        Response = acDataErrAdded
    Else
     Response = acDataErrContinue
       Me!cboMaster.Undo 'Optional. Restores previous text.
    End If
End Sub

FORM ComboBox>>>
cboChild
ControlSource:  SChilds_IDs
RowSourceType:  Table/Query 
RowSource: SELECT DISTINCT T_Child.C_ID, T_Child.CName, T_Child.CMs_IDs FROM T_Child
WHERE (((T_Child.CMs_IDs)=Forms!XF_ComboBox!cboMaster)) 
ORDER BY T_Child.C_ID;
BoundCol:  1  
ColCount:  3  
ColWidths:  0;1;0
EVENTs: On Not In List
Private Sub cboChild_NotInList(NewData As String, Response As Integer)
'Basic Module NotInList using Aceman AddToList..
    If AddToList(Me, "T_Child", "CName") Then
        Response = acDataErrAdded
    Else
     	Response = acDataErrContinue
       	Me!cboChild.Undo 'Optional. Restores previous text.
    End If
End Sub

If enter item not on list in cboMaster new value is added without problem
But if add a new item in cboChild get Run time error 3201 “You cannot add or change a record because a related record is required in table T_Master"

The reason I am using T_Sum is since the form can have only one table as the control source. Maybe there is a better way? Thanks for any help.

 

[TheAceMan1]

rproactive . . .

I stated:

[blue]Also, where more than one table is involved in the query/sql of the combo you have to [purple]be careful of adding to the child table without a corresponding record in the parent table.[/purple][/blue]

This true even for one table, if that table is a child! Looking at a form with subform:

[blue]You can't add a record to a subform without 1st adding a record to the parent.[/blue]

I'm at work but will do all I can to get you an answer before this evening . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884: