Error: “primary key not found” of Child form when loading Parent form (1 Viewer)

MilaK

Registered User.
Local time
Today, 14:01
Joined
Feb 9, 2015
Messages
285
Hello,

Form “frm_sample_report” is the main unbound form. It has a combobox “cmbSamples” that holds all sample names for a project indicated in “txt_Run_Name” text box.

“frm_report_comments” is the middle form that has a bound subform “frm_report_variants”. These two forms are not linked because I wanted to filter “frm_report_comments” based on “Current Event” of the “frm_report_variants”(as the user scrolls through each record). Please see the attached screenshot of the form setup for clarification.

The “After update” event of the combobox “cmbSamples” is set up to filter records on child form “frm_report_variants”.

I would like the parent form to display the first item in the combobox upon loading. The child form should be filtered to display variants for that sample (first item in “cmbSamples”). I get an error message that “primary key can’t be null”. Please suggest how to set this up correctly.

Thank you,

Mila
Parent Form Code:
Code:
 Private Sub cmbSamples_AfterUpdate()
 
 Dim temp_rst1 As Recordset
 Dim db As Database
 Dim qry As String
 Set db = CurrentDb
 
 qry = "SELECT tbl_Variants.ID, tbl_Variants.gene, tbl_Variants.exon, tbl_Variants.cDNA, tbl_Variants.aa_change, tbl_Samples.sample_name, tbl_Samples.run_name, tbl_Variants.variant_id " & _
 "From tbl_Samples INNER JOIN tbl_Variants ON tbl_Samples.sample_id = tbl_Variants.sample_id " & _
 "WHERE tbl_Samples.sample_name = '" & Me![cmbSamples] & "' And tbl_Samples.run_name = '" & Me![txt_Run_Name] & "'"
  Debug.Print qry
 Set temp_rst1 = db.OpenRecordset(qry)
  
   [Forms]![frm_sample_report]![frm_report_comments].[Form]![frm_report_variants].[Form].RecordSource = qry
  [Forms]![frm_sample_report]![frm_report_comments].[Form]![frm_report_variants].[Form].Requery
 [Forms]![frm_sample_report]![frm_report_comments].[Form].Requery
 
 End Sub
Code:
 Private Sub Form_Load()
 Me.txt_Run_Name = "Folder_One"
 Me.cmbSamples = Me.cmbSamples.ItemData(0)
 cmbSamples_AfterUpdate
 End Sub
Child Form Code:
Code:
 Private Sub Form_Current()
 
 Dim temp_rst1 As Recordset
 Dim db As Database
 Dim qry As String
 
 If IsNull(Me.txt_variantID.Value) = False Then 'to bypath primary key null error'
 Me.txtID = Me.txt_variantID
 Set db = CurrentDb
 qry = "SELECT variant_id, CreatedDate, LastReviewed, CreatedBy, CommentTxt FROM tbl_reportable_comments " & _
 "WHERE variant_id = '" & [Forms]![frm_sample_report]![frm_report_comments].[Form]![frm_report_variants].[Form]![txt_variantID] & "'"
  Debug.Print qry
 Set temp_rst1 = db.OpenRecordset(qry)
  Me.Parent.RecordSource = qry
 [Forms]![frm_sample_report]![frm_report_comments].[Form].Requery
 If temp_rst1.EOF Then
  [Forms]![frm_sample_report]![frm_report_comments].[Form]![txt_variant_id] = Me.txt_variantID
 
 End If
 End If
 End Sub
 

Attachments

  • Form.jpg
    Form.jpg
    67.6 KB · Views: 234

MarkK

bit cruncher
Local time
Today, 14:01
Joined
Mar 17, 2004
Messages
8,178
If you post code that generates an error, what do you think we need to know to provide further help?
 

MilaK

Registered User.
Local time
Today, 14:01
Joined
Feb 9, 2015
Messages
285
I think the Current event of the Child form is causing the error. It fires before the form record source updates and returns a null value. That's why I put IsNull(Me.txt_variantID.Value) to avoid the error message.

However, now the child form doesn't load when Parent form opens. It does populate correctly after I select a sample from the combobox but not the first sample.

I would like to the child form to populate with variants filtered by the first item in the combobox when the Parent form opens. Thanks

Code:
 Private Sub Form_Current()

Dim temp_rst1 As Recordset
Dim db As Database
Dim qry As String

If IsNull(Me.txt_variantID.Value) = False Then 'to bypath primary key null error'
Me.txtID = Me.txt_variantID
Set db = CurrentDb
qry = "SELECT variant_id, CreatedDate, LastReviewed, CreatedBy, CommentTxt FROM tbl_reportable_comments " & _
"WHERE variant_id = '" & [Forms]![frm_sample_report]![frm_report_comments].[Form]![frm_report_variants].[Form]![txt_variantID] & "'"
  Debug.Print qry
Set temp_rst1 = db.OpenRecordset(qry)
  Me.Parent.RecordSource = qry
[Forms]![frm_sample_report]![frm_report_comments].[Form].Requery
If temp_rst1.EOF Then
  [Forms]![frm_sample_report]![frm_report_comments].[Form]![txt_variant_id] = Me.txt_variantID

End If
End If
End Sub
 

MarkK

bit cruncher
Local time
Today, 14:01
Joined
Mar 17, 2004
Messages
8,178
So does that mean you solved the error in the first post? There is no question in your last post, so I'm not exactly sure what you are needing help with here. And this latest code is the Current event of the parent form, or the child form?

If the subform is not synchronizing with the parent, you may need to look at the LinkMasterFields and LinkChildFields properties of the subform control, which will be on the 'Data' tab of the subform control's property sheet in design view.
 

MilaK

Registered User.
Local time
Today, 14:01
Joined
Feb 9, 2015
Messages
285
I suppressed the error message, however, the child form doesn't load with anything when parent form opens. I would like the child form to load based on the first item of the combobox on the Parent form. Please see the screenshot in the original post.

The latest code is the Current event of the child form. As the user scrolls through each Variant record, it updates the report comments that corresponds to each Variant record.

I'm not linking the forms because it will not allow for continuous form view.

Thanks for you help, Mila
 

MarkK

bit cruncher
Local time
Today, 14:01
Joined
Mar 17, 2004
Messages
8,178
Sorry, I don't understand. My feeling is you are getting ahead of yourself with all this code. You go through a fair bit of trouble, for instance, to open a recordset you never use. You also say the parent form is unbound, but you bind it here . . .
Code:
  Me.Parent.RecordSource = qry
It just doesn't make sense.

What is the purpose of your database? What is the structure of the tables you are using? If that stuff is all OK, doing what you want here should be way simpler.
 

MilaK

Registered User.
Local time
Today, 14:01
Joined
Feb 9, 2015
Messages
285
Code:
Me.Parent.RecordSource = qry
is referring to middle form "frm_report_comments". It is the parent form of "frm_report_variants" but not the highest level parent form "frm_sample_report".

"Form Current" code is fired from the child form so I refer to middle form as
Code:
Me.Parent
and change it's RecordSource.

If there is a simpler way to accomplish what I'm looking for?

Thanks,

Mila
 

MarkK

bit cruncher
Local time
Today, 14:01
Joined
Mar 17, 2004
Messages
8,178
What is the purpose of your database? What is the structure of the tables you are using? Without understand those things, I don't know what to recommend.
 

MilaK

Registered User.
Local time
Today, 14:01
Joined
Feb 9, 2015
Messages
285
Would it help if I uploaded the database? Thanks
 

MarkK

bit cruncher
Local time
Today, 14:01
Joined
Mar 17, 2004
Messages
8,178
Yeah, sure, might save some time. :)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:01
Joined
Feb 28, 2001
Messages
27,001
Though there are many possibilities, the problem you are describing could easily be caused by a relational integrity issue in that you are trying to create an entry in the child table before you have saved the entry in the parent table. If there is such a relationship, then you cannot save the child record until a corresponding parent record exists. It is all a matter of timing.
 

Users who are viewing this thread

Top Bottom