Form requery time - options

Dear experts -
I have a main form that shows projects (nonupdatable recordset) - and includes some 6 or 7 fields that are complex calculations - some of these call functions that do a fair amount of calculation. Trust me, these fields are a huge benefit, and I explored options of simple queries, but in the end they do need a more complex calculation (e.g., looping through records to determine certain things).

NOW - the issue is - right now, the way the code is written, every time someone comes BACK to this form, it is doing a requery, which while not a deal-breaker, does take a few seconds every time. For a remote user perhaps as long as 5 seconds. Maybe this isn't so terrible.

Anyway, I read about REFRESH instead of REQUERY and this sounded ideal - except that REFRESH doesn't appear to update fields that are calculated from other queries (OR AM I MISSING SOMETHING?).

When i issue a refresh instead of requery command, the record in question isn't appearing properly (it DOES appear properly when the requery command is issued).

Any ideas, or should I just leave well enough alone? One idea I was considering was to remove those calculated fields from recordsource and use dlookup or function call on each record, based on key fields that I would hide on each record. Not sure if this is a good idea or not - would the refresh work in this case?

Thoughts? Thanks!
terpsichoreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
A Refresh will just update the data to what is current in the underlying tables.  It will not add or remove records that match or don't match the criteria you've assigned; that's what a Requery is for.  Using a DLookup a couple of times in a form is fine.  Using it in place of a query is inefficient.  You mentioned that you are looping through the records... perhaps your process can easily be accomplished with some SQL statements instead.  It would be hard to determine this without seeing what you have.  If you can post your code, we may be able to help you make it more efficient.
0
Gustav BrockCIOCommented:
If you really need a requery each time the form is activated, you need it, and your only option is to optimize the code you execute to perform the requery.

/gustav
0
terpsichoreAuthor Commented:
Here is the main procedure i am running that seems to be slowing things down - it calculates this on each record that appears - maybe 100 or so projects. It is scanning through each project + part + milestone record.
Ideas perhaps:
1) Use refresh, but add a 'requery' button on the form - people only need to reference this field in certain cases
2) Would adding a compound index perhaps speed this up?
Thanks!

NOTES:
1) I also include the SQL for the referenced query
2) IMPORTANT - it is actually runnig TWO SEPARATE FUNCTIONS because I need to return two separate values - a date and a text description - I guess I would automatically cut the time in half if I can return two values in one function - any ideas on this?





Function NextMilestoneDate(thisproject As Long, thispart As Long, thisprojtype As Long) As Variant
'if thispart = 0, then check across whole project

'IMPORTANT - ONLY INVOKE THIS IF PROJECT IS OPEN!!


    Dim rst As DAO.Recordset

If thisprojtype = 1 Then
    'IMPORTANT - ONLY LOOK AT NON-BILLING MILESTONES

    If thispart = 0 Then
        Set rst = CurrentDb.OpenRecordset("SELECT * FROM Milestones_By_Project_and_Part WHERE [Project_ID] =" & thisproject & " and [complete] <> -1 and [canceled] <> -1 and [billingonly] <> -1 ORDER BY expdate, sortorder")
    Else
        Set rst = CurrentDb.OpenRecordset("SELECT * FROM Milestones_By_Project_and_Part WHERE [Part_ID] =" & thispart & " and [complete] <> -1 and [canceled] <> -1 and [billingonly] <> -1 ORDER BY expdate, sortorder")
    End If
    
    If Not rst.EOF Then
       NextMilestoneDate = rst!ExpDate
    End If
    rst.Close
    Set rst = Nothing

ElseIf thisprojtype = 2 Then
    'should only be on project level
        
    
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM Project_Activities WHERE [Project_ID] =" & thisproject & " and [complete] <> -1 ORDER BY deadline")
    
    If Not rst.EOF Then
       NextMilestoneDate = rst!Deadline
    End If
    rst.Close
    Set rst = Nothing

End If





End Function
Function NextMilestoneName(thisproject As Long, thispart As Long, thisprojtype As Long) As String
'if thispart = 0, then check across whole project

'IMPORTANT - ONLY INVOKE THIS IF PROJECT IS OPEN!!


Dim rst As DAO.Recordset

If thisprojtype = 1 Then
    'IMPORTANT - ONLY LOOK AT NON-BILLING MILESTONES
    
    If thispart = 0 Then
        Set rst = CurrentDb.OpenRecordset("SELECT * FROM Milestones_By_Project_and_Part WHERE [Project_ID] =" & thisproject & " and [complete] <> -1 and [canceled] <> -1 and [billingonly] <> -1 ORDER BY expdate, sortorder")
    Else
        Set rst = CurrentDb.OpenRecordset("SELECT * FROM Milestones_By_Project_and_Part WHERE [Part_ID] =" & thispart & " and [complete] <> -1 and [canceled] <> -1 and [billingonly] <> -1 ORDER BY expdate, sortorder")
    End If
    
    If Not rst.EOF Then
        If thispart = 0 Then
            NextMilestoneName = IIf(Nz(rst!Milestone_ID, 0) = 23, "SP:" & left(rst!Notes, 5), rst!milestonetypeabbrev) & IIf(rst!Part_Number = 1, "", " (P" & rst!Part_Number & ")")
        Else
            NextMilestoneName = IIf(Nz(rst!Milestone_ID, 0) = 23, "SP:" & left(rst!Notes, 5), rst!milestonetypeabbrev)
        End If
    Else
       NextMilestoneName = ""
    End If

    rst.Close
    Set rst = Nothing

ElseIf thisprojtype = 2 Then
    'should only be on project level
    
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM Project_Activities WHERE [Project_ID] =" & thisproject & " and [complete] <> -1 ORDER BY deadline")

    If Not rst.EOF Then
        NextMilestoneName = rst!Description
    Else
       NextMilestoneName = ""
    End If
    
    rst.Close
    Set rst = Nothing

Else
    NextMilestoneName = ""
End If





End Function


SQL FOR QUERY INVOKED ABOVE:

SELECT Project_Parts.Project_ID, Project_Parts.Part_ID, Project_Parts.Part_Number, Project_Parts.Part_Description, parts_approved.ClientApprovalDate, Project_Parts.NoCharge, Project_Parts.FullyInvoiced, Project_Parts.Canceled, Project_Parts_Revenue.RevenueID, Project_Parts_Revenue.Milestone_ID, Project_Parts_Revenue.Hold, PICK_PartMilestoneType.Abbreviation AS milestonetypeabbrev, Project_Parts_Revenue.Complete, Project_Parts_Revenue.ExpDate, PICK_PartMilestoneType.StartMilestone, PICK_PartMilestoneType.ReviewMilestone, PICK_PartMilestoneType.FinalMilestone, PICK_PartMilestoneType.BillingOnly, Project_Parts_Revenue.QuoteAmnt, Project_Parts_Revenue.InvoiceAmnt, Project_Parts_Revenue.InvoiceDate, PICK_PartMilestoneType.IgnoreSort, PICK_PartMilestoneType.Sortorder, [pick_partmilestonetype].[abbreviation] & ": " & Format([expdate],"mm/dd/yy") & IIf(Nz([milestonedateflag_ID],0)<>0," (" & [pick_milestonedateflag].[abbreviation] & ")","") & IIf(Nz([project_parts_revenue].[notes],"")<>"","  " & [project_parts_revenue].[notes],"") AS mstone, Project_Parts_Revenue.notes, PICK_MilestoneDateFlag.Abbreviation AS dateflagabbrev, parts_approved.EmailOK
FROM Project_Parts RIGHT JOIN (((Project_Parts_Revenue LEFT JOIN PICK_PartMilestoneType ON Project_Parts_Revenue.Milestone_ID = PICK_PartMilestoneType.ID) LEFT JOIN PICK_MilestoneDateFlag ON Project_Parts_Revenue.MilestoneDateFlag_ID = PICK_MilestoneDateFlag.ID) LEFT JOIN parts_approved ON Project_Parts_Revenue.Part_ID = parts_approved.Part_ID) ON Project_Parts.Part_ID = Project_Parts_Revenue.Part_ID;

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Gustav BrockCIOCommented:
As you use only one record, you can select TOP 1 only which is faster.
Also, you can change the function to return both values as parameters:
Function NextMilestoneDate(thisproject As Long, thispart As Long, thisprojtype As Long, ByRef ExpDate As Date, ByRef NextName As String) As Boolean
'if thispart = 0, then check across whole project

