Delete record on list box

[Job]

Hi All,

Please, it would like a help.
I have a form with a list box (lstDic) based in a query (qryDic) and I would
like to select a register in the list box to delete it.
My code is the following one:

Private Sub cmdExcluir_Click()
Dim MySet As Recordset, varItem As Variant

For Each varItem In lstDic.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("qryDic", dbOpenDynaset)

MySet.FindFirst "code = " & [Me.qryDic] & "Code = '" & lstDic.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem
lstDic.Requery
End Sub

But the following error (2465) appears: "The Microsoft Office Access cannot
locate the related field "|" in your expression."

Someone can help me?

Thanks.

 

[Guest]

Try it this way:

Private Sub cmdExcluir_Click()
Dim MySet As Recordset, varItem As Variant

Set MySet = CurrentDb.OpenRecordset("qryDic", dbOpenDynaset)
With Me.lstDic
For Each varItem In .ItemsSelected
With MySet
.FindFirst "Code = '" & .ItemData(varItem) & "'"
If .NoMatch = False Then
.Delete
End If
End With
Next varItem
End With
MySet.Close
Set MySet = Nothing
Me.lstDic.Requery
End Sub

 

[Job via AccessMonster.com]

Klatuu, thanks for your attention,

But I received the following message from error:
"Method or member of data not found." referring to .ItemData

My query (qryDic) have four columns as to follow:

Code Port Eng Span
0 alarme alarm alarma

And in the form I have a box of listing (lstDic) with the three last columns.

Can you help me.

Thanks again.

Try it this way:

Private Sub cmdExcluir_Click()
Dim MySet As Recordset, varItem As Variant

Set MySet = CurrentDb.OpenRecordset("qryDic", dbOpenDynaset)
With Me.lstDic
For Each varItem In .ItemsSelected
With MySet
.FindFirst "Code = '" & .ItemData(varItem) & "'"
If .NoMatch = False Then
.Delete
End If
End With
Next varItem
End With
MySet.Close
Set MySet = Nothing
Me.lstDic.Requery
End Sub

Hi All,

[quoted text clipped - 22 lines]

Thanks.

 

[Guest]

That is because the code field in your query is not a column in your list
box. You will need to either add it to the list box or do the findfirst on
one of the columns in your list box.

Klatuu, thanks for your attention,

But I received the following message from error:
"Method or member of data not found." referring to .ItemData

My query (qryDic) have four columns as to follow:

Code Port Eng Span
0 alarme alarm alarma

And in the form I have a box of listing (lstDic) with the three last columns.

Can you help me.

Thanks again.

Try it this way:

Private Sub cmdExcluir_Click()
Dim MySet As Recordset, varItem As Variant

Set MySet = CurrentDb.OpenRecordset("qryDic", dbOpenDynaset)
With Me.lstDic
For Each varItem In .ItemsSelected
With MySet
.FindFirst "Code = '" & .ItemData(varItem) & "'"
If .NoMatch = False Then
.Delete
End If
End With
Next varItem
End With
MySet.Close
Set MySet = Nothing
Me.lstDic.Requery
End Sub

Hi All,

[quoted text clipped - 22 lines]

Thanks.

 

[Job via AccessMonster.com]

Klattu forgives me.
But still it does not function.
The field "Code" is in list box (lstDic) and placed the following line of
command:

Set MySet = CurrentDb.OpenRecordset("qryDic", dbOpenDynaset)
With Me.lstDic
For Each varItem In .ItemsSelected
With MySet
.FindFirst "Code = '" & Me.lstDic.Column(0) & "'"
If .NoMatch = False Then
.Delete
End If
End With
Next varItem
End With

But the following error happens: "Type of incompatible data in the criterion
expression."

Please, some idea.

Thanks again.

That is because the code field in your query is not a column in your list
box. You will need to either add it to the list box or do the findfirst on
one of the columns in your list box.

Klatuu, thanks for your attention,

[quoted text clipped - 38 lines]

 

[Guest]

I based my answer on your previous post:
Code Port Eng Span
0 alarme alarm alarma

And in the form I have a box of listing (lstDic) with the three last columns.

----------------------
That would indicate it is not in the list box.

I believe this may be the problem:
.FindFirst "Code = '" & Me.lstDic.Column(0) & "'"
I think it should be:
.FindFirst "Code = '" & Me.lstDic.ItemsSelected(varItem).Column(0) & "'"

Klattu forgives me.
But still it does not function.
The field "Code" is in list box (lstDic) and placed the following line of
command:

Set MySet = CurrentDb.OpenRecordset("qryDic", dbOpenDynaset)
With Me.lstDic
For Each varItem In .ItemsSelected
With MySet
.FindFirst "Code = '" & Me.lstDic.Column(0) & "'"
If .NoMatch = False Then
.Delete
End If
End With
Next varItem
End With

But the following error happens: "Type of incompatible data in the criterion
expression."

Please, some idea.

Thanks again.

That is because the code field in your query is not a column in your list
box. You will need to either add it to the list box or do the findfirst on
one of the columns in your list box.

Klatuu, thanks for your attention,

[quoted text clipped - 38 lines]

Thanks.

 

[Job via AccessMonster.com]

Klatuu,
Unhappyly the problem was not solved, now the error is "Qualifying invalid"
for ItemsSelected(varItem).
Thanks for the patience.

