Avatar of francodhs
Flag for United States of America asked on

Query Current Subform Records and not Entire Recordset

To All:

Can someone help me modify my code to only run the current subform data nested in my main form?  Another member did a great job helping me with my project, but it takes too long to run the entire recordset when I'm only interested in the active data.  So, by clicking on the "Sort" button, I would only like to sort the active flights and not all flights stored in the db.

Microsoft ApplicationsMicrosoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Jeffrey Coachman

Please note that you have simply posted your database here with no explanation of how to replicate the issue...

When posting a sample db, please follow these general rules:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the database window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. Post explicit steps to replicate the issue.
13. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see/troubleshoot the issue, and if applicable, also include a Graphical representation of the Exact results you are expecting.




I've seen these bullets before, so I made some changes to hopefully make things more clear.  I just want to click on the "ETA Sort" button (onclick script attached) and have the requery run on both subforms for only those records and not the whole db.  The ETA fields are in ascending mode from the queries.  Also, I uploaded the db again to focus on the issue as suggested.
Private Sub cmdRequery_Click()

End Sub

Public Sub UpdateStatus()

' this function can be called from any form (sy ETA sort button for example)
' or add a "Status Refresh' button to your form and call it like:
' Call UpdateStatus
' Me.Refresh  ' or me.requery

    Dim rs As DAO.Recordset
    Dim PAX_last As Long
    Dim ETA_last As String
    'reset remove asterik to deploy feature to override manual Hot Spots and base them strictly on the code
    'CurrentDb.Execute "Update tblArrivals Set CD = '' Where CD = 'Hot Spot'"
    ' look at ETA and PAX fields to update CD when conditions apply
    ' the recordser is sorted by ETA
    Set rs = CurrentDb.OpenRecordset("SELECT ID, ETA, PAX, CD FROM tblArrivals Order By ETA;")
    ' make sure there is at least on record
    If rs.RecordCount > 0 Then
        ' stroe the current PAX and ETA values
        PAX_last = CLng(Nz(rs!PAX))
        ETA_last = Nz(rs!ETA, "0000")
    ' loop though the data in tblArrivals
    Do Until rs.EOF
            ' check to see ETA diff and PAX total in two adjecent records meet the conditions
            If (CLng(Nz(rs!PAX)) + PAX_last >= 700) And (fnETAdiff(Nz(rs!ETA, "0000"), ETA_last) <= 15) Then
                CurrentDb.Execute "Update tblArrivals Set CD = 'Hot Spot' Where ID =" & rs!ID
                CurrentDb.Execute "Update tblArrivals Set CD = 'Hot Spot' Where ID =" & rs!ID
            End If
            PAX_last = CLng(Nz(rs!PAX)): ETA_last = Nz(rs!ETA, "0000")
    End If
End Sub

Function fnETAdiff(ETA_2 As String, ETA_1 As String) As Long

    Dim Min1 As Long
    Dim Min2 As Long
    Min1 = CLng(Left(ETA_1, 2)) * 60 + CLng(Right(ETA_1, 2))
    Min2 = CLng(Left(ETA_2, 2)) * 60 + CLng(Right(ETA_2, 2))
    fnETAdiff = Min2 - Min1
End Function

Open in new window

Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question


I didn't mean to confuse you, but your simple solution of targeting the current date on the SQL did the trick.  As for the sort button, it requiries two queries where the ETA is set to ascending, thus adjusting the ETAs as appropriate.  Thanks buddy.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Jeffrey Coachman

<I didn't mean to confuse you,>
No problem, its just that I was unsure of all the other factors, so I was not sure if they were contributing to the issue.