Table Relationship problem (A2K3) "a related record is required..."

[PW]

Hi,

I set up a relationship between two tables with the itineraryid fields
in both tables:

tblDailyItinera ry
tblDailyMeals

I have a form that writes a record to tblDailyItinera ry that updates
the autonumber field itineraryid. I then bring up a subform which
writes a record to tblDailyMeals. Access tells me "You cannot add or
change a record because a related record is required in table
tblDailyItinera ry".

Well, that is false because there IS a record in that table with the
same itineraryid value. I click OK and the record gets written any
way to the 2nd table (tblDailyMeals) .

If I remove the relationship, it works just fine (no funky records).

Any ideas? The code to save the record is pretty straight forward:

Dim dbCurr_pw As Database
Dim rst As Recordset
Dim strFind_pw As String

Set dbCurr_pw = CurrentDb
Set rst = dbCurr_pw.OpenR ecordset("tblDa ilyMeals")

With rst

.addnew

!ItineraryID = Me.txtItinerary ID
!mealid = Me.txtMealId
!description = Me.txtDescripti on

.Update

End With


-paulw

[Rich P]

Re: Table Relationship problem (A2K3) "a related record is required...&quo t;

Hi Paul,

Think about it this way: one table will have a unique Itinerary (the
Primary table) and the other table (the child/detail table) will contain
multiple of the Itinerary values in a One to Many relationship (Primary
key to Foreign key). If you try to insert a record into the Child table
-- and the Itinerary value does not exist in the Primary table --
Referential Integrity will bring up that error message. Ideally, if you
violate this -- Access should not insert the record. It looks like a
DAO Recordset object can bypass Referential Integrity (RI).

If you want to maintain RI I would use Jet sql instead of DAO:

DoCmd.RunSql "Insert Into Primary(fld1, fld2, fld3) Select '" &
txtfield1 & "','" & txtfld2 & "'," & txtfld3

i = DMax("ID", "Primary")
DoCmd.RunSql "Insert Into ChildTbl(ID, fldA, fldB, fldC)
Select " i & ", " & txtA & ", '" & txtB & "', #" & txtC & "#"

The single quote ' delimiters are delimiting text values, No single
quotes are numbers, Pound # delimits Date Values.

i = DMax("ID", "Primary") is picking up the Identity value from the
Primary table which you will use as the Foreign key in the Child table.
This is only one of many ways to get your key value(s).





Rich

*** Sent via Developersdex http://www.developersdex.com ***

[PW]

Re: Table Relationship problem (A2K3) "a related record is required...&quo t;

On 12 Nov 2008 00:04:03 GMT, Rich P <rpng123@aol.co mwrote:

>Hi Paul,
>
>Think about it this way: one table will have a unique Itinerary (the
>Primary table) and the other table (the child/detail table) will contain
>multiple of the Itinerary values in a One to Many relationship (Primary
>key to Foreign key). If you try to insert a record into the Child table
>-- and the Itinerary value does not exist in the Primary table --

But it DOES exist in the primary table (tblDailyIntine rary). I get
that error message when trying to write to the child
table(tblDailyM eals). Access is telling me that there isn't a record
in the parent table for the itineraryid, which is false.

-paul

>Referential Integrity will bring up that error message. Ideally, if you
>violate this -- Access should not insert the record. It looks like a
>DAO Recordset object can bypass Referential Integrity (RI).
>
>If you want to maintain RI I would use Jet sql instead of DAO:
>
>DoCmd.RunSql "Insert Into Primary(fld1, fld2, fld3) Select '" &
>txtfield1 & "','" & txtfld2 & "'," & txtfld3
>
>i = DMax("ID", "Primary")
>DoCmd.RunSql "Insert Into ChildTbl(ID, fldA, fldB, fldC)
>Select " i & ", " & txtA & ", '" & txtB & "', #" & txtC & "#"
>
>The single quote ' delimiters are delimiting text values, No single
>quotes are numbers, Pound # delimits Date Values.
>
>i = DMax("ID", "Primary") is picking up the Identity value from the
>Primary table which you will use as the Foreign key in the Child table.
>This is only one of many ways to get your key value(s).
>
>
>
>
>
>Rich
>
>*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

Re: Table Relationship problem (A2K3) &quot;a related record is required...&quo t;

Sometimes a bit did not get set somewhere in the software (very rarely).
To fix that possibility try this: Delete the child rows of the
Itinerary with the problem - if you have any child rows. Then delete
the related Primary row in the Primary table. And start over. Insert
the Primary row first. Then try inserting 1 child row in the child
table and see if it takes it.

If that doesn't work - then do this. Create a new Primary table and a
new child table. Add your relationships. Try adding 1 primary row to
this new primary table and then try adding a child row. If that works,
then transfer all of the data over to these new tables.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[PW]

Re: Table Relationship problem (A2K3) &quot;a related record is required...&quo t;

On 12 Nov 2008 16:12:02 GMT, Rich P <rpng123@aol.co mwrote:

>Sometimes a bit did not get set somewhere in the software (very rarely).
>To fix that possibility try this: Delete the child rows of the
>Itinerary with the problem - if you have any child rows. Then delete
>the related Primary row in the Primary table. And start over. Insert
>the Primary row first. Then try inserting 1 child row in the child
>table and see if it takes it.

This seemed to have done it Rich. I removed the relationship for
the two tables, deleted all records then built back the relationship
and so far so good!

Thanks,

-paul

>
>If that doesn't work - then do this. Create a new Primary table and a
>new child table. Add your relationships. Try adding 1 primary row to
>this new primary table and then try adding a child row. If that works,
>then transfer all of the data over to these new tables.
>
>Rich
>
>*** Sent via Developersdex http://www.developersdex.com ***