Edit/Delete List box Item

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.
MeginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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
Rey Obrero (Capricorn1)Commented:
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
MeginAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
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
Rey Obrero (Capricorn1)Commented:
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
Dale FyeCommented:
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
Dale FyeCommented:
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
MeginAuthor Commented:
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
Dale FyeCommented:
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
MeginAuthor Commented:
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
Dale FyeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
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
MeginAuthor Commented:
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
MeginAuthor Commented:
Thank you for being so patient and explaining everything to me. The code works well now!
0
Dale FyeCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.