MS Access - Update/Delete Error

[Scyclone]

Hello Everyone,

I have a semi-convoluted bit of code that I am working with and am getting a strange error.

=

Run-Time Error '438'
Object doesn't support this property or method

=

What I am trying to accomplish is that once a category is selected (displaying its name in a textbox), should I go to delete the category, it will prompt me for my approval. Assuming I say yes, it will prepare to remove the category by going to every product entered that is using this current category and changing its value to '25' or uncategorized.

Below in code is a select to find the id of the category, a select/update (which is causing the error to occur, on the line that I will bold/color below) and then delete the row afterwards.

========

Private Sub cmdCatDelete_Click()
Dim value As String
Dim CatName As String
Dim strSQL As String
Dim rsCat As String
Dim CatSQL As String
Dim strUpd As String

CatName = Me.txtCatSelect
value = MsgBox("Are you sure that you want to delete this category? 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 value = 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 = "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

========

Any help would be appreciated

Thanks

[boblarson]

Personally, I would use

Do Until .EOF

but the other should not throw an error.

Before you do anything else, you might try removing the extra double quote from this line:

strUpd = "SELECT * FROM ProdList WHERE ProdCatID = " & rsCat & ""

But if that doesn't help then I would try a decompile to see if that helps (make sure to keep a copy BEFORE doing it just in case anything goes wrong).

[Scyclone]

Just tried removing the double quote and switched to Do Until .EOF

Unfortunately the error still persists, hmm.

[boblarson]

Just tried removing the double quote and switched to Do Until .EOF

Unfortunately the error still persists, hmm.

Then the decompile is probably necessary. That is usually the case when something that should normally work doesn't.

See here for more about decompiling:
http://www.granite.ab.ca/access/decompile.htm

[Scyclone]

Followed that, the error still persists unfortunately

[boblarson]

Is ProdCatID text or numeric?

[Scyclone]

It is set to Numeric

[ssanfu]

strUpd = "SELECT * FROM ProdList WHERE ProdCatID = " & rsCat & ""

If rsCat is a number, then you don't have to append a null string to the end. All you need is

strUpd = "SELECT * FROM ProdList WHERE ProdCatID = " & rsCat

(numbers do not need delimiters)

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

Maybe I am missing something, but don't you need to have an .EDIT statment????

       Do While Not .EOF
             .EDIT         
               !ProdCatID = "25"
            .Update
            .MoveNext
        Loop

One last question. Why are you looping through a record set instead of using an Update query??


Seems to me that it would be quicker.....

[boblarson]

Why are you looping through a record set instead of using an Update query??


Seems to me that it would be quicker.....

Darn, that was going to be my next question

I was going to suggest it because, not only would it be faster, but it would be more efficient as well.

[goodguy]

Also, the value for ProdCatID is being passed as a string.
And neither rs1 nor rs2 have been declared or initialized.

[ssanfu]

Also, the value for ProdCatID is being passed as a string.
And neither rs1 nor rs2 have been declared or initialized.

Now HOW did I miss that???

[goodguy]

ssanfu: Don't be too hard on yourself. Whereas a pro like you will try for high-level fixes, footmen like me see the low-level fixes only.

[ssanfu]

ssanfu: Don't be too hard on yourself. Whereas a pro like you will try for high-level fixes, footmen like me see the low-level fixes only.

Pro??? Not yet, but a wannabe ....

Actually, I suffer from CRS..... and it has flared up, big time!!!

[goodguy]

Would that be Congenital Rubella Syndrome?

[Scyclone]

Hehe you guys have been quite busy over the weekend :P

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.

Would there be a better way to code that?

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.