Query keeps crashing

[jibb]

Hi All,

I've got a very confusing problem, I'll try explain as best as I can!

I have written a query in Access 2013 which has data from a couple of different sources and displays information on a form. I can open the form and it will run fine and if I close the form but leave the main Access window open I can open the form and the query will run correctly. If I then close Access completely and open it again and then open the form the query won't run. The only way I can fix it is to compact and repair and then the query works fine again until I close Access.

I have rewritten the query and run it in a previous version of my DB and it does the same thing.

Any ideas please??

Thanks

 

[GinaWhipp]

Try using refreshing the links and see if that resolves the issue...

J Street Access Relinker
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

 

[jibb]

No...that hasn't sorted it.

 

[GinaWhipp]

Please post the SQL of the query and any start up routines you are running on the Main form.

 

[jibb]

I don't have any startup routines running on the form, all it does is display the query results.

SELECT qryLowi15StockCodes.stcode AS [STOCK CODE], [desc_] & " " & [desc2] AS DESCRIPTION, [reordlev]*1000 AS OPTIMUM, [STSTOCKDETAILS]![LEV]*1000 AS ONHAND, [outorders]*1000 AS OUTSTANDINGSO, 1-(([STSTOCKDETAILS]![LEV]*1000)/([reordlev]*1000)) AS 25LOW, IIf((1-([ONHAND]/[OPTIMUM]))>=0.5,1-([ONHAND]/[OPTIMUM]),Null) AS 50LOW, ([OPTIMUM]-[ONHAND])+[OUTSTANDINGSO] AS QTYTOORDER, STDepotStockLevels.depot, DSum("[quan]","qrySTOCKENROUTE","[stcode] ='" & [STOCK CODE] & "'")*1000 AS [EN ROUTE]
FROM (STStockDetails INNER JOIN qryLowi15StockCodes ON STStockDetails.stcode = qryLowi15StockCodes.stcode) INNER JOIN STDepotStockLevels ON qryLowi15StockCodes.stcode = STDepotStockLevels.stcode
WHERE ((([reordlev]*1000)>0) AND ((1-(([STSTOCKDETAILS]![LEV]*1000)/([reordlev]*1000)))>=0.25) AND ((STDepotStockLevels.depot)="01"))
ORDER BY 1-(([STSTOCKDETAILS]![LEV]*1000)/([reordlev]*1000)) DESC;

Thanks for your help

 

[GinaWhipp]

Hmm, queries within queries, no help there. Okay, let's try this. Hold down the Shift Key and open the database with the Main Menu then go and try to open this query. Does it open? If not strt back tracking to the queries the query includes. One (or more) of those may show us a clue...

 

[jibb]

Its the same as the form...it will open the first time after a compact and repair with no problems but if I shut the DB then open it again it crashes.
The query that is in the main query doesn't have any problems...

 

[GinaWhipp]

That is why I want you to open without opening any Forms by holding down the Shift Key. Since you say there is no code behind the Form I want you to run the query by itself back tracking the queries within.

If you are sure that is not the problem, you could ensuring Track Autoname Correct* is unchecked. If it is then import all Objects into a clean, new database container and see if the problem persisits.

 

[jibb]

That was only opening the query, not the form...it has the same problems!
I'll try importing everything into a blank database now

 

[JHB]

That was only opening the query, not the form...it has the same problems!
I'll try importing everything into a blank database now

Else post your database with some sample data.
Personally I don't like fieldname like this [desc_]

 

[jibb]

That fieldname is in an ODBC linked table that can't be changed - it hasn't ever affected a query before

 

[GinaWhipp]

You never posted if you had the same problem once imported into a new database. Also, what References do you have set?

 

[jibb]

Just trying that now...it will take a while!
What reference should I have set?

 

[jibb]

Looks like this is a 2013 problem...tried in 2010 and there are no problems...

 

[GinaWhipp]

Well, at times it could vary, that is why I asked which ones you have set. You can use this as a guide...
http://www.access-diva.com/d5.html