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

Safaa

Safaa

Member
Local time
Tomorrow, 01:28
Joined
Jan 3, 2024
Messages
59
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
 

Attachments

any one can help me for do this?
 
arnelgp said:
see the Open Event of the sub-report.
Click to expand...
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.
 
Last edited:
change the code of the Open event of the subform:
Code: 
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
 
arnelgp said:
change the code of the Open event of the subform:
Code: 
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
Click to expand...
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
 
avoid 0, there is no such thing as Selecting 0 records.
Safaa said:
When I put in field number 1, two records appears
Click to expand...
can you show a screenshot of the 2 records?
 
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
 
where are the 2 records?
 
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.
 
arnelgp said:
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.
Click to expand...
 

Attachments

  • Screenshot_1.png
    Screenshot_1.png
    771 bytes · Views: 17
I think he counts all records with the same date as one record
 
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
 
Safaa said:
I think he counts all records with the same date as one record
Click to expand...
manually change TH_no in your table and run your report.
does it still show 2 records?
 
arnelgp said:
change the code of the Open event of the subform:
Code: 
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
Click to expand...
First of all, sir, I thank you for following me and helping me. When I put this part in the code(Me.Visible = Me.HasData), the sup report disappears if there is data. I also want it to disappear if there is no data (records) and its height is 0.
 
maybe change the code to this:
Code: 
Private Sub Report_Open(Cancel As Integer)
    Dim sql As String
    Dim db As DAO.Database
    
    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
   
   Set db = Currentdb
   With db.OpenRecordset(sql, dbOpenSnapshot)
       Me.Visible = Not (.Bof And .Eof)
        If Me.Visible Then
            Me.Height = 1440
        Else
            Me.Height=0
        End If
        .Close
    End With

End Sub
 
With db.OpenRecordset(sql, dbOpenSnapshot) GIVE ERROR HERE
 
Safaa said:
With db.OpenRecordset(sql, dbOpenSnapshot) GIVE ERROR HERE
Click to expand...
It's more helpful to share the exact error, both the error number and error description, please.
 
show us what is the error number and description.
you might try this:
Code: 
Private Sub Report_Open(Cancel As Integer)
    Dim sql As String
    Dim db As DAO.Database
    
    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)< #" & Format$(Forms!CBC_frm!vdate,"mm/dd/yyyy") & "#) And ((CBC_tbl.Code)=" & Forms!CBC_frm!Code & ")) ORDER BY CBC_tbl.Tdate DESC;"
   
    Me.RecordSource = sql
   
   Set db = Currentdb
   With db.OpenRecordset(sql, dbOpenSnapshot)
       Me.Visible = Not (.Bof And .Eof)
        If Me.Visible Then
            Me.Height = 1440
        Else
            Me.Height=0
        End If
        .Close
    End With

End Sub
 
You must log in or register to reply here.

Similar threads

G
Set Subreport Control Properties With VBA
Replies
14
Views
469
DHookom
DHookom
amorosik
How to build a budget sheet report with side-by-side sections
Replies
17
Views
769
amorosik
amorosik
Safaa
How to add space between lines in a report through a value in a field in the settings table
2
Replies
31
Views
590
sonic8
S
A
Solved Different report formats appear for the same database on different PCs
Replies
11
Views
337
CJ_London
C
zelarra821
Change the origin of multiple subreports in the same report
Replies
12
Views
778
zelarra821
zelarra821

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom