cannot add record; primary key not in recordset

[pbuethe]

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:

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:

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:

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]

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]

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]

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]

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]

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.