SHAPE APPEND command question

[CSapo]

I am trying to create a recordset using the SHAPE APPEND command, and have the following written:

rst.Open "SHAPE {SELECT * from Asset WHERE Field1='S65A0010'} " & _
"APPEND ({SELECT * from ValAssetFeat} " & _
"RELATE AssetID TO AssetID) AS NIC1", cnn


The tables Asset and ValAssetFeat are linked by the AssetID key (pk in Asset and fk in ValAssetFeat).

This will work, BUT, if I try the following:

rst.Open "SHAPE {SELECT SerialNo, Field1 from Asset WHERE Field1='S65A0010'} " & _
"APPEND ({SELECT * from ValAssetFeat} " & _
"RELATE AssetID TO AssetID) AS NIC1", cnn


where I just want the SerialNo and Field1 fields returned, I get an error as follows:

Run-time error '-2147217900 (80040e14)':
The referenced column (AssetID) does not exist in the appropriate rowset.

I have tried SELECT dbo.Asset.SerialNo, SELECT Asset.SerialNo and the following whole new command:

rst.Open "SHAPE {SELECT SerialNo, Field1 from Asset} " & _
"APPEND ({SELECT dbo.ValAssetFeat.ValString FROM dbo.ValAssetFeat INNER JOIN " & _
"dbo.Asset ON dbo.ValAssetFeat.AssetId = dbo.Asset.AssetId} " & _
"RELATE AssetID TO AssetID) AS chapNIC1", _
cnn

all to no avail, getting various errors.

Does anyone have any idea how to write a stupid SHAPE APPEND statement that actually works?? This is so frustrating!!

Thanks in advance.

Cindy

[Forest Dragon]

You need to add the field called "AssetID" to the parent recordset so that you would be able to link it to the child recordset:

rst.Open "SHAPE {SELECT AssetID, SerialNo, Field1 from Asset WHERE Field1='S65A0010'} " & _
"APPEND ({SELECT * from ValAssetFeat} " & _
"RELATE AssetID TO AssetID) AS NIC1", cnn

Good Luck!!!