In order to delete

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Hi all,
I have a form with a list box which contain records.
On the same form i want to put a delete button, when I click it will delete
the highlighted record.
what code i need to put in to accomplish that, in the on click event?

Thank you for the help,
Tom
 
Tom said:
Hi all,
I have a form with a list box which contain records.
On the same form i want to put a delete button, when I click it will delete
the highlighted record.
what code i need to put in to accomplish that, in the on click event?

Thank you for the help,
Tom
Click to expand...


Me.lstNameList.Selected(Me.lstNameList.ListIndex + 1) = False

Bye.
Alessandro(IT).
 
Hi,
Thank you for the very quick response.
I might missing something here because it doesn't work.
the name of the list is List0.
Maybe you can give me the full argument for that event?

Thanks,
Tom
 
Tom said:
Hi,
Thank you for the very quick response.
I might missing something here because it doesn't work.
the name of the list is List0.
Maybe you can give me the full argument for that event?

Thanks,
Tom
Click to expand...


Yes i missing to explane you to rename my ListName in the Exemple.

On CommandButton Click_Event put this code:

Me.List0.Selected(Me.List0.ListIndex + 1) = False

I think will be better now.

Alessandro(IT)
 
This solution doesn't make sense to me either if I'm understanding your
quetion correctly. You want to delete the record in the table, correct?

If you want to do that you could write some simple SQL code and execute it.
Assuming that the list box contains the primary key field in it's bound
column, I think this might work:

Dim lngRecordsAffected as Long

If Not IsNull(lstRecordsAffected) then
CurrentProject.Connection.Execute "Delete * From tblTableName Where
PrimaryKeyFieldName = " & lstListBoxName, lngRecordsAffected
End If

lstListBoxName.Requery

If your primary key field has a text data format you'll need to add
quotation marks as follows:

CurrentProject.Connection.Execute "Delete * From tblTableName Where
PrimaryKeyFieldName = '" & lstListBoxName & "'", lngRecordsAffected

lngRecordsAffected will return the number of records that actually get
deleted. The argument is not optional so you must include a variable in its
place.

This method will not prompt you for confirmation so you should probably use
a message box to make sure the user has a chance to abort the deletion
before it happens.
 
Tom Stoddard said:
This solution doesn't make sense to me either if I'm understanding your
quetion correctly. You want to delete the record in the table, correct?

If you want to do that you could write some simple SQL code and execute it.
Assuming that the list box contains the primary key field in it's bound
column, I think this might work:

Dim lngRecordsAffected as Long

If Not IsNull(lstRecordsAffected) then
CurrentProject.Connection.Execute "Delete * From tblTableName Where
PrimaryKeyFieldName = " & lstListBoxName, lngRecordsAffected
End If

lstListBoxName.Requery

If your primary key field has a text data format you'll need to add
quotation marks as follows:

CurrentProject.Connection.Execute "Delete * From tblTableName Where
PrimaryKeyFieldName = '" & lstListBoxName & "'", lngRecordsAffected

lngRecordsAffected will return the number of records that actually get
deleted. The argument is not optional so you must include a variable in its
place.

This method will not prompt you for confirmation so you should probably use
a message box to make sure the user has a chance to abort the deletion
before it happens.
Click to expand...


He wont to remove only HighLited ROW or the connected Record...???
For the first work good, for the second solution you give right one.....!

Bye.
Alessandro(IT)
 
Sorry, I meant to use your listbox name. The code should read:

Dim lngRecordsAffected as Long

If Not IsNull(List0) then
CurrentProject.Connection.Execute "Delete * From tblTableName Where
PrimaryKeyFieldName = " & List0, lngRecordsAffected
End If

lstListBoxName.Requery

and:

CurrentProject.Connection.Execute "Delete * From tblTableName Where
PrimaryKeyFieldName = '" & List0 & "'", lngRecordsAffected
 
Add a command button to your form and put this code. This
is the code from the command button wizard.

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

Jim
 
