Solved

Edit/Delete List box Item

Posted on 2014-07-18
15
368 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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