Operation must use an updateable query..

[Lewe22]

PLEASE HELP!!

I am trying to update a table which is held in a SQL database, from Access.

I have written the following SQL statement which runs perfectly as either a PTQ in Access or even run from SQL Tools 1.4.2.

UPDATE unit_instance_occurrences uio
SET fes_active_places =
(SELECT COUNT(*) FROM registration_units ru
WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code
AND ru.uio_occurrence_code = uio.calocc_occurrence_code
AND ru.progress_status = 'A'
AND ru.uio_occurrence_code = 07);

The problem only arrises when trying to execute the same code in VB from a button on an Access form. (This is necessary because the year (07) is selected by the user on the form)

My VB is......

Private Sub New_Click()
Dim db As DAO.Database
Dim qdfPassThru As DAO.QueryDef
Set db = CurrentDb
CurrentDb.QueryDefs("Active Update Query").SQL =
"UPDATE fes_unit_instance_occurrences uio " & _
"SET fes_active_places = " & _
"(SELECT COUNT(*) FROM fes_registration_units ru " & _
"WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code " & _
"AND ru.uio_occurrence_code = uio.calocc_occurrence_code " & _
"AND ru.progress_status = 'A' " & _
"AND ru.uio_occurrence_code = " & Me!cboYear & ");"
Set qdfPassThru = db.QueryDefs("Active Update Query")
qdfPassThru.Execute
Set qdfPassThru = Nothing
Set db = Nothing
End Sub

I am repeatedly seeing the error message "Operation must use an updateable query" and i don't understand why. It can't be a permissions error because i clearly have sufficient access as the code runs sucessfully as a standalone PTQ.

Any help will be greatly appreciated. Thanks in advance.

[Scott Price]

What value is being returned by Me!cboYear ?? What is the RowSource of this combo box, and which is the bound column? Does it, in fact, reside on the form from which you are running this query?

Regards,
Scott

[Lewe22]

At present my form only consists of 1 combo box and 1 button. The combo box is picking up a list of years from an ODBC linked table.

[Scott Price]

At present my form only consists of 1 combo box and 1 button. The combo box is picking up a list of years from an ODBC linked table.

You haven't answered my question!

What value is being returned by the combo box? This is indicated by it's row source and it's bound column.

For example: given combo box name cboYear, Rowsource

Select YearID, Year From tblYear Order By Year

Bound column = 1.

The value returned by the setup I indicated will be an ID number not a year number.

Regards,
Scott