what is wrong with this update query?

[mcnews]

UPDATE tblFollowUpData INNER JOIN vwPassenger ON tblFollowUpData.PLFId
= vwPassenger.PLFId SET tblFollowUpData.qstation = (SELECT qstation
FROM vwPassenger
WHERE Not isnull(vwPassenger.ConveyanceID) AND vwPassenger.PLFId =
tblFollowUpData.PLFId
AND (IsNull(tblFollowUpData.qstation) and Not
IsNull(vwPassenger.qstation)));


i get a 'Operation must use an updatebale query' error.

tia,
mcnewsxp

 

[John Spencer]

Would the following work for you?


UPDATE tblFollowUpData INNER JOIN vwPassenger
ON tblFollowUpData.PLFId= vwPassenger.PLFId
SET tblFollowUpData.qstation = vwPassenger.qStation

WHERE vwPassenger.ConveyanceID Is NOT Null
AND tblFollowUPDate.QStation is Null
AND vwPassenger.qStation is Not Null

Your query has the problem that the Subquery COULD return more than one
record and Access knows that and refuses to use the subquery.

Your other option would be to use the DLOOKUP function to return a value
(or one of the other VBA domain functions)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[mcnews]

Would the following work for you?


UPDATE tblFollowUpData INNER JOIN vwPassenger
ON tblFollowUpData.PLFId= vwPassenger.PLFId
SET tblFollowUpData.qstation = vwPassenger.qStation

WHERE vwPassenger.ConveyanceID Is NOT Null
AND tblFollowUPDate.QStation is Null
AND vwPassenger.qStation is Not Null

Your query has the problem that the Subquery COULD return more than one
record and Access knows that and refuses to use the subquery.

Your other option would be to use the DLOOKUP function to return a value
(or one of the other VBA domain functions)

i get the same error.
i'd prefer not to use the dlookup because i may need to make thisa
stored proc on the sql server.
thanks much, tho.

 

[John Spencer]

Is vwPassenger a query or a view on the SQL server?

If it is a query, then post the SQL for it. If it is a view, then you
may need to find a workaround - such as a stored proc on the SQL server.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[mcnews]

Is vwPassenger a query or a view on the SQL server?

If it is a query, then post the SQL for it. If it is a view, then you
may need to find a workaround - such as a stored proc on the SQL server.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

view.
must be the problem.
i wrote some code to do it.
thanks again.