Delete record

[Job via AccessMonster.com]

Hi, I have an old problem.

I have a form (frmDic) that he contains a list box (lstDic) with 4 columns
(Code, Port, Eng and Esp) based in a query (qryDic). I would like to erase a
selected register using a button (Exclude).

I have the following code:

Private Sub cmdExclude_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='" & Me.lstDic.ItemsSelected(varItem).Column
(0) & "'"
If .NoMatch = False Then
.Delete
End If
End With
Next varItem
End With
MySet.Close
Set MySet = Nothing
Me.Requery
End Sub

This code cause the following error of compilation: "Invalid qualifier."
referring the "ItemsSelected".

Somebody can help me?

Thanks.

Jorge Barreto

 

[Douglas J Steele]

Me.lstDic.ItemData(varItem).Column(0) & "'"

 

[Job via AccessMonster.com]

Me.lstDic.ItemData(varItem).Column(0) & "'"

Hi, I have an old problem.

[quoted text clipped - 33 lines]

Jorge Barreto

Thanks Douglas. But also it did not function. Now Error 424 happens: "The
object is obligator."

 

[Douglas J Steele]

Make sure that you're getting a DAO recordset. That means changing

Dim MySet As Recordset

to

Dim MySet As DAO.Recordset

If that doesn't solve the problem, try removing your "With Me.lstDic". In
other words, replace:

With Me.lstDic
For Each varItem In .ItemsSelected
With MySet
.FindFirst "Code='" & Me.lstDic.ItemData(varItem).Column(0)
& "'"
If .NoMatch = False Then
.Delete
End If
End With
Next varItem
End With

For Each varItem In Me.lstDic.ItemsSelected
With MySet
.FindFirst "Code='" & Me.lstDic.ItemData(varItem).Column(0) &
"'"
If .NoMatch = False Then
.Delete
End If
End With
Next varItem

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Me.lstDic.ItemData(varItem).Column(0) & "'"

Hi, I have an old problem.

[quoted text clipped - 33 lines]

Jorge Barreto

Thanks Douglas. But also it did not function. Now Error 424 happens: "The
object is obligator."

 

[Dirk Goldgar]

Hi, I have an old problem.

I have a form (frmDic) that he contains a list box (lstDic) with 4
columns (Code, Port, Eng and Esp) based in a query (qryDic). I would
like to erase a selected register using a button (Exclude).

I have the following code:

Private Sub cmdExclude_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='" &
Me.lstDic.ItemsSelected(varItem).Column (0) & "'"
If .NoMatch = False Then
.Delete
End If
End With
Next varItem
End With
MySet.Close
Set MySet = Nothing
Me.Requery
End Sub

This code cause the following error of compilation: "Invalid
qualifier." referring the "ItemsSelected".

Somebody can help me?

Thanks.

Jorge Barreto

I think what you're looking for is

.FindFirst "Code='" & Me.lstDic.Column(0, varItem) & "'"

Or, if Column(0) is the bound column, you could just use

.FindFirst "Code='" & Me.lstDic.ItemData(varItem) & "'"

 

[Job via AccessMonster.com]

Hi, I have an old problem.

[quoted text clipped - 33 lines]

Jorge Barreto

I think what you're looking for is

.FindFirst "Code='" & Me.lstDic.Column(0, varItem) & "'"

Or, if Column(0) is the bound column, you could just use

.FindFirst "Code='" & Me.lstDic.ItemData(varItem) & "'"

Douglas and Dirk, thanks for you help.

Making what it was suggested, the code was thus:

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

Set MySet = CurrentDb.OpenRecordset("qryDic", dbOpenDynaset)

With Me.lstDic
For Each varItem In lstDic.ItemsSelected
With MySet
' .FindFirst "Codigo='" & Me.lstDic.Column(0, varItem) & "'"
' .FindFirst "Codigo='" & Me.lstDic.ItemData(varItem).Column(0)
& "'"
' .FindFirst "Codigo='" & Me.lstDic.ItemData(varItem) & "'"
.FindFirst "Codigo='" & Me.lstDic.Column(0) & "'"
If .NoMatch = False Then
.Delete
End If
End With
Next varItem
End With
MySet.Close
Set MySet = Nothing
Me.Requery
End Sub

However, all the alternatives do not function. Emitting the following error:
"Incompatible type of data in the criteria expression."

Curiously:
When I leave for the design mode and I come back toward the form, the data of
the register to be deleted are copied on the first register of the table.

It will be that this aid?

Sorry my english, please!!!

 

[Douglas J Steele]

