MS Access - Update/Delete Error

[ssanfu]

From my understanding, wouldn't this be the only way to make a mass update to multiple rows.

For example, if I have 6 rows with the Category ID of 20, and I delete Category 20, I need to move all 6 of the products to Category 25 now.

If you have a table of categories, an update query is the most efficient way to do a mass update. But you would make the change/update to (Category 25) before you delete Category ID of 20.

As for the rs, it appears to auto declare them in the code, as Ive loaded them with break points and verified rs1 and rs2 do have values stored to them.

It is better to require variable declaration (a setting in Access) so that you don't introduce strange errors in the code because you mistype a variable name.

[Scyclone]

Fair enough on the declaration,

However my issue at the moment is getting the update on the products themselves as is, and yes done so prior to the delete of category 20 for example.

[ssanfu]

Fair enough on the declaration,

However my issue at the moment is getting the update on the products themselves as is, and yes done so prior to the delete of category 20 for example.

Like this??? (try this on a copy of your MDB)

Private Sub cmdCatDelete_Click()
   Dim sValue As String  ' "value" is a reserved word in (Jet) Access
   'see http://allenbrowne.com/AppIssueBadWord.html

   Dim CatName As String
   Dim strSQL As String
   Dim rsCat As String
   Dim CatSQL As String
   Dim strUpd As String

   CatName = Me.txtCatSelect
   sValue = MsgBox("Are you sure that you want to delete this category?" & vbCrLf & "Deleting a category will move all associated products to 'Uncategorized' which cannot be displayed until they have a new category manually added to them.", vbYesNo, "Delete the Selected Category?")
   If sValue = 6 Then
      'MsgBox ("Value is yes for delete!")
      '
      CatSQL = "SELECT ID FROM ProdCat WHERE CatName = '" & [Forms]![Brand Category Configuration]![CatDrop1] & "';"
      rs1 = CurrentDb.OpenRecordset(CatSQL)
      rsCat = rs1!id

      '
      strUpd = "UPDATE ProdList SET ProdList.ProdCatID = 25 WHERE ProdCatID = " & rsCat 

      CurrentDb.Execute strUpd, dbFailOnError
      
      
      'strUpd = "SELECT * FROM ProdList WHERE ProdCatID = " & rsCat 

      'rs2 = CurrentDb.OpenRecordset(strUpd)
      'With rs2
      'Do While Not .EOF
      '!ProdCatID = "25"
      '.Update
      '.MoveNext
      'Loop
      'End With

      '
      strSQL = "DELETE FROM [ProdCat] WHERE [CatName] = '" & CatName & "'"

      CurrentDb.Execute strSQL, dbFailOnError

      Me.CatDrop1.Requery

      Me.txtCatSelect = ""

   Else

      'MsgBox ("Value is no for delete!")

   End If

End Sub

[Scyclone]

Wow, I am retarded. It was the ',' that was the issue :P

Anyway, the update worked like a charm.

Thanks again

[ssanfu]

Awesome, that worked like a charm

I am running into another update issue for some odd reason, this should be working, given that it follows the same coding.

=======

Private Sub cmdUpdate_Click()
Dim strUpd As String
Dim rsID As String
rsID = Me.CustDrop1.Column(0)
strUpd = "UPDATE CustList SET CustList.CustName = '" & txtCustName & "' , CustList.[Address 1] = '" & txtAdd1 & "' , CustList.[Address 2] = '" & txtAdd2 & "' , CustList.City = '" & txtCity & "' , CustList.State = '" & txtState & "' , CustList.Zip = '" & txtZip & "' , WHERE ID = " & rsID
MsgBox (strUpd)
CurrentDb.Execute strUpd, dbFailOnError

End Sub
==============

The error is saying

Runtime error 3144

Syntax error on Update statement

thoughts?

1) Is Me.CustDrop1.Column(0) a Long Integer? You have the variable rsID declared as a string. If it is a string, then you need delimiters around strID

2) Try taking out the comma in front of the WHERE. The comma indicates there should be another item in the list of fields.

3) There a NULL in on or more of the values that are being updated. Try using NZ(txtCustName,"")

4) I use Me.txtCustName when referring to controls on a form. Indicates that it is not a variable and not a field name.

5) ON A COPY of the MDB, try updating just one field first. If that works, add another field. Keep adding fields until you find the problem field or the update statement works.

[Scyclone]

Heh, yeah as soon as I posted it, I noticed the issue, changed it, tested it, face palmed :P