Solved

Edit/Delete List box Item

Posted on 2014-07-18
15
363 Views
Last Modified: 2014-07-18
I have a list box on my form that shows the results of a query based on a selection made in a combo box on that same form.

I want to be able to make edits to the records by selecting items in the list box.

So far I can't seem to make it work.

I have this piece of code, but I am not sure how to adjust it to a list box:

Private Sub DeleteRecord_Click()

Dim strSQL As String
 
strSQL = "DELETE [Teamid] and [STOID] FROM TeamSTO WHERE " & _
          "TeamSTO.[TeamID] = '" & Me![lstExitingTeam] & "'" And "TeamSTO.[STOid] = '" & Me![CmbSTO]
          
          
          
CurrentDb.Execute strSQL, dbFailOnError
 
Me![EventList].Requery ' requery the list
 
End Sub

Open in new window


Although, this code is just to delete the item.

That might be enough, but I would really like the user to be able to edit the records as well.
0
Comment
Question by:Megin
  • 6
  • 5
  • 4
15 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40205312
if STOid is Number

strSQL = "DELETE [Teamid] and [STOID] FROM TeamSTO WHERE" _
          & " TeamSTO.[TeamID] = '" & Me![lstExitingTeam] & "' And TeamSTO.[STOid] = " & Me![CmbSTO]

if STOid is TEXT

strSQL = "DELETE [Teamid] and [STOID] FROM TeamSTO WHERE" _
          & " TeamSTO.[TeamID] = '" & Me![lstExitingTeam] & "' And TeamSTO.[STOid] = " & Me![CmbSTO] & "'"
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40205319
if STOid is Number And TeamID is TEXT

strSQL = "DELETE [Teamid] and [STOID] FROM TeamSTO WHERE" _
          & " TeamSTO.[TeamID] = '" & Me![lstExitingTeam] & "' And TeamSTO.[STOid] = " & Me![CmbSTO]

if STOid is Number And TeamID is Number

strSQL = "DELETE [Teamid] and [STOID] FROM TeamSTO WHERE" _
          & " TeamSTO.[TeamID] = " & Me![lstExitingTeam] & " And TeamSTO.[STOid] = " & Me![CmbSTO]


if STOid is TEXT And TeamID is Number

strSQL = "DELETE [Teamid] and [STOID] FROM TeamSTO WHERE" _
          & " TeamSTO.[TeamID] = " & Me![lstExitingTeam] & " And TeamSTO.[STOid] = " & Me![CmbSTO] & "'"          
         
if STOid is TEXT And TeamID is TEXT

strSQL = "DELETE [Teamid] and [STOID] FROM TeamSTO WHERE" _
          & " TeamSTO.[TeamID] = '" & Me![lstExitingTeam] & "' And TeamSTO.[STOid] = " & Me![CmbSTO] & "'"
0
 

Author Comment

by:Megin
ID: 40205334
It still isn't doing anything. I am not even getting an error. Am I missing something that would stop the code from even trying to run?

Both the TeamID and the STOID are numbers.

Private Sub DeleteRecord_Click()

Dim strsql As String


strsql = "DELETE [Teamid] and [STOID] FROM TeamSTO WHERE" _
           & " TeamSTO.[TeamID] = '" & Me![lstExitingTeam] & "' And TeamSTO.[STOid] = " & Me![CmbSTO]

CurrentDb.Execute strsql, dbFailOnError

Me![EventList].Requery
 
End Sub

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40205362
use this
if STOid is Number And TeamID is Number

strSQL = "DELETE [Teamid] and [STOID] FROM TeamSTO WHERE" _
          & " TeamSTO.[TeamID] = " & Me.[lstExitingTeam] & " And TeamSTO.[STOid] = " & Me.[CmbSTO]
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40205366
oops, sorry
use this
if STOid is Number And TeamID is Number

strSQL = "DELETE * FROM TeamSTO WHERE" _
          & " TeamSTO.[TeamID] = " & Me.[lstExitingTeam] & " And TeamSTO.[STOid] = " & Me.[CmbSTO]
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40205403
Is your list box single or multi-select?  Multi-select listboxes don't really have a value, you would have to loop through the itemsselected collection of the listbox.

Are you certain that the bound column in the listbox is the one you want to reference to get the TeamID?  If not, you will have to use the columns() property of the listbox to get the appropriate value, something like:

me.lstExitingTeam.columns(1)    'keeping in mind that columns is zero based, so columns(1) actually refers to the second column in your rowsource.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40205405
If you are not getting an error, are you certain that the code is firing at all.  Put a breakpoint after the line that defines your SQL string and then print out the SQL String to make sure it is valid.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Megin
ID: 40205526
I found what was keeping the code from firing and fixed that.

Now I am getting "Run-time error '2465: Microsoft Access can't find the field '|1' referred to in your expression"

I don't know what that is referring to.

I have also realized that I only need to reference the list box. Both of the items I need are in that box.

