Link to home
Create AccountLog in
Avatar of francodhs
francodhsFlag 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.

Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

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, 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.


Avatar of francodhs



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

Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

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.
<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.