weird query error: cells turn into expressions

[Cowboy_BeBa]

hi everyone,

the database im asking about is very complex and any one of a million things could be causing the problem im having but i dont wanna make all you guys read 100 pages just to help me out, so ill try and be as brief as possible,

basically ive built a database for a family business, we have products that we blend using various ingredients, the database was originally built to keep track of stock and automate our ordering process but its evolved quite far beyond its original scope over the years

the problem im having is in a query called "qryShoppingList" (it happens in a few other queries, but this one recurs the most), this query helps generate a report which basically tells us how much stock we have on hand, how much we need total to fill all our orders and finally how much (if any) stock we need to order, every few months ill get a call from the office people who use it and they tell me the shopping list doesnt work, whenever i look into it i find that all the fields in "qryShoppingList" have been turned into expressions, so instead of a field being called "ingID" it somehow gets turned into "expr1: ingid", "ingName" is turned into "expr2: ingName", etc.
has anyone ever seen anything like this?

i suspect a potential cause for this probiem is that all the info from this query is from another query called qryStockControl, this is essentially a huge summary of all our operations over the course of a month, in order to get all the data into the right format i had to create about 8 or 9 different queries built on top of one another and combined with tables, its very hard to explain and convoluted as frak but it works very well and i can use it as a basis for literally any other type of query id need. though it does work i am not sure whether the fact that im basing so many queries on other queries may have something to do with this error (as so far all the queries ive seen this on are layered on atleast 2 other queries), though im not 100%

another potential factor could be that while they use the database in the office im at home constantly updating it, ive been working on it for about 3 years or so, basically every month or so i update it, i meet with everyone in the office, they report any bugs and suggest new features, and then i go back home to work on it, once im done i bring it back to the office on a usb, copy the front end to the server, relink it with their copy of the back end (and update their back end and add any new tables or data manually), then copy the relinked front end from the server on to everyones computers, the next month i repeat, im wondering if this process might also have something to do with it

any thoughts?

 

[Mihail]

has anyone ever seen anything like this?

This happen many times and I wonder how you design as huge database without facing any more with this issue until now.

I know only one reason to happen this (maybe there are more):
In one of the direct subquery or table you have removed a field.
Or you have changed a field name and you moved that field into a new position in the query/table.

 

[Cowboy_BeBa]

omg thank you, yes ive done all of that, because im always changing the database, sometimes ill have to edit the subqueries, changing names, moving positions and deleting fields, i try not to do it too much, as i dont like to mess around with subqueries unless i really have to, does this error happen every time i do that?

not sure why i havent really seen this error happen too much, the database is huge (atleast 18 tables and more queries than i can count), i guess its becuase i try hard to keep it as neat as possible, at some point i think i might have to rebuild the database from scratch, but i dread having to do all that work

 

[Pat Hartman]

If you delete a table or a column, Access will rename the missing columns with alias' the next time you open an object that references them so the damage is not always noticed immediately. If you compact the database in the mean time, EVERY reference will be renamed and that will cause a real pain.

As a general practice I use querydefs for all things but in some situations where I know the tables get deleted and recreated, out of self preservation I have switched to using embedded SQL and either running that directly or creating querydefs as I need them. This is a much less efficient method than using querydefs but you never want the user to have a problem because Access thinks it is smarter than you are and renames all the fields of a query even though the table is back by the time the query runs.