I based my answer on your previous post:
Code Port Eng Span
0 alarme alarm alarma

And in the form I have a box of listing (lstDic) with the three last columns.

----------------------
That would indicate it is not in the list box.

I believe this may be the problem:
.FindFirst "Code = '" & Me.lstDic.Column(0) & "'"
I think it should be:
.FindFirst "Code = '" & Me.lstDic.ItemsSelected(varItem).Column(0) & "'"

Klattu forgives me.
But still it does not function.

[quoted text clipped - 29 lines]

 

[Guest]

Well, turns out it is a bit more sticky than I thought. The only syntax that
works is
Me.lstDic.Column(0)
If you try to qualify which row in any way, you get an error.

The only way I could make this work (using an old app I have laying around
that has a list box with multiselect) and multiple columns) was to set the
ListIndex property to a selected item:
Me.lstDic.ListIndex = Me.lstDic.ItemsSelected(0)
?Me.lstDic.Column(0) returns the currect value
It works, but is clears all the other selections, so you don't know what
other selections you have. So, here is what I came up with. I did not write
the code, sorry, don't have the time at the moment.

Once you have all the selections made, create an array with the same number
of elements as the number of items selected.
Loop through the itemsselected and put each value into the array.
Loop through the array and set the ListIndex property to the value in the
array element.
Each iteration will let you use the above syntax to do your lookup.

Now, if you need to put the selections back like they were, loop through the
array again and use the Selected property to reselect each row that was
originally selected.

I hope this helps. Sorry I answered incorrectly earlier.

I would test it out, but I don't have anything

Klatuu,
Unhappyly the problem was not solved, now the error is "Qualifying invalid"
for ItemsSelected(varItem).
Thanks for the patience.

I based my answer on your previous post:
Code Port Eng Span
0 alarme alarm alarma

And in the form I have a box of listing (lstDic) with the three last columns.

----------------------
That would indicate it is not in the list box.

I believe this may be the problem:
.FindFirst "Code = '" & Me.lstDic.Column(0) & "'"
I think it should be:
.FindFirst "Code = '" & Me.lstDic.ItemsSelected(varItem).Column(0) & "'"

Klattu forgives me.
But still it does not function.

[quoted text clipped - 29 lines]

Thanks.

 

[Job via AccessMonster.com]

Thanks for the attempts. However today I could not work in the problem. In
the next week I will be of vacation. When to come back of them, I try again.
If you it will be able to help me, I will find excellent.

Well, turns out it is a bit more sticky than I thought. The only syntax that
works is
Me.lstDic.Column(0)
If you try to qualify which row in any way, you get an error.

The only way I could make this work (using an old app I have laying around
that has a list box with multiselect) and multiple columns) was to set the
ListIndex property to a selected item:
Me.lstDic.ListIndex = Me.lstDic.ItemsSelected(0)
?Me.lstDic.Column(0) returns the currect value
It works, but is clears all the other selections, so you don't know what
other selections you have. So, here is what I came up with. I did not write
the code, sorry, don't have the time at the moment.

Once you have all the selections made, create an array with the same number
of elements as the number of items selected.
Loop through the itemsselected and put each value into the array.
Loop through the array and set the ListIndex property to the value in the
array element.
Each iteration will let you use the above syntax to do your lookup.

Now, if you need to put the selections back like they were, loop through the
array again and use the Selected property to reselect each row that was
originally selected.

I hope this helps. Sorry I answered incorrectly earlier.

I would test it out, but I don't have anything

Klatuu,
Unhappyly the problem was not solved, now the error is "Qualifying invalid"

[quoted text clipped - 20 lines]

 

[Guest]

Enjoy your vacation. post back to this thread and I will see if we can make
it work. In the meantime, I will work on a solution.

Thanks for the attempts. However today I could not work in the problem. In
the next week I will be of vacation. When to come back of them, I try again.
If you it will be able to help me, I will find excellent.

Well, turns out it is a bit more sticky than I thought. The only syntax that
works is
Me.lstDic.Column(0)
If you try to qualify which row in any way, you get an error.

The only way I could make this work (using an old app I have laying around
that has a list box with multiselect) and multiple columns) was to set the
ListIndex property to a selected item:
Me.lstDic.ListIndex = Me.lstDic.ItemsSelected(0)
?Me.lstDic.Column(0) returns the currect value
It works, but is clears all the other selections, so you don't know what
other selections you have. So, here is what I came up with. I did not write
the code, sorry, don't have the time at the moment.

Once you have all the selections made, create an array with the same number
of elements as the number of items selected.
Loop through the itemsselected and put each value into the array.
Loop through the array and set the ListIndex property to the value in the
array element.
Each iteration will let you use the above syntax to do your lookup.

Now, if you need to put the selections back like they were, loop through the
array again and use the Selected property to reselect each row that was
originally selected.

I hope this helps. Sorry I answered incorrectly earlier.

I would test it out, but I don't have anything

Klatuu,
Unhappyly the problem was not solved, now the error is "Qualifying invalid"

[quoted text clipped - 20 lines]

Thanks.

 

[Job via AccessMonster.com]

Thanks, when I to come back, in we work them.

Enjoy your vacation. post back to this thread and I will see if we can make
it work. In the meantime, I will work on a solution.

Thanks for the attempts. However today I could not work in the problem. In
the next week I will be of vacation. When to come back of them, I try again.

[quoted text clipped - 34 lines]