So, I decided to go with an entirely different piece of code. Of course, it is not working either!

Here it is:
Private Sub DeleteRecord_Click()
Dim frm As Form
Dim ctl As Control
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Variant
Set frm = Forms("frm_TeamSTO")
Set ctl = frm![lstExistingTeam]
Set db = db.OpenRecordset("TeamSTO")

For Each i In ctl.ItemsSelected
    rst.Delete
        rst("STOid") = ctl.Column(3, i)
        rst("Teamid") = ctl.Column(4, i)
    rst.Update
Next i

rst.Close
Set rst = Nothing

End Sub

Open in new window



The error is "Run-time error '01': Object variable or With block variable not set"

the line that is highlighted in the debugger is
 
Set db = db.OpenRecordset("TeamSTO")

Open in new window


Am I going in a better direction with this?

Should I go back to working with the code I originally had?

I took the second code from another part of my db where I was adding records from a list box. I just changed it to rst.delete.  Is that right?

Thank you!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40205591
Personally, I prefer to use SQL to delete records.  Given your earlier statement that both  [STOid] and [Teamid] are numbers, I would change the loop to look like:

For Each i In ctl.ItemsSelected
     strSQL = "DELETE FROM TeamSTO WHERE [STOid] = " & ctl.Column( 3, i) _
                  & " AND [Teamid] = " & ctl.Column(4, i)
     db.execute strSQL, dbfailonerror
Next i
ctl.requery

Open in new window

But that is basically what Rey told you above.
0
 

Author Comment

by:Megin
ID: 40205626
To be sure, this is what I have now. Are there too many Dim statements?


Private Sub DeleteRecord_Click()
Dim frm As Form
Dim ctl As Control
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Variant
Set frm = Forms("frm_TeamSTO")
Set ctl = frm![lstExistingTeam]
Set db = db.OpenRecordset("TeamSTO")

Dim strsql As String


For Each i In ctl.ItemsSelected
     strsql = "DELETE FROM TeamSTO WHERE [STOid] = " & ctl.Column(3, i) _
                  & " AND [Teamid] = " & ctl.Column(4, i)
     db.Execute strsql, dbFailOnError
Next i
ctl.Requery

Open in new window



I am still getting the same error.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40205634
do you mean this error:

The error is "Run-time error '01': Object variable or With block variable not set"

That error is because you did not instantiate the db object

Private Sub DeleteRecord_Click()
Dim frm As Form
Dim ctl As Control
Dim db As DAO.Database
Dim rst As DAO.Recordset  ' Delete this line
Dim i As Variant
Set frm = Forms("frm_TeamSTO")
Set ctl = frm![lstExistingTeam]
Set db = db.OpenRecordset("TeamSTO")     'delete this line too
Set db = Currentdb   'Add this line

Dim strsql As String

For Each i In ctl.ItemsSelected
     strsql = "DELETE FROM TeamSTO WHERE [STOid] = " & ctl.Column(3, i) _
                  & " AND [Teamid] = " & ctl.Column(4, i)
     db.Execute strsql, dbFailOnError
Next i
ctl.Requery
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40205643
The form objects may be overkill, if this button is on the same form that the listbox is on, I would normally just do:

set ctl = me.lstExisting
For each i in ctl.Itemsselected
0
 

Author Comment

by:Megin
ID: 40205646
IT WORKED!!!!!!!!!!!


So I didn't need anything about the record set in there?

But, I would have needed it if I had used that other piece of code, correct? It would be necessary because I would have had to use "rst" in the code. Because the VBA syntax references the recordset, while the SQL does not. Correct?

It totally works. I just want to make sure I understand why it works.

Also, why do you prefer SQL over VBA in this case?  Is it just a preference, or does one work better than the other?
0
 

Author Closing Comment

by:Megin
ID: 40205649
Thank you for being so patient and explaining everything to me. The code works well now!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40205789
for inserting records, I will use the recordset technique, but for deleting records, I generally use SQL because I'm lazy and the recordset method just takes more code.  I'm comfortable with writing SQL, so I know what my results will be.  You can try the recordset method like this.
Private Sub DeleteRecord_Click()

    Dim ctl As Control
    Dim db As DAO.Database
    Dim rst As DAO.Recordset 
    Dim i As Variant
    Dim strCriteria As String

    Set ctl = Forms("frm_TeamSTO").lstExistingTeam
    Set db = Currentdb   'Add this line
    set rst = db.openrecordset("TeamSTO", dbopendynaset, dbfailonerror)

    For Each i In ctl.ItemsSelected
         strCriteria = "[STOid] = " & ctl.Column(3, i) & " AND [Teamid] = " & ctl.Column(4, i)
         with rst
            .findfirst strCriteria
             if .nomatch then 
                msgbox "record not found", strCriteria
            Else
                .Delete
            End If

        End With

    Next i
    ctl.Requery 

    rst.Close
    set rst = nothing
    set db = nothing
End Sub

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now