List box - I am desperate!

[John Jnr]

I have searched the forum and used every know possible solution to this!
All I want to do is click on the parts in a list box hit a delete button and it then deletes those records from the relative table.

Simple you think, then please help!

Here is some sample code for you yo laugh at, and represents last attempt before I pulled my hair out!

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click

Dim frm As Form, ctl As Control
Dim varItm As Variant, intI As Integer
Dim par As Recordset
Dim dbsamanda As Database
Dim leg As Long


Set frm = Forms!EnterParts
Set ctl = frm!List32
Set par = dbsamanda.OpenRecordset("Table1")
leg = par!PartNumber
For Each varItm In ctl.ItemsSelected
For intI = 0 To ctl.ColumnCount - 1
DeleteRecord par, leg
Next intI
Next varItm




Exit_Command40_Click:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click


Thanks in advance

 

[glynch]

Try something like this. Use the primary key of Table1 as a hidden field in your listbox. Write a query:

DELETE * FROM Table1 WHERE YourPrimaryKey = frm!List32
(btw, give the listbox a meaningful name)

Then put a delete button on the form, in the code behind the on_click event of the delete button, create a querydef object on your delete query (look in Help if you are not familiar with querydefs), and execute your querydef. The record will be gone.

Good Luck

 

[John Jnr]

Thank's Glynch,
However, I have two list boxes. I copy data from one list box to another, with a table behind the target list box. The user might copy two or three of the same date to the list box as the data is car parts. I therefor also need the user to be able to delete these if they make a mistake. I tried to code for a multiple selection using a sub procedure for 'DeleteRecord'. However my code does cause some glitches! My code is as follows:

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click

Dim MyDb As Database
Dim Myset As Recordset
Dim Mylist As String
Dim frm As Form, ctl As Control
Dim varItm As Variant
Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set Myset = MyDb.OpenRecordset("Table1")
Set frm = Forms!EnterParts
Set ctl = frm!List32

With Myset
.Index = "Table1PartNumber"
Mylist = Me!List32.Column(0)
End With

For Each varItm In ctl.ItemsSelected
MsgBox ctl.ItemData(varItm)
DeleteRecord Myset, Me!List32.Column(0)
Next varItm

Exit_Command40_Click:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub

Public Sub DeleteRecord(Myset As Recordset, Mylist As String)

With Myset
.Seek "=", Mylist
If .NoMatch Then
MsgBox "No Part " & Mylist & " in file!"
Else
.Delete
MsgBox "Part " & Mylist & " Deleted"
End If
End With

End Sub


I would appreciate some help with the code as I am relativeley new to VBA. However I will also try your sugestion. Can it cope with multiple selections?

Cheers

John

 

[John Jnr]

I cracked it . . . . . But!

For future reference. This code allows a multiple selection from the list box and then deletes all selected. However as you delete more (testing) it sometimes leaves a previous deleted area in the list box highlighted (black). This then produces a remark 'Invalid use of Null'. Does anyone know how to stop this???????

Anyway here is the code:

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click

Dim MyDb As Database
Dim Myset As Recordset
Dim Mylist As String
Dim frm As Form, ctl As Control
Dim varItm As Variant

Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set Myset = MyDb.OpenRecordset("Table1")
Set frm = Forms!EnterParts
Set ctl = frm!List32

With Myset
For Each varItm In ctl.ItemsSelected
.Index = "Table1PartNumber"
DeleteRecord Myset, ctl.ItemData(varItm)
Next varItm
End With

Exit_Command40_Click:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub

Public Sub DeleteRecord(Myset As Recordset, Mylist As String)

With Myset
.Seek "=", Mylist
If .NoMatch Then
MsgBox "No Part " & Mylist & " in file!"
Else
.Delete
MsgBox "Part " & Mylist & " Deleted"
End If
End With

End Sub

 

[John Jnr]

And I thought I had Cracked It?!?!? List Box Trouble

And I thought I had cracked it?

It deletes a multiple selection, but it sometimes deletes records I haven't selected. Please help I am going insane!

Cheers

J