Cannot add or update record

  • Thread starter Thread starter azu_daioh
  • Start date Start date
A

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
 
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)"
Click to expand...

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.
Click to expand...

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
Click to expand...

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
__________________________________________

[email protected] said:
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
Click to expand...
 
Back
Top