Hi all,
Thank you for your help.
I put evething in place: tbl name, the key. I tried the last code from Tom
and i got this massage
"Syntax error in "Where" clause"
what`s that mean?
Here all the details:
table:Products
key: ProductID
List0


Thanks,
Tom
 
Hi Tom,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this issue.

From your description, I understand that you wanted to delete the highlighted record within teh
list box on the form. Have I fully understood you? If there is anything I misunderstood, please
feel free to let me know.

I'm not sure of your Access version and I think the following method may be all-purpose in
Access 2000 and Access 2002. This method is deleting the records in the Products table so
you should backup the database first. Please also make sure that there is no Primary Key
violation when you delete the record in the table, otherwise, the deletion will be avoided.

''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Command2_Click()

Dim strSQL As String

If Not IsNull(List0) Then
strSQL = "Delete * From Products Where ProductID=" & Me.List0.Value
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Me.List0.Requery
End If

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

In Access 2002 or later, you can use RemoveItem to delete the highlighted record in your list
box.

For how to move the items between the list box, please reference the following articles:
177117 ACC: How to Move List Box Items to Another List Box (7.0/97)
http://support.microsoft.com/?id=177117

278378 How to Use AddItem and RemoveItem to Move Selections from One List Box
http://support.microsoft.com/?id=278378

Does that answer your question? Please apply my suggestions above and let me know if this
helps resolve your problem. If there is anything more I can do to assist you, please feel free to
post it in the group.


Best regards,

Billy Yao
Microsoft Online Support
 
Hi Tom,

Thank you for your update! I'm not sure why it doesn't work on your side. Have you received
any error message? It works fine on my side.

However, as I mentioned before, you should make sure that there is NO Primary Key Violation
when you delete the record in that 'Product' table. To ensure this, please make a copy of that
table with the new table name 'Product2' and re-create a new form with a listbox and delete
button.

''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Command2_Click()

Dim strSQL As String

If Not IsNull(List0) Then
strSQL = "Delete * From Products2 Where ProductID=" & Me.List0.Value
'' DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
'' DoCmd.SetWarnings True
Me.List0.Requery
End If

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

As you use Access XP, you can also use the new feature of RemoveItem in the listbox. For
detailed information on how to remove a selected item in the listbox, please read the following
step by step article:

278378 How to Use AddItem and RemoveItem to Move Selections from One List Box
http://support.microsoft.com/?id=278378


Best regards,

Billy Yao
Microsoft Online Support
 
Hi all,
Thank you for your help, now it`s works.
Im not sure why iy didnt before.
When i think about that, maybe because i started with a text box and then i
converted it to a list.
anyway, thanks again
Tom
Tom said:
Hi all,
Thank all, thank you billy.
It doesnt work.
I`m using Access xp.

Thanks,
Tom
"Billy Yao [MSFT]" said:
Hi Tom,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this issue.

From your description, I understand that you wanted to delete the highlighted record within teh
list box on the form. Have I fully understood you? If there is anything
Click to expand...
I
misunderstood, please
feel free to let me know.

I'm not sure of your Access version and I think the following method may be all-purpose in
Access 2000 and Access 2002. This method is deleting the records in the Products table so
you should backup the database first. Please also make sure that there
Click to expand...
is
no Primary Key
violation when you delete the record in the table, otherwise, the
Click to expand...
deletion
will be avoided.
''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Command2_Click()

Dim strSQL As String

If Not IsNull(List0) Then
strSQL = "Delete * From Products Where ProductID=" & Me.List0.Value
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Me.List0.Requery
End If

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

In Access 2002 or later, you can use RemoveItem to delete the
Click to expand...
highlighted
record in your list
box.

For how to move the items between the list box, please reference the following articles:
177117 ACC: How to Move List Box Items to Another List Box (7.0/97)
http://support.microsoft.com/?id=177117

278378 How to Use AddItem and RemoveItem to Move Selections from One
Click to expand...
List
Box
http://support.microsoft.com/?id=278378

Does that answer your question? Please apply my suggestions above and
Click to expand...
let
me know if this
helps resolve your problem. If there is anything more I can do to assist you, please feel free to
post it in the group.


Best regards,

Billy Yao
Microsoft Online Support
Click to expand...
Click to expand...
 
Back
Top