Delete from combo

[owilson]

Hi,

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.

TIA

Owen

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 +
vbDefaultButton1
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
cbo.Undo
Exit Sub
ElseIf intResult = vbYes Then
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTable)
rst.AddNew
rst(strFieldname) = strNewData
rst.Update
rst.Close

intResponse = acDataErrAdded

End If

ErrorHandlerExit:
Exit Sub

ErrorHandler:
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;