Delete Items from listbox

[nim180]

Hi Everyone,

I have a combobox with 2 options "DISPOSED" and "IN USE", depending on the option chosen a listbox is populate with "Asset ID's". I have a button to delete the items in the listbox but i cant seem to get it working. The code is as follows.

Dim strSQL As String, List As ListBox, itm
   
   Set List = Me!List236
   
      If MsgBox("This Action Cannot be Reversed ,Do You Really Want to Delete Assets?", vbQuestion + vbYesNo) = vbYes Then
   
   For Each itm In List.ItemsSelected
   
      strSQL = "DELETE Asset_ID FROM tblAssets Where (Asset_ID)=" & List.ItemData(itm) & ";"
            
      DoCmd.RunSQL strSQL

   Next
   
      Me.Requery
      List.Requery
   
      Set List = Nothing
   
   MsgBox ("Assets have been deleted")
      End If

Can someone see where i might be going wrong?

Nim

 

[TheAceMan1]

How are ya nim180 . . .

Assuming your making selections in the Listbox before you hit the button, I see two posibilities:
[ol][li][blue]Requery[/blue] doesn't work for a listbox, however . . .

[blue]      List.RowSource = List.RowSource[/blue]

. . . does!

Also, the form requery . . .

[blue]      Me.Requery[/blue]

. . . isn't needed (unless your reasons require it).[/li]
[li]If by chance the data type of [blue]Asset_ID[/blue] is Text, then the SQL changes too . . .

[blue]      strSQL = "DELETE Asset_ID " & _
	       "FROM tblAssets " & _
	       "Where (Asset_ID) = [red][b]'[/b][/red]" & List.ItemData(itm) & "[red][b]'[/b][/red];"[/blue]

[/li][/ol]
Its possible the [blue]column reference[/blue] for the listbox (List.ItemData(itm)) is not correct. The above will bring this to the surface depending on your answers.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

nim180 . . .

Bump to Top!

Have you forgotton this guy? . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[nim180]

Hey aceman sorry about that was still applying different methods to achieve the goal before i replied. List.rowsource worked perfect, Asset_ID was a text field so that was also an error. One thing that is annoying me is when i click the button to delete the records i get an access custom message "You are about to delete 1 row from the specified table" how do i suppress this message.

The onerror event of the form wont seem to work for me when i have used it before. I have used the following code on the onerror event but it doesnt seem to work.

If DataErr = 3022 Then
    MsgBox "The Job Number you have entered is already in use Please re-enter Unique Job Number"
    response = acDataErrContinue 
  End If

also when i get that custom delete message fom access and i decide to click no dont delete i get a runtime error 2501
"The RunSQL action was cancelled"

Thanks for your help,
Nim

 

[JesOakley]

Howabout one or more of

    Application.SetOption "Confirm Record Changes", False
    Application.SetOption "Confirm Document Deletions", False
    Application.SetOption "Confirm Action Queries", False

just before you do the delete, remembering to set them back to true immediately afterwards?