Opening Recordset Causes Access to Crash

[GummowN]

I have some code as follows:

Set dbs = CurrentDb
strSQL = "SELECT tbl_Request.RequestID, tbl_Request.PriorityMIS, " & _
"tbl_MIS.Name, tbl_Request.DateDelivery, tbl_Request.Time " & _
"FROM tbl_MIS INNER JOIN tbl_Request ON tbl_MIS.MISID = tbl_Request.MIS " & _
"WHERE (((tbl_Request.PriorityMIS) > 0) And ((tbl_MIS.Name) = " & _
&quot;CurrentUser()) And ((fncGetMaxStatus([RequestID])) < 50)) &quot; & _
&quot;ORDER BY fncIsactive([requestid]), fncCreatedate([requestid]);&quot;
'strSQL = &quot;SELECT * FROM tbl_Request&quot;
Set rst = dbs.OpenRecordset(strSQL)
rst.Close
Set dbs = Nothing

When I set rst = dbs.openrecordset(strSQL) it causes Access to crash.

If I use that strSQL in a Query and open the recordset as the query it crashes. The query opens without a problem.
All the functions are referenced in other queries without a problem.

Any ideas

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[JohnTulley]

I have 2 ideas

1 Add the foolowing before Set dbs = CurrentDb
Dim dbs as Database
Dim rst as Recordset

2 Put the strSQL into a Query and save it and change
Set rst = dbs.OpenRecordset(&quot;QueryName&quot

Also check References.

Might help

 

[GummowN]

Thanks for the suggestion

I dont need to Dim as they are Dim'ed in the module and not in the function - there are about 20 functions so dimming every time begins to get boring.

I found that it is the sorting on the fnc.... fields that causes the error.

I have changed the query structure to avoid this and it works. when I get 5 minutes I will research on Microsoft and post my findings back here


If at first you don't succeed, try

http://www.tek-tips.com

for the answer.