Query slow to run

[JBRTaylor]

I have a query within some vba code which updates a record with information from a form but it is taking about 6 seconds to run. Is there anything i could do to speed this up because it is getting slower as more and more records are being added to the database.

The query i believe is slowing it down is this:

SELECT tblCollectionItemized.HireLine, tblRateCard.DepositApply, tblCollectionItemized.DepositReturned, tblCollectionItemized.BookingActive
FROM (tblTariff INNER JOIN (tblCollection INNER JOIN (tblEquipment INNER JOIN tblCollectionItemized ON tblEquipment.KitKey = tblCollectionItemized.KitKey) ON tblCollection.HireID = tblCollectionItemized.HireID) ON tblTariff.TariffKey = tblCollection.Tarriff) INNER JOIN (tblRateCard INNER JOIN tblKitSubCat ON tblRateCard.KitKey = tblKitSubCat.CategoryID) ON (tblKitSubCat.CategoryID = tblEquipment.KitSubCategory) AND (tblTariff.TariffKey = tblRateCard.Tariff);

Here is the while vba code:

        Dim ResID As String
        Dim DepositRtn As Boolean
        
    
    txtHireLine.SetFocus
    ResID = txtHireLine.Value
 

    
    Dim Db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    
    Set Db = CurrentDb
    Set rs = Db.OpenRecordset("SELECT tblCollectionItemized.HireLine, tblCollectionItemized.BookingActive, tblCollectionItemized.KitRef, " & _
                                "tblCollectionItemized.ReturnDate, tblCollectionItemized.CheckDate, tblCollectionItemized.CheckNotes, " & _
                                "tblCollectionItemized.DepositReturned, tblCollectionItemized.UserRtned FROM tblCollectionItemized;", dbOpenDynaset)
    Set rs2 = Db.OpenRecordset("SELECT tblCollectionItemized.HireLine, tblRateCard.DepositApply, tblCollectionItemized.DepositReturned, tblCollectionItemized.BookingActive " & _
                                "FROM (tblTariff INNER JOIN (tblCollection INNER JOIN (tblEquipment " & _
                                "INNER JOIN tblCollectionItemized ON tblEquipment.KitKey = tblCollectionItemized.KitKey) ON " & _
                                "tblCollection.HireID = tblCollectionItemized.HireID) ON tblTariff.TariffKey = tblCollection.Tarriff) " & _
                                "INNER JOIN (tblRateCard INNER JOIN tblKitSubCat ON tblRateCard.KitKey = tblKitSubCat.CategoryID) ON " & _
                                "(tblKitSubCat.CategoryID = tblEquipment.KitSubCategory) AND (tblTariff.TariffKey = tblRateCard.Tariff);", dbOpenDynaset)
    
    rs.FindFirst "HireLine =" & ResID
    rs2.FindFirst "HireLine =" & ResID
    
    'Check That there is a record that matches the ResID
        If rs.NoMatch = True Then
          MsgBox "ERROR: No record found."
          Exit Sub
        End If
        
   'Check if a deposit has been taken
  If rs2![DepositApply] = True Then
    If MsgBox("Would you like to refund the deposit for this item", vbYesNo) = vbYes Then
        'update the record to show refund returned and return item.
        rs.Edit
        rs![DepositReturned] = True
        rs![BookingActive] = False
        rs![ReturnDate] = Forms!RtnLogger!txtDateReturned
        rs![CheckDate] = Forms!RtnLogger!txtDateofCheck
        rs![CheckNotes] = Forms!RtnLogger!txtReturnIssues
        rs![UserRtned] = Forms!frmBookingAdminSwitch!txtUserID
        rs.Update
        
        Set Db = Nothing
        Set rs = Nothing
         Set rs2 = Nothing
         Forms![RtnKitForm]!lstRtnSearch.Requery
        DoCmd.Close
        MsgBox "Your Item has been logged as returned" & vbNewLine & "HireLine " & ResID
        Exit Sub
    Else
        'Do Not Refund Deposit
      End If
    End If
   
    'return item
        rs.Edit
        rs![BookingActive] = False
        rs![ReturnDate] = Forms!RtnLogger!txtDateReturned
        rs![CheckDate] = Forms!RtnLogger!txtDateofCheck
        rs![CheckNotes] = Forms!RtnLogger!txtReturnIssues
        rs![UserRtned] = Forms!frmBookingAdminSwitch!txtUserID
        rs.Update
   
    Set Db = Nothing
    Set rs = Nothing
    Set rs2 = Nothing
    Forms![RtnKitForm]!lstRtnSearch.Requery
    DoCmd.Close
    MsgBox "Your Item has been logged as returned" & vbNewLine & "HireLine " & ResID

 

[Galaxiom]

Much of the time would be wasted returning records you don't need. Instead of FindFirst include the required HireLine in the SQL that generates the recordset.