cannot add record; primary key not in recordset (1 Viewer)

pbuethe

Returning User
Local time
Today, 16:47
Joined
Apr 9, 2002
Messages
210
This error message “cannot add record; primary key for table 'tlkpReview' not in recordset” appears on selecting from combobox cboDecision when entering a new record. This has been working with the table in Access (in the previous version of this database), but the error started appearing when we moved the backend to SQL Server. The code is as follows:

Code:
Private Sub cboDecision_AfterUpdate()

If IsNull(Me.[cboCaseID]) Then
  MsgBox " Please select a case first."
  Me.[cboDecision] = Null
  Exit Sub
Else
  Me.txtLevel = "PRA"
  Me.[txtDecision] = Trim(Me.[cboDecision])
  Me.ReviewDate = Now
  Me.[ReviewStaff] = Me.[txtUser]
  Me.Refresh
  If Me.cboDecision.Value = "Referral" Then
     Me.cmdOpenReferral.Visible = True
  Else
     Me.cmdOpenReferral.Visible = False
  End If
  
End If

End Sub

The error message occurs on the line Me.txtLevel = “PRA”. The primary key of tlkpReview is CaseNbr + ReviewLevel. CaseNbr is selected from another combobox on the form. The control txtLevel is bound to ReviewLevel. The record source of the form is qryCases:

Code:
SELECT qrySearchFields.CaseNbr, qrySearchFields.SampleNbr, qrySearchFields.Provider, qrySearchFields.PatientName, qrySearchFields.MedRecNbr, qrySearchFields.MedicaidID, qrySearchFields.Gender, qrySearchFields.DOB, qrySearchFields.AdmitDate, qrySearchFields.DischDate, qrySearchFields.ProviderNbr, qrySearchFields.ProviderName, qrySearchFields.CUID, qrySearchFields.ReviewLevel, qrySearchFields.Outcome, qrySearchFields.ReviewDate, qrySearchFields.ReviewStaff, qrySearchFields.Password, qrySearchFields.FileName
FROM qrySearchFields
WHERE (((qrySearchFields.SampleNbr)=[Forms]![frmPRAReview]![cboSampleNbr]) AND ((qrySearchFields.ProviderNbr)=[Forms]![frmPRAReview]![cboProvider]));

qrySearchFields:
Code:
SELECT tblWkshtHeader.CaseNbr, tblWkshtHeader.SampleNbr, tblWkshtHeader.Provider, tblWkshtHeader.PatientName, tblWkshtHeader.MedRecNbr, tblWkshtHeader.MedicaidID, tblWkshtHeader.Sex AS Gender, tblWkshtHeader.DOB, tblWkshtHeader.AdmitDate, tblWkshtHeader.DischDate, tblWkshtHeader.ProviderNbr, tblWkshtHeader.ProviderName, tblWkshtHeader.CUID, tblWkshtHeader.Password, tlkpReview.ReviewLevel, tlkpReview.Outcome, tlkpReview.ReviewDate, tlkpReview.ReviewStaff, tlkpReview.DataEntry, tlkpReview.EntryDate, tblCaseList.FileName, tlkpReview.EntryStaff, tlkpReview.LetterSent, tlkpReview.LetterDate, tlkpReview.LetterStaff
FROM (tblWkshtHeader LEFT JOIN tlkpReview ON tblWkshtHeader.CaseNbr = tlkpReview.CaseNbr) LEFT JOIN tblCaseList ON tblWkshtHeader.CaseNbr = tblCaseList.CaseID;
Thanks for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:47
Joined
Oct 29, 2018
Messages
21,358
Hi. I think the error means you need to include the primary key or add one, if you don't have one, because without it, I am guessing the recordset is read only.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:47
Joined
Feb 28, 2001
Messages
27,001
If there is a parent/child relationship here and you are adding a child record, then your problem is relational integrity, which will not allow you to create an orphan record. All child records must have corresponding parents.

If that is NOT what you were doing, then I'm not so clear on that error.
 

missinglinq

AWF VIP
Local time
Today, 16:47
Joined
Jun 20, 2003
Messages
6,423
Hi. I think the error means you need to include the primary key or add one, if you don't have one, because without it, I am guessing the recordset is read only.

That's my understanding, too. Primary Keys, or at least Unique Indexes, must be defined for the Tables in SQL Server...and the PK must be part of the RecordSource of the Form...or the Form will be Read-Only.

Linq ;0)>
 

pbuethe

Returning User
Local time
Today, 16:47
Joined
Apr 9, 2002
Messages
210
I think I solved the problem. I added the rest of the fields from tlkpReview to the query grids (including CaseNbr) and then the new record was added without error. Thanks.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:47
Joined
Feb 28, 2001
Messages
27,001
By the way, pbuethe - your signature line is something we used to say all the time with our Digital Equipment Corporation computers. And hoo, dogies, did we ever have a lot of features.
 

Users who are viewing this thread

Top Bottom