How to display a specific number of records in a subreport linked to the original report with the patient code

[Safaa]

I have a report with a subreport inside it. I want to limit the number of records displayed in the subreport based on the value of a field named TH_no In a table called settings_general_tbl




For your information, this sub-report reflects the patient’s history

I want solutions, I don't want advice

 

[Pat Hartman]

I want solutions, I don't want advice

We'll make a note of that.

 

[Safaa]

any one can help me for do this?

 

[arnelgp]

see the Open Event of the sub-report.

 

[Safaa]

see the Open Event of the sub-report.

Thank you very much. I want to hide the subreport when there are no previous visits


It works well, but there are two errors. If the field value is equal to 0, the program closes suddenly. Also, if there are two visits on the same day, it is counted as one.

 

[arnelgp]

change the code of the Open event of the subform:

Private Sub Report_Open(Cancel As Integer)
    Dim sql As String
    sql = "SELECT TOP " & Nz(DLookup("TH_no", "settings_general_tbl"), 1) & _
        " CBC_tbl.Tdate, CBC_tbl.Code, CBC_tbl.hgb, CBC_tbl.hgbp, CBC_tbl.RBC, " & _
        "CBC_tbl.HCT, CBC_tbl.MCV, CBC_tbl.MCH, CBC_tbl.MCHC, CBC_tbl.RDWcv, CBC_tbl.RDWsd, " & _
        "CBC_tbl.PLT, CBC_tbl.PCT, CBC_tbl.MPV, CBC_tbl.PDW, CBC_tbl.WBC, CBC_tbl.netp, CBC_tbl.BandP, " & _
        "CBC_tbl.SegmP, CBC_tbl.lymp, CBC_tbl.monp, CBC_tbl.eosp, CBC_tbl.basp, CBC_tbl.MIDP FROM CBC_tbl " & _
        "WHERE (((CBC_tbl.Tdate)<Forms!CBC_frm!vdate) And ((CBC_tbl.Code)=Forms!CBC_frm!Code)) ORDER BY CBC_tbl.Tdate DESC;"
    
    Me.RecordSource = sql
    
    Me.Visible = Me.HasData
End Sub

 

[Safaa]

change the code of the Open event of the subform:

Private Sub Report_Open(Cancel As Integer)
    Dim sql As String
    sql = "SELECT TOP " & Nz(DLookup("TH_no", "settings_general_tbl"), 1) & _
        " CBC_tbl.Tdate, CBC_tbl.Code, CBC_tbl.hgb, CBC_tbl.hgbp, CBC_tbl.RBC, " & _
        "CBC_tbl.HCT, CBC_tbl.MCV, CBC_tbl.MCH, CBC_tbl.MCHC, CBC_tbl.RDWcv, CBC_tbl.RDWsd, " & _
        "CBC_tbl.PLT, CBC_tbl.PCT, CBC_tbl.MPV, CBC_tbl.PDW, CBC_tbl.WBC, CBC_tbl.netp, CBC_tbl.BandP, " & _
        "CBC_tbl.SegmP, CBC_tbl.lymp, CBC_tbl.monp, CBC_tbl.eosp, CBC_tbl.basp, CBC_tbl.MIDP FROM CBC_tbl " & _
        "WHERE (((CBC_tbl.Tdate)<Forms!CBC_frm!vdate) And ((CBC_tbl.Code)=Forms!CBC_frm!Code)) ORDER BY CBC_tbl.Tdate DESC;"
 
    Me.RecordSource = sql
 
    Me.Visible = Me.HasData
End Sub

It works well, but there are two errors. If the field value is equal to 0, the program closes suddenly. Also, When I put in field number 1, two records appears

 

[arnelgp]

avoid 0, there is no such thing as Selecting 0 records.

When I put in field number 1, two records appears

can you show a screenshot of the 2 records?

 

[Safaa]

Private Sub Report_Open(Cancel As Integer)
Dim sql As String
Dim thNo As Variant

thNo = Nz(DLookup("TH_no", "settings_general_tbl"), 1)

' تحقق مما إذا كانت قيمة TH_no صفر
If thNo > 0 Then
sql = "SELECT TOP " & thNo & _
" CBC_tbl.Tdate, CBC_tbl.Code, CBC_tbl.hgb, CBC_tbl.hgbp, CBC_tbl.RBC, " & _
"CBC_tbl.HCT, CBC_tbl.MCV, CBC_tbl.MCH, CBC_tbl.MCHC, CBC_tbl.RDWcv, CBC_tbl.RDWsd, " & _
"CBC_tbl.PLT, CBC_tbl.PCT, CBC_tbl.MPV, CBC_tbl.PDW, CBC_tbl.WBC, CBC_tbl.netp, CBC_tbl.BandP, " & _
"CBC_tbl.SegmP, CBC_tbl.lymp, CBC_tbl.monp, CBC_tbl.eosp, CBC_tbl.basp, CBC_tbl.MIDP FROM CBC_tbl " & _
"WHERE (((CBC_tbl.Tdate)<Forms!CBC_frm!vdate) And ((CBC_tbl.Code)=Forms!CBC_frm!Code)) ORDER BY CBC_tbl.Tdate DESC;"

Me.RecordSource = sql

' تحقق من وجود بيانات
If Me.HasData Then
Me.Visible = True
Me.Height = 1000 ' قم بتعيين ارتفاع التقرير إلى قيمة افتراضية
Else
Me.Visible = False
Me.Height = 0 ' تعيين ارتفاع التقرير إلى 0 إذا لم توجد بيانات
End If
Else
MsgBox "قيمة الحقل TH_no لا يمكن أن تكون صفر.", vbExclamation
Cancel = True ' هذا يمنع فتح التقرير
End If
End Sub

 

[arnelgp]

where are the 2 records?

 

[Safaa]

avoid 0, there is no such thing as Selecting 0 records.

can you show a screenshot of the 2 records?

 

[arnelgp]

have you check the table settings_general_tbl if TH_No field has changed to 1?
also on the db i uploaded, if you go to design view of the subreport, on its Record Source, i set it to Blank.

 

[Safaa]

have you check the table settings_general_tbl if TH_No field has changed to 1?
also on the db i uploaded, if you go to design view of the subreport, on its Record Source, i set it to Blank.

 

[Safaa]

I think he counts all records with the same date as one record

 

[DHookom]

If there are two (or more) records with the same CBC_tbl.Tdate, which one do you expect to show? You can add a tie-breaker field to your ORDER BY like:

ORDER BY CBC_tbl.Tdate, CBC_tbl.Code DESC