Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Edit/Delete List box Item

Posted on 2014-07-18
15
Medium Priority
?
374 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 49

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 49

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 49

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 49

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 49

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 49

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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 has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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

577 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