Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

problems deleting a specific record

Discussion in 'Information Technology' started by andriulli@gmail.com, Jul 28, 2006.

  1. andriulli@gmail.com

    andriulli@gmail.com
    Expand Collapse
    Guest

    Hello.

    I have a listbox and a delete button. Records are being displayed in
    the listbox using three columns. The first column, TrackID is the
    primary key for the record. i want the user to be able to click the
    delete button, have a msg window popup and confirm the deletion, and
    then delete the record if the user clicks yes. my problem is that the
    record is not deleting, here is my code (History is the listbox):

    If MsgBox("Are you sure you wish to delete this ticket?", vbExclamation
    + vbYesNo + vbDefaultButton2, "Confirm Record Deletion") = vbYes Then

    DoCmd.SetWarnings False

    DoCmd.RunSQL "DELETE FROM [Equipment Tracking Log] WHERE [Equipment
    Tracking Log].EquipNumber = '" & Me.History.Value & "'"

    DoCmd.SetWarnings True

    End If

    Any help would be appreciated.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    Muslim Girl-Sikh Boy Marriage Problems- Please Help! Love & Marriage Oct 12, 2012
    Problems with Sikh Historiography-prof Jagdish Singh Sikh Video Aug 31, 2012

  3. Biz Enhancer

    Biz Enhancer
    Expand Collapse
    Guest

    Hi,

    Try replacing the "me.History. value" with "Forms![your form name]!history"
    You also need to delete the entire row using the tbl.*.

    DoCmd.RunSQL "DELETE [Equipment Tracking Log].*, [Equipment Tracking
    Log].EquipNumber FROM [Equipment Tracking Log] WHERE ((([Equipment Tracking
    Log].EquipNumber)=[Forms]![your form here]![history]));"

    Hope it helps.
    Regards,
    Nick.
    "andriulli@gmail.com" wrote:

    > Hello.
    >
    > I have a listbox and a delete button. Records are being displayed in
    > the listbox using three columns. The first column, TrackID is the
    > primary key for the record. i want the user to be able to click the
    > delete button, have a msg window popup and confirm the deletion, and
    > then delete the record if the user clicks yes. my problem is that the
    > record is not deleting, here is my code (History is the listbox):
    >
    > If MsgBox("Are you sure you wish to delete this ticket?", vbExclamation
    > + vbYesNo + vbDefaultButton2, "Confirm Record Deletion") = vbYes Then
    >
    > DoCmd.SetWarnings False
    >
    > DoCmd.RunSQL "DELETE FROM [Equipment Tracking Log] WHERE [Equipment
    > Tracking Log].EquipNumber = '" & Me.History.Value & "'"
    >
    > DoCmd.SetWarnings True
    >
    > End If
    >
    > Any help would be appreciated.
    >
    >
     
  4. andriulli@gmail.com

    andriulli@gmail.com
    Expand Collapse
    Guest

    I updated the statement to what you advised, but still no luck. I don't
    get any errors, the record just isn't deleted from the table. Here is
    my code:

    Private Sub DeleteEntryButton_Click()

    If MsgBox("Are you sure you wish to delete this ticket?", vbExclamation
    + vbYesNo + vbDefaultButton2, "Confirm Record Deletion") = vbYes Then

    DoCmd.SetWarnings False

    DoCmd.RunSQL "DELETE [Equipment Tracking Log].*, [Equipment Tracking
    Log].EquipNumber FROM [Equipment Tracking Log] WHERE ((([Equipment
    Tracking Log].EquipNumber) = [Forms]![CCUEquipmentList]![History]));"

    DoCmd.SetWarnings True

    End If

    End Sub
     
  5. Biz Enhancer

    Biz Enhancer
    Expand Collapse
    Guest

    Hmmm. That should have worked.
    I suspect that the problem may be concerning the "History" field. Is this a
    combo box or list? If so, then make sure the bound column is the right one
    for the equipNumber.
    Else if it is a textbox, do you get an exact match to the record you are
    trying to delete?

    If you can give a bit of info about the "history" field and control, that
    may unlock the problem.

    Regards,
    Nick.

    "andriulli@gmail.com" wrote:

    > I updated the statement to what you advised, but still no luck. I don't
    > get any errors, the record just isn't deleted from the table. Here is
    > my code:
    >
    > Private Sub DeleteEntryButton_Click()
    >
    > If MsgBox("Are you sure you wish to delete this ticket?", vbExclamation
    > + vbYesNo + vbDefaultButton2, "Confirm Record Deletion") = vbYes Then
    >
    > DoCmd.SetWarnings False
    >
    > DoCmd.RunSQL "DELETE [Equipment Tracking Log].*, [Equipment Tracking
    > Log].EquipNumber FROM [Equipment Tracking Log] WHERE ((([Equipment
    > Tracking Log].EquipNumber) = [Forms]![CCUEquipmentList]![History]));"
    >
    > DoCmd.SetWarnings True
    >
    > End If
    >
    > End Sub
    >
    >
     
  6. RoyVidar

    RoyVidar
    Expand Collapse
    Guest

    "andriulli@gmail.com" <andriulli@gmail.com> wrote in message
    <1153922865.652113.252220@p79g2000cwp.googlegroups.com>:
    > I updated the statement to what you advised, but still no luck. I
    > don't get any errors, the record just isn't deleted from the table.
    > Here is my code:
    >
    > Private Sub DeleteEntryButton_Click()
    >
    > If MsgBox("Are you sure you wish to delete this ticket?",
    > vbExclamation + vbYesNo + vbDefaultButton2, "Confirm Record
    > Deletion") = vbYes Then


    ' DoCmd.SetWarnings False

    > DoCmd.RunSQL "DELETE [Equipment Tracking Log].*, [Equipment Tracking
    > Log].EquipNumber FROM [Equipment Tracking Log] WHERE ((([Equipment
    > Tracking Log].EquipNumber) = [Forms]![CCUEquipmentList]![History]));"
    >
    > DoCmd.SetWarnings True
    >
    > End If
    >
    > End Sub


    What message do you get if you don't turn off the warnings? Also, would
    it differ if you included the value into the string, and not the
    reference
    ....Where [Equipment Tracking Log].EquipNumber) = " & _
    Me.History.Value

    If it's a text field, then add single quotes
    ....Where [Equipment Tracking Log].EquipNumber) = '" & _
    Me.History.Value & "'"

    Also, a little tricks, in stead of stuffing it all into a docmd
    statement, assign it to a string

    dim strSql as string
    strSql = "DELETE FROM [Equipment Tracking Log] e " & _
    "WHERE e.EquipNumber = " & Me.History.Value

    then run like this (here using the execute method of the current db)

    currentdb.execute strsql, dbfailonerror

    or you could do a

    debug.print strsql

    then pick up the sql statement in the immediate pane (ctrl+g), try
    studying it, or copy/paste to the SQL view of the query tool.

    --
    Roy-Vidar
     
  7. andriulli@gmail.com

    andriulli@gmail.com
    Expand Collapse
    Guest

    My History listbox is an unbounded listbox that shows all of the
    related tickets, through a query, a piece of equipment has. Tickets are
    kept track of in another table called Tickets. Everytime an employee
    signs out a piece of equipment they create a ticket. I want to be able
    to highlight a row in the History listbox and delete the ticket that
    corresponds to that row.
     
  8. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    If multiselect is on for the listbox it either has to be turned off (it will
    always yield Null) or you have
    to write additional code

    Dim CurVal As Variant
    Dim lb As Access.Listbox
    Dim Db As DAO.Database

    Set Db = Access.CurrentDb()
    Set lb = Me.History
    With lb
    For Each CurVal In .ItemsSelected
    Db.Execute "DELETE ... FROM ... WHERE ... = " & .ItemData(CurVal),
    DAO.dbFailOnError ' Enclosed By (') for Text Fields
    Next
    End With

    Set lb = Nothing
    Set Db = Nothing

    HTH

    Pieter

    PS Please all retain the full thread when answering


    <andriulli@gmail.com> wrote in message
    news:1153926332.481938.323200@b28g2000cwb.googlegroups.com...
    > My History listbox is an unbounded listbox that shows all of the
    > related tickets, through a query, a piece of equipment has. Tickets are
    > kept track of in another table called Tickets. Everytime an employee
    > signs out a piece of equipment they create a ticket. I want to be able
    > to highlight a row in the History listbox and delete the ticket that
    > corresponds to that row.
    >
     
  9. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    If multiselect is on for the listbox it either has to be turned off (it will
    always yield Null) or you have
    to write additional code

    Dim CurVal As Variant
    Dim lb As Access.Listbox
    Dim Db As DAO.Database

    Set Db = Access.CurrentDb()
    Set lb = Me.History
    With lb
    For Each CurVal In .ItemsSelected
    Db.Execute "DELETE ... FROM ... WHERE ... = " & .ItemData(CurVal),
    DAO.dbFailOnError ' Enclosed By (') for Text Fields
    Next
    End With

    Set lb = Nothing
    Set Db = Nothing

    HTH

    Pieter

    PS Please all retain the full thread when answering


    <andriulli@gmail.com> wrote in message
    news:1153926332.481938.323200@b28g2000cwb.googlegroups.com...
    > My History listbox is an unbounded listbox that shows all of the
    > related tickets, through a query, a piece of equipment has. Tickets are
    > kept track of in another table called Tickets. Everytime an employee
    > signs out a piece of equipment they create a ticket. I want to be able
    > to highlight a row in the History listbox and delete the ticket that
    > corresponds to that row.
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  10. Biz Enhancer

    Biz Enhancer
    Expand Collapse
    Guest

    OK. Assuming that the query on the ticket table was the ticketID as the first
    column and the Equipnumber in a column further down the query, you would need
    to ensure that the bound column is not column 1 (if that is the ticketid) but
    is the column that relates to equipNumber.

    Although do you want to be deleting from the [Equipment Tracking Log] or the
    [Ticket] table. If it is the created ticket in the Ticket table that is the
    target then surely the ticket table entry needs to be deleted.
    Mind you that is just a stab in the dark!

    Post the listbox source SQL, hopefully that will shed some more light.

    Regards,
    Nick.

    "andriulli@gmail.com" wrote:

    > My History listbox is an unbounded listbox that shows all of the
    > related tickets, through a query, a piece of equipment has. Tickets are
    > kept track of in another table called Tickets. Everytime an employee
    > signs out a piece of equipment they create a ticket. I want to be able
    > to highlight a row in the History listbox and delete the ticket that
    > corresponds to that row.
    >
    >
     

Share This Page