Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB code for deleting. (2000)

    Hi,
    Wondered whether anyone would be willing to rustle up a quick VB code for me. Its a standard Control with a delete function to remove an entry from a database. I would like some form of Confirmation on the control to the affect of, "Are you sure you want to delete this record?" with confirmation of Yes or No.
    I've had a quick delve myself but I know nothing of VB so I'm doing it blind.
    Thanks,
    Nigel

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB code for deleting. (2000)

    Create a command button named cmdDelete.
    In the Event tab of the properties window, select Event Procedure in the On Click event.
    Click the ... to the right of the dropdown arrow.
    Make the code look like this:

    Private Sub cmdDelete_Click()
    On Error GoTo ErrHandler
    RunCommand acCmdDeleteRecord
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    When the user clicks the button, Access will ask for a confirmation to delete the record(s) by default.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code for deleting. (2000)

    Thanks Hans,
    Perfick!!!!
    Ta,
    Nigel

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code for deleting. (2000)

    another thing to consider for situations where you need to do something a little more involved than deleting the current record is to use the SQL DELETE in VBA to find and delete records. One advantage is SQL strings can be very precise and you can delete more than one record (say, related records on other fields).

    for example,
    <font face="Georgia"> Set dbs = CurrentDb
    dbs.Execute "DELETE * FROM [MainTable] where [IntakeID] = " & IntakeID & ";"
    dbs.Execute "DELETE * FROM [Sub Table1] where [IntakeID] = " & IntakeID & ";"
    dbs.Execute "DELETE * FROM [Sub Table2] where [IntakeID] = " & IntakeID & ";"
    </font face=georgia>

    Will go and delete all the associated records on three tables. (that is, records with the same IntakeID number.) You probably don't need it for your circumstance but I find it handy to be able to interact with tables using SQL in code at times.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code for deleting. (2000)

    Hi Steve

    Wouldn

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB code for deleting. (2000)

    Sometimes you don't want deletes to cascade, you want the existence of child records to prevent deleting the parent. In that case, referential integrity without cascading deletes is the method to use. To make Steve's proposed method work with referential integrity the parent record would have to be deleted *after* deleting the child records instead of before.
    Charlotte

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code for deleting. (2000)

    You can do that as well but I've seen cases where referential integrity was set up inappropriately (some tables were included that shouldn't have been) and it was a big pain to undo the relationships to perform the delete I wanted (or risk deleting data!). Of course, this is more of an issue of a bad design (I didn't do it -- this was an inherited project). But, again, using referential integrity and Cascade deletes may lock you into a data relationship model and requires a bit more planning beforehand.

    Personally, I like having as much control as possible -- cascade deletes sounds like a good idea but what if you've added some functionality later and don't take the previous settings into account? My SQL DELETE suggestion does the same thing as a Cascade Delete but it's completely explicit and easy to modify. Also, and this is more of a philosophical issue, I like people to know and understand and use SQL (and not depend on the Query builder exclusively) because this widens your ability to do things with data. If you're moving a db to a webpage knowing SQL is very handy. If you go on to use some other DB than Access, knowing SQL will be highly transferrable. I don't know if cascade delete/ref int. is as common as plan old SQL.

    The other point to all this is, of course, performance. My suggestion may indeed be less optimal than some other methods. A stored query, apparently, is optimized and perhaps ref. int./cascade deletes are as well. Dunno -- but I suspect it is, for a high-traffic db backend.

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code for deleting. (2000)

    Hi Steve

    I agree with your approach and Charlotte's comment.

    Nigel mentioned he knew nothing of VB, so I thought in his case, to go along with Hans suggestion, if there were child records involved, RI would be the easy way to go.

    The beauty and power of Access, MW2DTST

    John

  9. #9
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code for deleting. (2000)

    Another alternative, one I've found to be VERY useful is the ability to use message box input this way: -

    Dim Response as String

    Response = msgbox("Give the user the alternatives",VBYesNo,"Message box title")

    then a simple if ...... then loop for the two (three if you use VbYesNoCancel) alternatives.

    As someone who knows next to nothing about VBA this has allowed me to solve a few user interaction problems.

    Ian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •