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:
Child Form Code:
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
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