Can SELECT be used on recordset?

K

Ken

Hi, I am looking for help. Can the SELECT statement be used on ADO recordset
For example : SELECT * from <table>; Can <table> be ADO.record instead of a table name?
 
K

Ken

Thanks. Do you have any suggestion on my case
I have a combo box (WarehouseCode) which depends on the ProductCode. I use ADO to build a query to extract all information and union with <ALL> for the warehousecode combo box. So as a result in the combo box list, I will have <ALL> as the first choice followed by all warehouse codes. I got all these done perfectly. However, if there is no warehouse code associated with this product, in the list I still see <ALL> as one and only one choice. So what I am going to do is, after I got the recordset, I test if it is EOF. If yes, I will not union with <ALL>. Since combo boc ROW Source accepts only query, so my query is this

Select * from rst1 union <ALL> ....

But at run-time, I got message saying rst1 is disconnected or misspelled or recordset is not exist.
I put a debug.print rst1.getstring before the Select statement, I got all expected rescords printed. Quite confuse about the message. Any idea?
 
D

Douglas J. Steele

Set the row source to the original SQL you use to open the recordset, plus
UNION <ALL>...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ken said:
Thanks. Do you have any suggestion on my case?
I have a combo box (WarehouseCode) which depends on the ProductCode. I
Click to expand...
use ADO to build a query to extract all information and union with <ALL> for
the warehousecode combo box. So as a result in the combo box list, I will
have <ALL> as the first choice followed by all warehouse codes. I got all
these done perfectly. However, if there is no warehouse code associated
with this product, in the list I still see <ALL> as one and only one choice.
So what I am going to do is, after I got the recordset, I test if it is EOF.
If yes said:
Select * from rst1 union <ALL> ....

But at run-time, I got message saying rst1 is disconnected or misspelled or recordset is not exist.
I put a debug.print rst1.getstring before the Select statement, I got all
Click to expand...
expected rescords printed. Quite confuse about the message. Any idea?
 
K

Ken

Yes, this is what I am doing. But if the result of the SQL is empty, I still will have <ALL> that is no meaning at all
Thanks anyway for your suggestion.
 
D

Douglas J. Steele

So check whether there's only 1 row in your recordset, and reset the combo
box's rowsource if there is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Top