What data type is Codigo? If it's numeric, change your code to:

..FindFirst "Codigo=" & Me.lstDic.Column(0)

If it's text, then are you sure that first column actually contains a value?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, I have an old problem.

[quoted text clipped - 33 lines]

Jorge Barreto

I think what you're looking for is

.FindFirst "Code='" & Me.lstDic.Column(0, varItem) & "'"

Or, if Column(0) is the bound column, you could just use

.FindFirst "Code='" & Me.lstDic.ItemData(varItem) & "'"

Douglas and Dirk, thanks for you help.

Making what it was suggested, the code was thus:

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

Set MySet = CurrentDb.OpenRecordset("qryDic", dbOpenDynaset)

With Me.lstDic
For Each varItem In lstDic.ItemsSelected
With MySet
' .FindFirst "Codigo='" & Me.lstDic.Column(0, varItem) & "'"
' .FindFirst "Codigo='" & Me.lstDic.ItemData(varItem).Column(0)
& "'"
' .FindFirst "Codigo='" & Me.lstDic.ItemData(varItem) & "'"
.FindFirst "Codigo='" & Me.lstDic.Column(0) & "'"
If .NoMatch = False Then
.Delete
End If
End With
Next varItem
End With
MySet.Close
Set MySet = Nothing
Me.Requery
End Sub

However, all the alternatives do not function. Emitting the following error:
"Incompatible type of data in the criteria expression."

Curiously:
When I leave for the design mode and I come back toward the form, the data of
the register to be deleted are copied on the first register of the table.

It will be that this aid?

Sorry my english, please!!!

 

[Job via AccessMonster.com]

Douglas,

Really "Codigo" is a numeric field and its tip solved my problem.

Thank you very much for your help.

What data type is Codigo? If it's numeric, change your code to:

.FindFirst "Codigo=" & Me.lstDic.Column(0)

If it's text, then are you sure that first column actually contains a value?
[quoted text clipped - 24 lines]

' .FindFirst "Codigo='" & Me.lstDic.Column(0, varItem) & "'"
' .FindFirst "Codigo='" & Me.lstDic.ItemData(varItem).Column(0)
& "'"
' .FindFirst "Codigo='" & Me.lstDic.ItemData(varItem) & "'"

[quoted text clipped - 20 lines]

Sorry my english, please!!!

 

[Job via AccessMonster.com]

Dears, I me sorry, but I think that the problem was solved, but now when I
exclude a register any, its content is copied for the first register.

The actual code is:

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

Set MySet = CurrentDb.OpenRecordset("qryDic", dbOpenDynaset)

With Me.lstDic
For Each varItem In lstDic.ItemsSelected
With MySet
.FindFirst "Codigo=" & Me.lstDic.Column(0)
If .NoMatch = False Then
.Delete
End If
End With
Next varItem
End With
MySet.Close
Set MySet = Nothing
Me.Refresh
End Sub

Somebody has a new idea?

Douglas,

Really "Codigo" is a numeric field and its tip solved my problem.

Thank you very much for your help.

What data type is Codigo? If it's numeric, change your code to:

[quoted text clipped - 13 lines]

 

[Douglas J Steele]

Is the listbox bound or unbound? If it's bound, then you will run into
problems if you're deleting values from it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dears, I me sorry, but I think that the problem was solved, but now when I
exclude a register any, its content is copied for the first register.

The actual code is:

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

Set MySet = CurrentDb.OpenRecordset("qryDic", dbOpenDynaset)

With Me.lstDic
For Each varItem In lstDic.ItemsSelected
With MySet
.FindFirst "Codigo=" & Me.lstDic.Column(0)
If .NoMatch = False Then
.Delete
End If
End With
Next varItem
End With
MySet.Close
Set MySet = Nothing
Me.Refresh
End Sub

Somebody has a new idea?

Douglas,

Really "Codigo" is a numeric field and its tip solved my problem.

Thank you very much for your help.

What data type is Codigo? If it's numeric, change your code to:

[quoted text clipped - 13 lines]

Sorry my english, please!!!

 

[Job via AccessMonster.com]

Is the listbox bound or unbound? If it's bound, then you will run into
problems if you're deleting values from it.

Dears, I me sorry, but I think that the problem was solved, but now when I
exclude a register any, its content is copied for the first register.

[quoted text clipped - 34 lines]

Douglas, if I understood its question, the box of listing is unbound,
therefore I can include and exclude registers.

My project is a small techical glossary of naval terms, in Portuguese,
English and Spanish, where the users of our company will be able to always
collaborate with new terms.

Thanks again.