Adding Data to Combo Box

[Kobe1]

Hi

I have been trying for the past week to set up combo boxes in one of my forms to allow data to be added to it and am having problems.

The form is a timesheet input form which records how many hours an employee has worked in a certain week. I have 3 combo boxes for: EmployeeID;ContractID; and ClientID

The form is based on a query and I have been trying to code procedures so that if any of the above fields are not on the combo list then the user can double click and add the item to the appropriate form e.g.employee form

However, I have been having problems with my code and can't seem to get it right. I thought I had solved the problem when one of my forms opened but then I received an error message saying "object doesn't support this property or method"

Can anyone help me with the code as I am just starting to learn VB.

Thanks in advance
Kobe1

 

[saltecho]

Hi this looks a bit complicated but really its easy.
You add one bit of the code to your combo box On Not in List event and put the other in a Module (don't forget Option Explicit at head of module). This will tell the user the item is not in the list and allow them to add it.

Text in the controls NotInList Event

Private Sub Title_NotInList(NewData As String, response As Integer)
On Error GoTo ErrorHandler
Dim ctl As Control
Set ctl = Screen.ActiveControl
response = MsgBox(NewData & " is not in list, do you want to add it Y/N?", vbQuestion + vbYesNo, "New Data&quot
If response = vbYes Then
response = acDataErrAdded
UpdateTitle NewData
Else
response = acDataErrContinue
ctl.Undo
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
Msgbox Err.Description
Resume ErrorHandlerExit

End Sub


text in a module change table and field name to your values

Public Sub UpdateTitle(strNewItem As String)
On Error GoTo ErrorHandler

Dim db As Database
Dim rs As DAO.Recordset
Dim strTable As String

strTable = "tblTitles"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strTable, dbOpenDynaset)
With rs
.AddNew
!Title = strNewItem
.Update
.Close
End With

ErrorHandlerExit:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Msgbox Err.Description
Resume ErrorHandlerExit

End Sub