problems deleting a specific record

#1 (**permalink**) 28-Jul-2006, 08:41 AM

Hello.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13914-problems-deleting-a-specific-record.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914

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 (**permalink**) 28-Jul-2006, 08:41 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
> record is not deleting, here is my code (History is the listbox):
>
> If MsgBox("Are you sure you wish to delete this ticket?", vbExclamation
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
> + 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.
>
>

#3 (**permalink**) 28-Jul-2006, 08:41 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
Tracking Log].EquipNumber) = [Forms]![CCUEquipmentList]![History]));"

DoCmd.SetWarnings True

End If

End Sub

#4 (**permalink**) 28-Jul-2006, 08:41 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
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:
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
> 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 (**permalink**) 28-Jul-2006, 08:42 AM

"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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
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 " & _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
"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

#6 (**permalink**) 28-Jul-2006, 08:42 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
to highlight a row in the History listbox and delete the ticket that
corresponds to that row.

#7 (**permalink**) 28-Jul-2006, 08:42 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
Set Db = Nothing

HTH

Pieter

PS Please all retain the full thread when answering

wrote in message
news:1153926332.481938.323200@b28g2000cwb.googlegr oups.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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
> 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 (**permalink**) 28-Jul-2006, 08:42 AM

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

wrote in message
news:1153926332.481938.323200@b28g2000cwb.googlegr oups.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
> 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

#9 (**permalink**) 28-Jul-2006, 08:42 AM

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13914
> 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.
>
>