?
Solved

Edit/Delete List box Item

Posted on 2014-07-18
15
Medium Priority
?
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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
 

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 48

Expert Comment

by:Dale Fye
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 48

Accepted Solution

by:
Dale Fye earned 2000 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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

771 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