Delete entry in combobox

[judgehopkins]

I need to allow the end-users of my application to delete the entries in a combobox. I suspect the code should look something like the following; I am just not sure where to put it. Suggestions?

Private Sub WHERE DOES THIS GO?(NewData As String, Response As Integer)
'allows user to delete an entry in propertycounty
If MsgBox("Delete " & NewData & " from list?", vbYesNo, "Delete this county?") = vbYes Then
    Dim db As Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Counties")
    rst.Delete
        rst!PropertyCounties = NewData
    rst.Update
    Response = acDataErrAdded
    rst.Close
    Else
Response = acDataErrContinue
Me.Undo
MsgBox "Use a county that is already in the list.", vbOKOnly, "Don't delete county!"
End If

[PinkPanther2003]

Hi Judge,

AFAIK you can't do this by triggering an event in combobox.

I am a bit skeptical about your intentions here (with the combobox only of course!).

The only suggestion I would have is use a command button (delete's the value from the combobox and from the lookup when clicked).

You could also do the same with a user right clicking over the control (use the Mousedown event and Button = 2 - there's a constant for it but I can't remember it).

HTH,

Peter

[KnooKie]

what nechanism are you allowing the users to delete an entry in the combo box with ?

A button ?
A separate form ?
A checkbox ?
A listbox ?
Telepathy ?

[pachecf]

You might consider creating a table for your list. If you want to sort the information you can do so by entering a SQL at the Row Source property of a combo box or a list box. It’s easier to manage, you can delete or populate your list.

I started by creating a table which I named tbl_test_list with the fields ID = AutoNumber and LIST_ITEM = Text. On en empty form I placed a List box with the row source property:

SELECT DISTINCTROW [tbl_Test_List].[LIST_ITEM] FROM [tbl_Test_List];

I don’t need the ID field.

I placed a command button named cmdDelete with caption Delete or you can type the symbol – and format to bold giving the idea that minus indicates remove. If you decide that you want your users to add items to the list, you can place another command button with the caption to +, but it will require a different script.

Build an event for the cmdDelete button as fallow:

Private Sub cmdDelete_Click()

On Error GoTo errHndlr ' If the user presses the delete button without selecting an item from the list,
' the system will generate an error.

Dim dbs As Database
Dim rst As Recordset
Dim myID As Long
Dim ListItem As String
Dim retVal As Long

'Get the item from the list
retVal = myList.ListIndex
ListItem = myList.Column(0, retVal)

'Do you want to delete?
If MsgBox("Delete " & ListItem & " from the list?", vbQuestion + vbYesNo) = vbYes Then
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM TBL_TEST_LIST WHERE LIST_ITEM ='" & ListItem & "'")
rst.Delete
rst.Close
myList.Requery ' Requery the list to display the new list
Else
Exit Sub
End If

Exit Sub

errHndlr:
If ERR.NUMBER = 94 Then ' The user did not select from the list and pressed the delete button
MsgBox "Please select an item from the list.", vbInformation, "Delete"
Exit Sub
Else ' If by any chance there is another error encountered
MsgBox ERR.NUMBER & " " & ERR.DESCRIPTION, vbCritical, "Delete Error"
Exit Sub
End If
End Sub


I hopt that helps!

[judgehopkins]

I got some expert help and came up with this.

‘Here is what I put on the delete county form
'
' Notes:
' ---
' 1) The code created was in response to a newsgroup posting
' 2) The underscore character used in the code is a line-continuation character
'

Private Sub cmdCancel_Click()

  On Error GoTo Proc_Err
  
  DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name
  
Proc_Exit:
  Exit Sub

Proc_Err:
  MsgBox Prompt:=Err.Description, Buttons:=vbExclamation
  Resume Proc_Exit
  
End Sub

Private Sub cmdOK_Click()

  On Error GoTo Proc_Err
  
  If IsNull(Me.cboCounties) Then
    
    ' The user clicked OK without selecting an entry
    '
    MsgBox _
      Prompt:="A selection must be made before proceeding.", _
      Buttons:=vbInformation + vbOKOnly
    Me.cboCounties.SetFocus
  
  Else
  
    ' The user made a selection. Display a "second chance" prompt to be sure
    ' the user wants to delete the entry
    '
    If vbYes = MsgBox _
      ( _
        Prompt:="You have selected a county to be deleted." & vbCrLf & "Are you sure?", _
        Buttons:=vbExclamation + vbYesNo + vbDefaultButton2 _
      ) Then
    
        ' The user wants to delete the entry
        If DeleteEntry(Me.cboCounties) Then
          MsgBox _
            Prompt:="The selected county was successfuly deleted.", _
            Buttons:=vbInformation + vbOKOnly
          
          ' Now that the entry was deleted, requery the combo box so that
          ' the entry is removed from the list. Otherwise, Access gives a
          ' weird result in the place where the county existed.
          '
          Me.cboCounties.Requery
          
          ' Clear the combo box so another entry can be selected.
          Me.cboCounties = Null
          
        Else
          MsgBox _
            Prompt:="The selected county could not be deleted.", _
            Buttons:=vbExclamation + vbOKOnly
        End If
    
    End If
  
  End If
  
Proc_Exit:
  Exit Sub

Proc_Err:
  MsgBox Prompt:=Err.Description, Buttons:=vbExclamation
  Resume Proc_Exit

End Sub

Private Function DeleteEntry( _
  pstrEntry As String) As Boolean

'
' Comments: This will delete the County entry that was selected.
'
  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim fRetVal As Boolean

  On Error GoTo Proc_Err

  ' This is how you execute a query programmatically and how
  ' you pass it a parameter.
  '
  Set dbs = CurrentDb()
  Set qdf = dbs.QueryDefs("qryDeleteCountyEntry")
  qdf![prmCounty] = pstrEntry
  qdf.Execute
  
  ' Return a TRUE value to indicate the process completed successfully
  '
  fRetVal = True

Proc_Exit:
  Set qdf = Nothing
  Set dbs = Nothing
  DeleteEntry = fRetVal
  Exit Function

Proc_Err:
  fRetVal = False
  MsgBox Prompt:=Err.Description, Buttons:=vbExclamation
  Resume Proc_Exit

End Function

‘Here is the procedure I use on the Order form
Private Sub Delete_a_County_Click()
On Error GoTo Err_Delete_a_County_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmDeleteCounty"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Delete_a_County_Click:
    Exit Sub

Err_Delete_a_County_Click:
    MsgBox Err.Description
    Resume Exit_Delete_a_County_Click
    
End Sub