Delete record

  • Thread starter Thread starter Job via AccessMonster.com
  • Start date Start date
J

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
 
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!)


Job via AccessMonster.com said:
Douglas said:
Me.lstDic.ItemData(varItem).Column(0) & "'"
Hi, I have an old problem.
Click to expand...
[quoted text clipped - 33 lines]
Jorge Barreto
Click to expand...
Click to expand...

Thanks Douglas. But also it did not function. Now Error 424 happens: "The
object is obligator."
Click to expand...
 
Job via AccessMonster.com said:
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
Click to expand...

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) & "'"
 
Dirk said:
Hi, I have an old problem.
Click to expand...
[quoted text clipped - 33 lines]
Jorge Barreto
Click to expand...

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) & "'"
Click to expand...
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!!!
 
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!)


Job via AccessMonster.com said:
Dirk said:
Hi, I have an old problem.
Click to expand...
[quoted text clipped - 33 lines]
Jorge Barreto
Click to expand...

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) & "'"
Click to expand...
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!!!
Click to expand...
 
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) & "'"
Click to expand...
[quoted text clipped - 20 lines]
Sorry my english, please!!!
Click to expand...
Click to expand...
 
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:
Click to expand...
[quoted text clipped - 13 lines]
Click to expand...
 
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!)


Job via AccessMonster.com said:
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:
Click to expand...
[quoted text clipped - 13 lines]
Sorry my english, please!!!
Click to expand...
Click to expand...
Click to expand...
 
Douglas said:
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.
Click to expand...
[quoted text clipped - 34 lines]
Click to expand...

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.
 
Back
Top