Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Create Delete command button

    Hi, I have written this code for a delete button, when it runs through, there doesnt seem to be a functional error but doesnt delete the row in the table.

    How can I get around this issue?

    Jez

    Private Sub btnDeleteInput_Click()
    If Me.Pharmacy <> "DO NOT DELETE THIS ROW" Then 'My Key field in table
    varResponse = MsgBox("Are You Sure?", vbYesNo, cApplicationName)
    If varResponse = vbNo Then
    Me.Undo
    Exit Sub
    End If
    DoCmd.SetWarnings False
    Dim sQRY As String
    sQRY = "DELETE tblData.* FROM tblData " & _
    "WHERE tblData.FormNumber = " & Me.FormNumber & ";"
    DoCmd.RunSQL sQRY
    Me.RecordSource = "SELECT tblData.* FROM tblData WHERE " & _
    "tblData.Pharmacy = ' ';"
    Me.txtDummy.SetFocus
    DoCmd.SetWarnings True
    End If
    End Sub

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Try this:
    sQRY = "DELETE tblData.FormNumber FROM [tblData] WHERE (((tblData.FormNumber)=" & [FORMS]![FormName]![Field name] & "));"

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    If all you want to do is delete the CURRENT record in a form try

    RunCommand acCmdDeleteRecord

    (or DoCmd.RunCommand acCmdDeleteRecord)

    in place of

    Dim sQRY As String
    sQRY = "DELETE tblData.* FROM tblData " & _
    "WHERE tblData.FormNumber = " & Me.FormNumber & ";"
    DoCmd.RunSQL sQRY


    There are a number of things that puzzelse me in this and your other thread

    1) Why do you keep changing the form RecordSource (BTW you need to Me.Requery after do this) instead of changing the filter.

    2) Why do you have Me.Undo if Delete is aborted ?

    3) What is meant by
    If Me.Pharmacy <> "DO NOT DELETE THIS ROW" Then 'My Key field in table

    This is not normally data for a key field, which I understand is FormNumber!!


    MTB

Posting Permissions

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