'IMPORTANT - ONLY INVOKE THIS IF PROJECT IS OPEN!!


    Dim rst As DAO.Recordset

If thisprojtype = 1 Then
    'IMPORTANT - ONLY LOOK AT NON-BILLING MILESTONES

    If thispart = 0 Then
        Set rst = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM Milestones_By_Project_and_Part WHERE [Project_ID] =" & thisproject & " and [complete] <> -1 and [canceled] <> -1 and [billingonly] <> -1 ORDER BY expdate, sortorder")
    Else
        Set rst = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM Milestones_By_Project_and_Part WHERE [Part_ID] =" & thispart & " and [complete] <> -1 and [canceled] <> -1 and [billingonly] <> -1 ORDER BY expdate, sortorder")
    End If
    
    If Not rst.EOF Then
       ' Return values.
       ExpDate = rst!ExpDate
       If thispart = 0 Then
           NextName = IIf(Nz(rst!Milestone_ID, 0) = 23, "SP:" & Left(rst!Notes, 5), rst!milestonetypeabbrev) & IIf(rst!Part_Number = 1, "", " (P" & rst!Part_Number & ")")
       Else
           NextName = IIf(Nz(rst!Milestone_ID, 0) = 23, "SP:" & Left(rst!Notes, 5), rst!milestonetypeabbrev)
       End If
       ' Return success.
       NextMilestoneDate = True
    End If
    rst.Close
    Set rst = Nothing

ElseIf thisprojtype = 2 Then
    'should only be on project level
    
    Set rst = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM Project_Activities WHERE [Project_ID] =" & thisproject & " and [complete] <> -1 ORDER BY deadline")
    
    If Not rst.EOF Then
       ' Return values.
       ExpDate = rst!Deadline
       NextName = rst!Description
       ' Return success.
       NextMilestoneDate = True
    End If
    rst.Close
    Set rst = Nothing

End If

End Function

Open in new window

So where you now call:

  myDate = NextMilestoneDate(...)
  myName = NextMilestoneDate(...)

you will change this to:

  If NextMilestoneDate(..., myDate, myName) = True Then
     ' New values returned in myDate and myName.
  Else
     ' No new values.
  End If

Finally, an Update button may be a good option. Depends on how often data are changed.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
terpsichoreAuthor Commented:
thanks; one question - i actually call two different functions, nextmilestonedate and nextmilestonename - they both find the same record, but return a different field (one returns a datetime, the other returns a description of the same record.
Is there a way to return two values in the same function?
0
Gustav BrockCIOCommented:
Yes, the two functions are merged into one which returns the two values in ExpDate and NextName to be retrieved as shown.

/gustav
0
terpsichoreAuthor Commented:
many sincere thanks. very thorough and insightful.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
terpsichoreAuthor Commented:
if you're still there - i am calling this function as a field in a query (recordsource of a form). Instead of the ByRef, perhaps I can create a comma-delimited string, and then in the detail of the form, split this into its components?
0
Gustav BrockCIOCommented:
Of course. You could do that.
Then use Split to separate them at the receiving end.

/gustav
0
terpsichoreAuthor Commented:
thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.