By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

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

P: n/a
PW
Hi,

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

tblDailyItinerary
tblDailyMeals

I have a form that writes a record to tblDailyItinerary 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
tblDailyItinerary".

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.OpenRecordset("tblDailyMeals")

With rst

.addnew

!ItineraryID = Me.txtItineraryID
!mealid = Me.txtMealId
!description = Me.txtDescription

.Update

End With
-paulw
Nov 11 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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 ***
Nov 12 '08 #2

P: n/a
PW
On 12 Nov 2008 00:04:03 GMT, Rich P <rp*****@aol.comwrote:
>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 (tblDailyIntinerary). I get
that error message when trying to write to the child
table(tblDailyMeals). 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 ***
Nov 12 '08 #3

P: n/a
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 ***
Nov 12 '08 #4

P: n/a
PW
On 12 Nov 2008 16:12:02 GMT, Rich P <rp*****@aol.comwrote:
>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 ***
Nov 12 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.