Do you know where it is hanging? I would have this broken up into several sub procedures/functions. You are creating multiple recordsets, then filling the form, ... That is a lot to bite off in one big procedure IMO. I would probably have functions like
GettblIR() as dao.recordset
GettblPNA() as dao.recordset
And for sure
GetXl_qry() as dao.recordset
FillEditableForm(argument,argument2...)
Not that this is more efficient, but would sure a lot easier to code, debug, and work on optimizing.
That XL_query is pretty brutal. Does it return query results in a reasonable time? There are a lot of functions done in the query. I have not worked with SP for a long time, but it was notoriously slow in the old days. Would it make sense to simplify the query and do the processing on the fields in the procedure to fill the editable table? In other words do your if checks, trimming, nz, etc when filling the table. Obviously if this was something other than SP then doing it is SQL would probably be the efficient way.
Thank you for your responses.
Well first, I'm not sure what "SP" is, stored procedure? Secondly, I believe that breaking it up into more functions is not going to increase the speed of the result, if anything the overhead of the procedure calls should slow it down as far as I know. Third, I don't know how to use a function to open a recordset and then use fields from that recordset in the calling function - without using a complicated string return that will make it take much longer to process, and complicate the debugging. I have tried to return a recordset object before from a function and failed. I tried using vba code search results to do so without success. But I think that's going off on a tangent as it is not addressing the main problem.
The main reason I need to do this this way is I need to populate a field with results from one of two other fields, and only after comparing the text with an "alias" table and a "not a name" table - of things entered by the staff that should not have been entered. So I need to create a table that I can work with (edit). A secondary objective, also important, is to show the data for examination for proofreading (error checking). Yes, much of the data was entered incorrectly, but there is nothing I can do about that, I just have to deal with it. This function is immediately followed by another function that looks at some text fields and fills in another (unmentioned) text field with the appropriate text.
This is all made more complicated by the fact that some of the fields that are passed directly to access from the Sharepoint table are not simple fields, "complex data", "type2/object" data type, and other fields are text of >255 characters. This means I have to first query with Excel, then link my database to that Excel table, because Excel converts these "complex data" to dates for me, but will not capture all the long text. So I have to add it with vba, from two different sources. I tried a SQL "inner join," and "left join," but using them made my database unstable, and I had to restore from previous saves many times trying to work with that. Since then, recently, I have rebuilt it from scratch carefully to restore stability. It seems that working with tables that include the "complex data" type in one table, and simple fields in the corresponding field of the other table, with "join," causes the db to become unstable, and occasionally it corrupts the db making it unusable.
Now I am thinking maybe I need to try using a "maketable" query (not in vba), as since my code has matured a bit and I am now doing things differently it may be feasible as an alternative. The problem with this a few weeks ago was I was trying to limit my query to a date range to speed things up a lot, but that did not work out (first it was not feasible because I was trying to do it with that "complex data" field directly from Sharepoint, then later I learned more info from my internal customer that made me abandon this). Would an internal Access maketable query run significantly faster than the vba SQL? And yes, I could (and did previously) do a lot of those calculations after the main SQL query, but I don't know if I would be wasting my time trying it as I have no idea of whether or not it would speed it up.
I would appreciate any advice here. I consider the responses in this forum to be of the best quality, in comparison to a few other Access forums. The guys here seem to know more and hit the mark with the responses better. Thank you.