Use updateable query error

[looloo]

I don't understand which part of this statement is an attempt to update a query. I am trying to update the Restaurant table. The WHERE clause references a list box that contains a query.



Private Sub AssignButton_Click()
Dim SQLMasterUpdate As String
Dim iRow As Variant

If Me!RestaurantListBox.ItemsSelected.Count <> 0 Then
For Each iRow In Me!RestaurantListBox.ItemsSelected
SQLMasterUpdate = "UPDATE Restaurant SET " & _
"Restaurant.AccountManagerID = (SELECT AccountManagerID FROM AccountManager " & _
"WHERE AccountManagerName = " & Me!NameComboBox.Value & ")" & _
"WHERE ((SELECT RestaurantPhone FROM Restaurant) = " & _
Me!RestaurantListBox.Column(1, iRow) & ");"

DoCmd.RunSQL SQLMasterUpdate
Next iRow
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If
End Sub

[stmoong]

Would this solve the problem you're facing?

Make sure you use single quote or Char(34) for those values that are text (or string).

SQLMasterUpdate = "UPDATE Restaurant SET " & _
  "Restaurant.AccountManagerID = (SELECT AccountManagerID FROM AccountManager " & _
  "WHERE AccountManagerName = '" & Me!NameComboBox.Value & "')" & _
  "WHERE RestaurantPhone = '" & Me!RestaurantListBox.Column(1, iRow) & "');"

[looloo]

Oh yeah. Well, I changed that, but still get the same error. It seems like it has to do with my subquery in the WHERE clause.

[looloo]

Is the "non-updateable query" the table I am updating? Could it be because the phone number field does not allow duplicates? If so, what would be the way around that?

[looloo]

This update works:

SQLMasterUpdate = "UPDATE Restaurant SET " & _
"Restaurant.AccountManagerID = 7" & _
" WHERE RestaurantPhone = '" & _
Phone & "';"

I used the number 7 instead of the SELECT statement. So how can I store and use the value obtained from:

"SELECT AccountManagerID FROM AccountManager WHERE AccountManagerName = '" & Me!NameComboBox.Value & "'"

[stmoong]

You can try using the DISTINCT keyword in the SQL to get unique row.

Alternatively, you could change your combo box to store AccountManagerID as a hidden column. So, you can just access that selected value using combobox.Column(1). This will save you from writing the subquery in your update statement.

[looloo]

DISTINCT didn't cut it, but the other suggestion worked perfectly. I already had the ID hidden in the Combo Box knowing I would need it.

Now the only question is how do I handle multiple updates without having to confirm each row updating separately?

[stmoong]

You can do this 2 ways.

1st way:

DoCmd.SetWarnings False
.....
DoCmd.RunSQL strSQL
.....
DoCmd.SetWarnings True

I prefer the 2nd way:

Set db = CurrentDb
db.Execute strSQL, dbFailOnError

With the 2nd method, you can wrap in a transaction so as to control whether or not you commit transaction. You also have access to db.RecordsAffected. This means you can prompt user whether they want to update the number of records before committing the transactions. If not, then, either way is fine.

[looloo]

Thanks. Useful info. Stay tuned... this project is not finished yet! (are they ever?)