This error occurs because, drum-roll please….
The setting for this property is too long.
So in this particular case, the developer was trying to create a long SQL string using multiple UNIONs with a complex query and trying to set this string to the RowSource property of a combo box.
The error message points to the fact that a RowSource property is limited in the number of characters it can accept. If you try to set the property to a string with more than 2048 characters, you will get this error message.
Your subquery you are trying to build is too long.
A RowSource of a control can only contain 2048 characters, so when you are trying to insert the subquery twice this is causing your string to go beyond the 2048 character limit, so that is what the error is saying.
Solution 1
Putting the long sql in a saved query object fixes this, since that can contain much longer text. So in the RowSource property you only have to reference the name of the saved query object rather than have all the sql in there.
Solution 2
If you don’t want to use a saved query object, then you will have to modify your string to be shorter and fit within the 2048 character limit. One way to shorten a query is by aliasing tables. Consider this query:
SELECT Myreallylongtablename.MyReallyLongFieldName FROM Myreallylongtablename
And notice how it can be shortened:
SELECT m.MyReallyLongFieldName FROM Myreallylongtablename m
If the query has been generated by Access then it likely has all the full table names, the fields referenced by the table names, etc.
You can actually omit the table name qualifier in front of the field (m.) as well IF the field name is unique across all the tables you are joining.
If that doesn’t make it short enough, you could make a query based on your original table object and also alias the field names to make them shorter, however you will need to create a saved query to do this anyway, so you might as well just use solution 1. Unless perhaps you are going to use this saved query many times for many RowSource properties.