1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Delete a record in Access based on a combo box selection

Discussion in 'Business Applications' started by Jack_Maloney, Dec 21, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Jack_Maloney

    Jack_Maloney Thread Starter

    Joined:
    Dec 21, 2010
    Messages:
    4
    Hi,

    I am new to access and am struggling on trying to delete a record. Basically, I want the user to select an option from a drop down list (Select_Project_Type_cbx) and then click on a 'Delete' button which will delete the record associated with the drop down option selected.

    This is the code I have so far:

    Private Sub Delete_Project_Type_btn_Click()
    Dim sDeleteRecordSQL As String
    sDeleteRecordSQL = "Delete * From Project_Type_tbl " & _
    " Where Project_Type_Name.Name = " & Select_Project_Type_cbx.Value
    Me.Select_Project_Type_cbx.RowSource = sDeleteRecordSQL
    Me.Select_Project_Type_cbx.Requery

    End Sub


    When the user clicks on the delete button it just removes ALL options from the combo box. Nothing is deleted from the table "Project_Type_tbl".

    If someone can point me in the right direction on where I am going wrong here it would be much appreciated!

    Thanks,
    Jack
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Jack_Maloney, welcome to the forum.
    This line is your problem
    Me.Select_Project_Type_cbx.RowSource = sDeleteRecordSQL
    it should be
    set rs = sDeleteRecordSQL
    where rs is dimmed as
    dim rs as object
    You could also run a delete query using the combo as a Criteria, or have the form's record source based on the table and convert the combo to a "Find Combo" and then delete the rcord on the form.
    You should also have a Confiming Message something like
    response = msgbox("Are you sure you want to Delete " & Select_Project_Type_cbx & "", vbYesNo + vbExclamation + vbDefaultButton2)
     
  3. Jack_Maloney

    Jack_Maloney Thread Starter

    Joined:
    Dec 21, 2010
    Messages:
    4
    Thanks for the reply OBP.

    I now get a message saying 'type mismatch' on the 'Set rs = sDeleteRecordSQL' line.

    Code is:
    Dim sDeleteRecordSQL As String
    Dim rs As Object
    sDeleteRecordSQL = "Delete * From Project_Type_tbl " & _
    "Where Project_Type_Name.Name = " & Select_Project_Type_cbx.Value
    Set rs = sDeleteRecordSQL
    Me.Select_Project_Type_cbx.Requery

    Also, where do you enter the code for the confirmation delete message? Is this entered below the code already entered in VB?

    Thanks,
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Sorry , I should have checked what I was writing instead of relying on my old memory.
    It should be
    Set rs = CurrentDb.OpenRecordset(sDeleteRecordSQL)

    The Message box should go before you actually doe the deletion like this

    Dim response
    response = MsgBox("Are You Really Sure You Want to Delete Record?", vbYesNo + vbExclamation + vbDefaultButton2)
    If response = vbNo Then Exit Sub ' User chose No.
    'User Chose Yes
     
  5. Jack_Maloney

    Jack_Maloney Thread Starter

    Joined:
    Dec 21, 2010
    Messages:
    4
    Im now getting a run time error '3219' when it executes that line. Says 'Invalid Operation'. Any ideas why this might be occuring?

    Thanks,
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Which line?
     
  7. Jack_Maloney

    Jack_Maloney Thread Starter

    Joined:
    Dec 21, 2010
    Messages:
    4
    When it executes this line:

    Set rs = CurrentDb.OpenRecordset(sDeleteRecordSQL)
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Perhaps it does not like the Delete, you could try changing it to a Select query and see if it returns a Record for RS Recordset based on the SQL statement.
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, try the Run SQL version instead
    DoCmd.RunSQL sDeleteRecordSQL
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/969803

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice