run-time error 3201

[fascal]

I get the runtime error, 'that I cannot add or change a record because a
related record is required in table 'tblPatients'.

When I check tblPatients, the PatientID that the form is looking for is
there. Below is the code. it has been working previously. It highlights
yeloow at the rs.Update. When I pass my cursor over the PatientID and the
rs.update, the numbers are there.

Any help asap would be much appreciated.
****************************************
Private Sub Form_AfterUpdate()

If AddMark = "Yes" Then 'If a new patient was added, need to create
'a dispo record for them
If IsOpen("frmGetNewEnrollDispo") Then
Dim db As Database
Dim rs As Recordset
Dim sql As String
Dim newid As Long
Dim msg As String
Dim ttl As String

sql = "SELECT * FROM tblPatientDispos WHERE PatientID=" & Me!PatientID

Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

rs.AddNew
rs!PatientID = Me!PatientID
rs!AdmitDate = Forms!frmGetNewEnrollDispo!AdmitDate
newid = rs!DispoID
rs.Update
rs.Close

sql = "INSERT INTO tblPatientVenues (DispoID, StartDate, Venue) "
sql = sql & "VALUES (" & newid & ", #" &
Forms!frmGetNewEnrollDispo!AdmitDate & "#,"
sql = sql & Forms!frmGetNewEnrollDispo!Venue & ")"

DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

msg = ""
If Me.ChooseStatus = "Inactive Patients" Then
msg = "The patient you just added is active. The patient's
record will not be available again until you " & _
"select 'Show active patients' or 'Show all patients'."
End If
If msg <> "" Then
ttl = "Patient not available in this view"
DoCmd.Beep
MsgBox msg, vbInformation + vbOKOnly, ttl
End If

End If
AddMark = "No"
DoCmd.Close acForm, "frmGetNewEnrollDispo"
End If

Dim Dummy As Integer
Dummy = DisableSave()

 

[Wayne Morgan]

I suspect that since you've just created the new patient, that it may not
have been written to the table yet. This may just be a timing problem. As
you mentioned, by the time you get the error and go to the table, the value
is there. Before you open the RecordSet try the line:

DbEngine.Idle dbRefreshCache

Also, there is a more efficient way for the following:

DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

Instead of these 3 lines, try

db.Execute sql, dbFailOnError

This will run without the "do you want to..." prompt, but will return an
error if the statement fails to complete. It also saves having to remember
to turn Warnings back on.

 

[fascal]

Hi,

Thank you for the assistance. It's still not working. It had been working
a week ago. So, I'm trying to think about if somewthing else in the
environment has changed that could affect the app.

fascal

 

[fascal]

One more question: I placed the PatientID from the tblPatient into the
tblPatientDispo table. I see the DispoID that got created without a
PatientID. When I attempt this, I get the message that I have to create a
PatientID in tblPAtient before doing this.

What do you think?

fascal

 

[Wayne Morgan]

If the tables are all linked, this could be the problem. The linked field in
a one-to-many relationship has to have a value on the one side before you
use the value on the many side.

 

[fascal]

Hi,

They aren't physically linked.

If the tables are all linked, this could be the problem. The linked field in
a one-to-many relationship has to have a value on the one side before you
use the value on the many side.

 

[Wayne Morgan]

Have you done a Compact and Repair?

"The tables aren't 'physically' linked" -- I don't understand this
statement.

If the tables aren't linked in the Relationships window, how are you
enforcing Referential Integrity? Also, if they aren't linked, there would be
no reason to need a value in the other table prior to inserting one in the
current table.

 

[fascal]

HI,

I did a compact and repair. I do get a new error message, on a new spot:

Set rs = db.OpenRecordset(sql, dbOpenDynaset)
The rs is now equal to nothing.

fascal

 

[fascal]

Hi,

I decided to rewrite the code from scratch in a fresh database. This seems
to have stopped the errors.

Thank you for your assistance. I can definitely use the code you showed me
in other areas.