I have Table of orders, and a table of comments. The comments table has fields that cover date/time entered, name of user that added the comment, the text comment, and of course a field that ties it to a certain order_id. So, I have a continuous form showing a certain range of orders (50ish on average usually. I wanted to have a calculated field in my form showing the comment that is most recent to each order. So I came up with two calculated fields in my query to accomplish it. comment_count: IIf(DCount("[comment]","[Comments]","[load] =" & [Load].[ID])>0,"yes","no") latest_comment: IIf([comment_count]="yes",DLookUp("[comment]","[Comments]","[load] =" & [Load].[ID] & " AND [when_input] = #" & DMax("[when_input]","[Comments]","[load] =" & [Load].[ID]) & "#"),"") It works, but over the network, it's painfully slow to scroll and navigate the form. I'm looking for a way to accomplish the same thing efficiently but much quicker. Thanks in advance.
Slow Continuos Form Query - Looking For A Better Way... (1 Viewer)
- Thread starter rmulder
- Start date
Here's what my form for the loads looks like. Like I said, my code works, it's just not efficient and very slow. I'm thinking it's got to be the DMax, DLookup, etc. Maybe there's a better way like keeping a check box field that marks the comment as the newest. And each time a new comment is added, run a update query that clears check marks from all the other comment records matching that load id, then check the new comment just saved. just a thought.
You can try two things to see which one is faster:
1. Create a query based on your Comments table, sort it in Descending Order by ID, use only a DLookup(). It will pick the Max comment because of the sort.
OR
2. Get the value from the query described above using a Recordset with the dbOpenSnapshot option.
1. Create a query based on your Comments table, sort it in Descending Order by ID, use only a DLookup(). It will pick the Max comment because of the sort.
OR
2. Get the value from the query described above using a Recordset with the dbOpenSnapshot option.
