Rowsource syntaxchanges when form imported to new db. Why?

[Margarita]

Hello, googled this for a while, but no luck. All threads which deal any kind of 'Access changes my sql syntax' topic end abruptly without a resolution.

I have a form called UpdateFTE with a listbox whose rowsource is:

select distinct ucase(LastName & " " & FirstName), LastName, FirstName from (select LastName, FirstName from EMPLOYEE_REG union Select LastName, FirstName from EMPLOYEE_TEMP);

This works just fine in my development copy. When I import this form into another db- ANY other db- (tried my users' copies and also a blank new database)- the syntax changes to this:

SELECT DISTINCT ucase(LastName & " " & FirstName), LastName, FirstName FROM [select LastName, FirstName from EMPLOYEE_REG union Select LastName, FirstName from EMPLOYEE_TEMP] AS [%$##@_Alias];

And when I try to pick from the listbox, I get the error:
"The record source '~sq_cUpdateFTEform~sq_cEmployeeName' specified on this form or report does not exist."

Why does this happen? All the tables are linked in the users' databases properly, all data is available and no problems with importing other forms. Compacting both databases, relinking tables, importing and directly pasting from a back up copy does not work. How can I stop it from messing with the syntax?
Thank you.

 

[vbaInet]

I see that your posts are becoming shorter Margarita. I'm impressed

If you write a subquery, you need to give it an alias or else Access will create one for you.

 

[Margarita]

I see that your posts are becoming shorter Margarita. I'm impressed

If you write a subquery, you need to give it an alias or else Access will create one for you.

Thank you, thank you, I've been trying to stick to your less is more advice.
I understand that an alias is assigned in queries with subqueries. But why all of a sudden does the import process kill the db's ability to read its own syntax? It says the source doesn't exist. But all the tables are there- the source does exist, so I'm assuming it's the syntax change that happens during import that kills it. When I save and close the form in the original db- the one that works- the syntax does not get changed and I don't get any errors.
Thank you.

 

[vbaInet]

It's just the alias that's causing problems. It may work in one and not work in another. But if the alias was constant it will always work. Is your dev copy a different version of Access?

 

[Margarita]

It's just the alias that's causing problems. It may work in one and not work in another. But if the alias was constant it will always work. Is your dev copy a different version of Access?

It's the same version of Access. And I didn't have any problems importing any other forms that have sql statements with subqueries as record sources... Not sure what went wrong with this one...

 

[vbaInet]

I have no idea why it would work on one and not work on the other. You now know the proper way of doing things so you shouldn't worry about it any longer.