[AccessD] referential integrity in a relationship

How can Access tell which is the correct record in Table A. 

RI only works with One-To-Many, not Many-To-Many.

If using 2007 or 2010, you can probably do it with Table Constraints.

If using an earler version,  you can't do it at table level.  You can do it in your Form validation 

Something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Dcount("Fld1","Table A","Fld1 = '" &  Fld1 & "'") = 0 THEN
	Cancel = True
	Msgbox "No matching record for " & Fld1 & " in Table A".
End IF
End Sub



On 27 Mar 2012 at 5:38, Benson, William (GE Global Re wrote:

> I have two tables, A and B.
> 
> Table A has field Fld1 which is indexed but non-unique, so has records like:
> 
> X
> X
> X
> Y
> Z
> Z
> Z
> Z
> 
> Table B has a foreign key to Fld1, and has values like:
> 
> X
> X
> Z
> Z
> Z
> 
> I don't want to be able to add a value "P" in table B which does not exist in Table A.
> 
> I do not seem to be able to set up a relationship between these with referential integrity. 
> 
> Is it because of the repetition within Fld1 in Table A?
> 
> If so, it would seem that the remedy is going to be to create a primary key in Table A and a foreign key to THAT in Table B instead of (or in addition to) the one on Fld2.
> 
> But oh, I really don't want to do this.
> 
> Is there maybe some other problem, or is it the repetition causing this issue, as I suspect?
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>