By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,313 Members | 2,798 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,313 IT Pros & Developers. It's quick & easy.

I've never seen A97 combo-box wizard fail on a valid query until today...

P: n/a
MLH
I invoked the combo-box wizard today, telling it to use a 4-table
union query as a row-source for the combo-box it was assisting
me in building. The error I got was without number and stated,
"Data cannot be retrieved from the source you have selected.
You must select a different table or query to continue in the wizard."
just after I chose the query named
qryAuthsOwnersAddnlOwnersLienholders4OneCar.

Have any of you ever seen that error message in A97 before?
I was able to successfully build the combo-box manually. Each
time I tried to use the query to build it, I failed.

The query runs fine, returning the desired records anytime its
launched. And, the manually constructed combo-box works
fine.

Here is the SQL for the union query...
SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] & " " &
[AddnlOwnrAddr] & " " & [AddnlOwnrCity] & ", " & [AddnlOwnrState] & "
" & [AddnlOwnrZip] & " " & "(" & [AddnlOwnrCounty] & " county) " &
Format$([AddnlOwnrPhone],"(@@@) @@@-@@@@") & " " &
IIf([Corporation]=True,"(corporation)","(person)") AS Item,
tblVehicleJobs.VehicleJobID, "X" AS OLAX, tblAddnlOwnrs.AddnlOwnrID AS
RecID
FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs ON
tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID
WHERE tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID ();

UNION SELECT [LienHolderName] & " " & [LienHolderAddr] & " " &
[LienHolderCity] & ", " & [LienHolderState] & " " & [LienHolderZip] &
" " & "(" & [LHCounty] & " county) " &
Format$([LienHolderPhone],"(@@@) @@@-@@@@") & " (corporation)" AS
Item, tblVehicleJobs.VehicleJobID, "L" AS OLAX,
tblLienHolders.LienHolderID AS RecID
FROM tblVehicleJobs INNER JOIN tblLienHolders ON
tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID
WHERE tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID ();

UNION SELECT [OwnerFName] & " " & [OwnerLName] & " " & [OwnerAddr] &
" " & [OwnerCity] & ", " & [OwnerState] & " " & [OwnerZip] & " " &
"(" & [OwnerCounty] & " county) " & Format$([OwnerPhone],"(@@@)
@@@-@@@@") & " (corporation)" AS Item, tblVehicleJobs.VehicleJobID,
"O" AS OLAX, tblOwners.OwnerID AS RecID
FROM tblVehicleJobs INNER JOIN tblOwners ON tblVehicleJobs.OwnerID =
tblOwners.OwnerID
WHERE tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID ();

UNION SELECT [AuthCompany] & " " & [AuthAddr] & " " & [AuthCity] & ",
" & [AuthState] & " " & [AuthZip] & " " & "(" &
Format$([AuthPhone],"(@@@) @@@-@@@@") AS Item,
tblVehicleJobs.VehicleJobID, "A" AS OLAX, tblAuth.AuthID AS RecID
FROM tblVehicleJobs INNER JOIN tblAuth ON tblVehicleJobs.AuthID =
tblAuth.AuthID
WHERE tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID ();

Dec 18 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
MLH
Oops. I had a typo in the 2nd paragraph of the OP...

Last line should-a-read... Each time I tried to use the wizard to
build it, I failed.
Dec 18 '05 #2

P: n/a
MLH wrote:
I invoked the combo-box wizard today, telling it to use a 4-table
union query as a row-source for the combo-box it was assisting
me in building. The error I got was without number and stated,
"Data cannot be retrieved from the source you have selected.
You must select a different table or query to continue in the wizard."
just after I chose the query named
qryAuthsOwnersAddnlOwnersLienholders4OneCar.

Have any of you ever seen that error message in A97 before?
I was able to successfully build the combo-box manually. Each
time I tried to use the query to build it, I failed.


I recall it when my SQL in the combo row source was too long. That is,
when I made the SQL actually the row source and not a named query.

Two things I can suggest looking at:

First is if you are putting the name of the query in the row source, ie,
building the query via the Access query builder, saving it as
qryAuthsOwnersAddnlOwnersLienholders4OneCar and then, in the row source
property box, selecting qryAuthsOwnersAddnlOwnersLienholders4OneCar from
a list of tables/queries.

I wonder if the name is too long? I don't think it would be, but try
saving it as "Test" or something short and see if that helps. This is a
guess.

The second thing is if you are actually putting the SQL in the combo
box. In other words, either building the
qryAuthsOwnersAddnlOwnersLienholders4OneCar query in the query builder
and copy/pasting the actual SQL into the row source box OR clicking the
builder button and developing the query from scratch so that there's
"Select blah blah, etc" showing in the row source property rather than
the name of the query.

In this case, it could well be that your SQL is too long (what's
happened to me in the past) for the combo row source property. There's
a limit on the number of characters for the SQL statement in a row
source (I can't recall the character limit). If this is the case,
you'll need to save your sql as a saved query and point the row source
to that query. Again, my posit in the first case is a guess if you run
into the same difficulty.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Dec 18 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.