Cannot add or update record

[azu_daioh]

I'm getting this error message on one of the database but not on the
other. "You cannot add or change a record because a related record is
required in table <name>. (Error 3201)"

Both databases have similar relatioships/design so I dont know why
it's working on one and not the other.


here's the code:
Private Sub License__AfterUpdate()
Dim xLic As String


xLic = Nz(DLookup("[License#]", "Doctor Basic Info", _
"[License#] = '" & Me.[License#] & "'"), "0")


If xLic = "0" Then
MsgBox "Doctor's license# is currently not in the database." &
Chr(13) _
& "Please complete the 'Basic Doctor Info' before
proceeding.", _
vbCritical, "Doctor Record Not Found"


DoCmd.OpenForm "Doctor Table", acNormal
DoCmd.GoToRecord , , acNewRec
Forms![Doctor Table]![License#] = Forms![Add New Monthly
Payment Record]![License#]


End If


End Sub


In the database where the code works I have this relationship
[Doctor Basic Info] 1-Many [Monthy Activity]


[Monthly Activity] is the record source for Forms![Add New Monthly
Payment Record]
[Doctor Basic Info] is the record source for Forms![Doctor Table]


In the database where the code does not work:
[tbl_Doctors] 1-Many [tbl_ClaimDetails]


And this is the code that doesnt work:
Private Sub drLicense_AfterUpdate()
Dim xLic As String


xLic = Nz(DLookup("[drLicense]", "tbl_Doctor", _
"[drLicense] = '" & Me.[drLicense] & "'"), "0")


If xLic = "0" Then
MsgBox "Doctor's license# is currently not in the database." &
Chr(13) _
& "Please complete the 'Basic Doctor Info' before
proceeding.", _
vbCritical, "Doctor Record Not Found"


DoCmd.OpenForm "frm_Doctor", acNormal
DoCmd.GoToRecord , , acNewRec
Forms![frm_Doctor]![drLicense] = Forms![frm_ClaimDetails]!
[License#]


End If
End Sub
--------


Any idea? It says to update the table with (1) one relationship but
why does it work on the other database?

Thanks,
Sharon

 

[Tom Wickerath]

Hi Sharon,

I'm getting this error message on one of the database but not on the
other. "You cannot add or change a record because a related record is
required in table <name>. (Error 3201)"

This is a referential integrity (RI) error. In other words, you are
attempting to add a record to a child table that does not have a matching
parent record. Because you have previously created a relationship with
enforced RI, the JET database engine says 'no-can-do' in it's own special way.

Both databases have similar relationships/design so I dont know why
it's working on one and not the other.

Do both databases include relationships with enforced RI? First, make a
copy of the database that your code is not working in. You can possibly
narrow it down by removing the Enforce RI option on one relationship at a
time, that the child table is involved in, while testing your code each time.
You might find, for example, that a numeric foreign key field has a default
value of 0, and that this field is not being properly updated to match a
parent record.

Also, you should be able to simplify this part of your code:

DoCmd.OpenForm "Doctor Table", acNormal
DoCmd.GoToRecord , , acNewRec

Here, you are apparently opening a bound form named "Doctor Table", and then
navigating to a new record. Why not just open the form to a new record
directly? For example:

DoCmd.OpenForm "Doctor Table", DataMode:=acFormAdd

You could even pass the new license # into the Doctor Table form using the
optional OpenArgs argument, although the method you are using should work
fine as well.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I'm getting this error message on one of the database but not on the
other. "You cannot add or change a record because a related record is
required in table <name>. (Error 3201)"

Both databases have similar relationships/design so I dont know why
it's working on one and not the other.


here's the code:
Private Sub License__AfterUpdate()
Dim xLic As String


xLic = Nz(DLookup("[License#]", "Doctor Basic Info", _
"[License#] = '" & Me.[License#] & "'"), "0")


If xLic = "0" Then
MsgBox "Doctor's license# is currently not in the database." &
Chr(13) _
& "Please complete the 'Basic Doctor Info' before
proceeding.", _
vbCritical, "Doctor Record Not Found"


DoCmd.OpenForm "Doctor Table", acNormal
DoCmd.GoToRecord , , acNewRec
Forms![Doctor Table]![License#] = Forms![Add New Monthly
Payment Record]![License#]


End If


End Sub


In the database where the code works I have this relationship
[Doctor Basic Info] 1-Many [Monthy Activity]


[Monthly Activity] is the record source for Forms![Add New Monthly
Payment Record]
[Doctor Basic Info] is the record source for Forms![Doctor Table]


In the database where the code does not work:
[tbl_Doctors] 1-Many [tbl_ClaimDetails]


And this is the code that doesnt work:
Private Sub drLicense_AfterUpdate()
Dim xLic As String


xLic = Nz(DLookup("[drLicense]", "tbl_Doctor", _
"[drLicense] = '" & Me.[drLicense] & "'"), "0")


If xLic = "0" Then
MsgBox "Doctor's license# is currently not in the database." &
Chr(13) _
& "Please complete the 'Basic Doctor Info' before
proceeding.", _
vbCritical, "Doctor Record Not Found"


DoCmd.OpenForm "frm_Doctor", acNormal
DoCmd.GoToRecord , , acNewRec
Forms![frm_Doctor]![drLicense] = Forms![frm_ClaimDetails]!
[License#]


End If
End Sub
--------


Any idea? It says to update the table with (1) one relationship but
why does it work on the other database?

Thanks,
Sharon