Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Scyclone is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    51

    MS Access - Update/Delete Error

    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

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    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).

  3. #3
    Scyclone is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    51
    Just tried removing the double quote and switched to Do Until .EOF

    Unfortunately the error still persists, hmm.

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by Scyclone View Post
    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

  5. #5
    Scyclone is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    51
    Followed that, the error still persists unfortunately

  6. #6
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Is ProdCatID text or numeric?

  7. #7
    Scyclone is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    51
    It is set to Numeric

  8. #8
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,044
    Code:
    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)





    Code:
        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????
    Code:
    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.....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by ssanfu View Post
    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.

  10. #10
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    225
    Also, the value for ProdCatID is being passed as a string.
    And neither rs1 nor rs2 have been declared or initialized.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,044
    Quote Originally Posted by goodguy View Post
    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???
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #12
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    225
    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.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,044
    Quote Originally Posted by goodguy View Post
    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!!!
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  14. #14
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    225
    Would that be Congenital Rubella Syndrome?

  15. #15
    Scyclone is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    51
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update/Delete Outlook appointments from Access
    By IdleJack in forum Programming
    Replies: 1
    Last Post: 09-12-2011, 03:48 PM
  2. Delete and Update VBA - Revisited
    By shexe in forum Programming
    Replies: 1
    Last Post: 08-26-2010, 10:34 AM
  3. Delete and Update VBA
    By shexe in forum Programming
    Replies: 0
    Last Post: 08-25-2010, 01:44 PM
  4. Update / delete and recreate access tables
    By metro17 in forum Access
    Replies: 4
    Last Post: 09-23-2009, 04:45 PM
  5. Replies: 1
    Last Post: 12-09-2005, 09:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums