Perfomance issue looping through recordset

[Never Hide]

Helo everyone,

I have a bit a performance issue with a loop I'm using to create a report. let me elaborate a bit

I've create a db that uses linked tables to an SQL db,which is used by an ERP program for hotel management, that a client of us uses. Because that ERP program has some problems with the native generated reports for the cleaning plan of the rooms. So we made this db to generate the reports he wanted.
One of the reports is for a projected plan for the next 30 days (after a date that he choses) that shows how many rooms need to be cleaned each day.

This code is in the Active event of the report

    Dim i As Long
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Dim pType1 As String
    Dim pType2 As String
    Dim pType3 As String
    Dim pType4 As String
    Dim X, Y As Integer
    Dim chkDate As Date
    Dim chkDate2 As Date
    Dim searchTable As String
    Dim fID As Long
    Dim checkIn As Date
    Dim checkOut As Date
    
    
    searchTable = "qLINAPRG_Diamenontes"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(searchTable)
    
    pType1 = "petsetes"
    pType2 = "sentonia"
    pType3 = "elkatharimsos"
    pType4 = "plkatharismos"
    
    chkDate = Forms![frmCleaningPlan]![tbHotelDate]
    
    
    For j = 1 To 30
        Me("tba" & j) = 0
        Me("tbb" & j) = 0
        Me("tbc" & j) = 0
        Me("tbd" & j) = 0
    Next j
            
    For Y = 0 To 29
        rs.MoveFirst
        chkDate2 = chkDate + Y
        For i = 1 To rs.RecordCount
            checkIn = rs!CHKIDATE
            checkOut = rs!CHKODATE
            fID = rs!FOLIOID
            
            varTowels = Cleaning(checkIn, checkOut, chkDate2, Plano(searchTable, fID, pType1))
            
            If varTowels = True Then
                Me("tbd" & (Y + 1)) = Me("tbd" & (Y + 1)) + 1
            End If
            
            varSheets = Cleaning(checkIn, checkOut, chkDate2, Plano(searchTable, fID, pType2))
           
            If varSheets = True Then
                Me("tbc" & (Y + 1)) = Me("tbc" & (Y + 1)) + 1
            End If
            
            varElKath = Cleaning(checkIn, checkOut, chkDate2, Plano(searchTable, fID, pType3))
            
            If varElKath = True Then
                Me("tba" & (Y + 1)) = Me("tba" & (Y + 1)) + 1
            End If
            
            varPlKath = Cleaning(checkIn, checkOut, chkDate2, Plano(searchTable, fID, pType4))
           
            If varPlKath = True Then
                Me("tbb" & (Y + 1)) = Me("tbb" & (Y + 1)) + 1
            End If

            rs.MoveNext
        Next i
    Next Y
    rs.Close

Functions Cleaning and Plano are custom functions that I have created.
the "searchTable" is a query in which I get the occupied rooms and then with using the "Cleaning" function I check if each room needs to be cleaned.


Basicaly what I do is loop through each of the 30 days, and for each day I check if any of the occupied rooms needs to be cleaned ( I hope that makes some sense )
I have a attached a screenshot to get an idea of how the report is. Now I know it's in greek but you can get an idea of the number of textboxes that I fill with the loop. There are 4 categories for cleaning(the 4 rows that you see), that's the request of the customer.

It's working correctly,what I'd like from you guys is to tell me if you have any thoughts about what i could do to make thing run a bit faster.


I hope the whole thing can make some sense to you guys