Delete from combo




I have a combo box that gets its data from one table and
stores it in another. The lookup Tabe is
"Sub-Contractors List" and the ControlSource is in a
table called "ChangeOrders"

I am using the following code to allow the user to add to
the list, but I cannot find a way to allow them to delete
items that are no longer used.

Any suggestions would be greatly appreciated.



Private Sub cboCompany_NotInList(strNewData As String,
intResponse As Integer)

On Error GoTo ErrorHandler

Dim intResult As Integer
Dim strTitle As String
Dim intMsgDialog As Integer
Dim strMsg1 As String
Dim strMsg2 As String
Dim strmsg As String
Dim cbo As Access.ComboBox
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strTable As String
Dim strEntry As String
Dim strFieldname As String

strTable = "Sub-Contractors List"
strEntry = "Company Name"
strFieldname = "COMPANY"

Set cbo = Me![cboCompany]

strTitle = strEntry & " not in list"
intMsgDialog = vbYesNo + vbExclamation +
strMsg1 = "Do you want to add "
strMsg2 = "as a new " & strEntry & " entry?"
strmsg = strMsg1 + strNewData + strMsg2
intResult = MsgBox(strmsg, intMsgDialog, strTitle)

If intResult = vbNo Then
intResponse = acDataErrContinue
Exit Sub
ElseIf intResult = vbYes Then
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTable)
rst(strFieldname) = strNewData

intResponse = acDataErrAdded

End If

Exit Sub

MsgBox "Error No: " & Err.Number & "; Description: "
& Err.Description
Resume ErrorHandlerExit

End Sub



Allen Browne

Use the DblClick event of the combo to open a form where the user can delete
the item. Use the AfterDelConfirm event of that form to Requery the combo on
the original form so as to make it disappear immediately.

If there are historical records that still use the old values, add a yes/no
field named (say) "Inactive" to your lookup table. Instead of deleting,
Execute an Update query statement to set the record's Inactive field to Yes,
and then requery the combo. This assumes the RowSource of the combo is:
SELECT * FROM ChangeOrders WHERE Inactive = False;

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question