List box (deselect/delete)

J

jenny

Hi,
I have a list box that is bound to a table with unique
key, but I wanted to deselect some items in the list box,
at the same time delete theose Items in the bound table
that were deselected in the list box. I used a recordset
(dbOpenDynaset) for the bound table,and using loop,
findfirst and findnext methods for search to match so
that the selected content is the same as the contents in
the table, then do a recordset.delete , right now I can
only delete items from the very first of the list box. if
I selected some other items, ie. 4th or 5th items in the
list box, and tried to delete,nothing happens. though
I've found the matching contents for deletion. Does
anyone know why it can only delete first row in the list
box, not some other rows?

I've pasted my codes in the following, if anybody can
help me to take a look at them, let me what's wrong with
them, it will be greated appreciated.
Thanks for any help.
Jenny

''''''''' the following codes are for list box deselecting

Private Sub lstHomeGIDRemove_DblClick(Cancel As Integer)
Dim db as database
Dim rstRemove As Recordset
Dim strCriteria As String
Dim intSub As Integer
Dim intRecordCount As Integer
Dim varRemoved As Variant
Dim varRemoved2 As Variant
Dim intID As Variant
Dim intCount2 As Integer

Set rstRemove = db.OpenRecordset
("TmpSpecificEmployer", dbOpenDynaset)

If Not rstRemove.EOF And Not rstRemove.BOF Then

For Each varRemoved In Me!
lstHomeGIDRemove.ItemsSelected()
intID = Me!lstHomeGIDRemove.Column(0,
varRemoved) ' look for id for the table to match
rstRemove.MoveLast
intRecordCount = rstRemove.RecordCount
rstRemove.MoveFirst

intCount2 = rstRemove!intCount
strCriteria = intCount2 = intID
rstRemove.FindFirst strCriteria
If Not rstRemove.NoMatch Then
rstRemove.Edit
rstRemove.Delete
rstRemove.Requery
Me.lstHomeGIDRemove.Requery
Exit Sub
Else

For intSub = 2 To intRecordCount

rstRemove.MoveNext
intCount2 = rstRemove!intCount
strCriteria = intCount2 = intID
rstRemove.FindNext strCriteria
If Not rstRemove.NoMatch Then
rstRemove.Edit
rstRemove.Delete
rstRemove.Requery
Me.lstHomeGIDRemove.Requery
Exit Sub
End If

Next intSub
End If

Next varRemoved

Else
MsgBox "There is no data in the list box. Please
select the year(s) from the combox first.",
vbInformation, "CQM"
Me.cboYear.SetFocus
Me.cboYear.Dropdown
End If
End Sub
 
K

Ken Snell

I haven't gone through your code in detail, but I do see a syntactical error
in this code snippet:

intID = Me!lstHomeGIDRemove.Column(0, varRemoved) ' look for id for the
table to match
rstRemove.MoveLast
intRecordCount = rstRemove.RecordCount
rstRemove.MoveFirst

intCount2 = rstRemove!intCount
strCriteria = intCount2 = intID
rstRemove.FindFirst strCriteria

In this snippet, you're setting strCriteria equal to the text string "True"
or "False", depending upon whether intCount2 has the same value of intID
("True" if yes, "False" if no). Then you're doing a FindFirst based on the
string "True" or the string "False"; you're not doing a match of the value
of a field to another value.

I believe you need to change this line
strCriteria = intCount2 = intID

to this line
strCriteria = "[intCount] = " & intID

Try that and see if it works better for you.
 
J

Jenny

Hi Ken,

Thank you so much for your help. You saved my life.

Jenny
-----Original Message-----
I haven't gone through your code in detail, but I do see a syntactical error
in this code snippet:

intID = Me!lstHomeGIDRemove.Column(0, varRemoved) ' look for id for the
table to match
rstRemove.MoveLast
intRecordCount = rstRemove.RecordCount
rstRemove.MoveFirst

intCount2 = rstRemove!intCount
strCriteria = intCount2 = intID
rstRemove.FindFirst strCriteria

In this snippet, you're setting strCriteria equal to the text string "True"
or "False", depending upon whether intCount2 has the same value of intID
("True" if yes, "False" if no). Then you're doing a FindFirst based on the
string "True" or the string "False"; you're not doing a match of the value
of a field to another value.

I believe you need to change this line
strCriteria = intCount2 = intID

to this line
strCriteria = "[intCount] = " & intID

Try that and see if it works better for you.
--
Ken Snell
<MS ACCESS MVP>

jenny said:
Hi,
I have a list box that is bound to a table with unique
key, but I wanted to deselect some items in the list box,
at the same time delete theose Items in the bound table
that were deselected in the list box. I used a recordset
(dbOpenDynaset) for the bound table,and using loop,
findfirst and findnext methods for search to match so
that the selected content is the same as the contents in
the table, then do a recordset.delete , right now I can
only delete items from the very first of the list box. if
I selected some other items, ie. 4th or 5th items in the
list box, and tried to delete,nothing happens. though
I've found the matching contents for deletion. Does
anyone know why it can only delete first row in the list
box, not some other rows?

I've pasted my codes in the following, if anybody can
help me to take a look at them, let me what's wrong with
them, it will be greated appreciated.
Thanks for any help.
Jenny

''''''''' the following codes are for list box deselecting

Private Sub lstHomeGIDRemove_DblClick(Cancel As Integer)
Dim db as database
Dim rstRemove As Recordset
Dim strCriteria As String
Dim intSub As Integer
Dim intRecordCount As Integer
Dim varRemoved As Variant
Dim varRemoved2 As Variant
Dim intID As Variant
Dim intCount2 As Integer

Set rstRemove = db.OpenRecordset
("TmpSpecificEmployer", dbOpenDynaset)

If Not rstRemove.EOF And Not rstRemove.BOF Then

For Each varRemoved In Me!
lstHomeGIDRemove.ItemsSelected()
intID = Me!lstHomeGIDRemove.Column(0,
varRemoved) ' look for id for the table to match
rstRemove.MoveLast
intRecordCount = rstRemove.RecordCount
rstRemove.MoveFirst

intCount2 = rstRemove!intCount
strCriteria = intCount2 = intID
rstRemove.FindFirst strCriteria
If Not rstRemove.NoMatch Then
rstRemove.Edit
rstRemove.Delete
rstRemove.Requery
Me.lstHomeGIDRemove.Requery
Exit Sub
Else

For intSub = 2 To intRecordCount

rstRemove.MoveNext
intCount2 = rstRemove!intCount
strCriteria = intCount2 = intID
rstRemove.FindNext strCriteria
If Not rstRemove.NoMatch Then
rstRemove.Edit
rstRemove.Delete
rstRemove.Requery
Me.lstHomeGIDRemove.Requery
Exit Sub
End If

Next intSub
End If

Next varRemoved

Else
MsgBox "There is no data in the list box. Please
select the year(s) from the combox first.",
vbInformation, "CQM"
Me.cboYear.SetFocus
Me.cboYear.Dropdown
End If
End Sub
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi Selection Combo Box Query 1
LIST BOX HEADACHE FOR NEWBIE 7
More Problems with List Box 2
List Box Selection to Run report 3
Access Query limitations 4
multi select list box in a form as a query parameter 3
Multi Select list Box 2
Can the below code be modified for select. Drive Letter, Dir. 2

Top