problems deleting a specific record

[andriulli]

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.

 

[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]

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

 

[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.

 

[RoyVidar]

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.

 

[andriulli]

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.

 

[Pieter Wijnen]

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

 

[Pieter Wijnen]

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

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.

--

 

[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.