Combo box for both input and bound list

[kmerc]

I'm not knew to access but I'm very green at vba. I've created a database with two: 1 for putting in the initial incident and the other for the follow up comments. In the field for the person getting assigned to the comments on the first form it could be anyone in our company so I don't want to use a table of all our employees. Since it depends on the issue as to who's assigned to help. Is there a way for me to have a combo box that can be an input for people's names AND pull up ones that have already been used: i.e. Jessica Cook and Jason Goff both have had something assigned to them, their names will show up in a drop down menu OR I can enter Rebecca Miller and it will store her name with Jason and Jessica to use later. I'd prefer to do this in vba and not a subform but I'll put just this in a separate table from my main one if need be. I will obviously need to pull the names in queries and reports. Thanks in advance!

 

[GinaWhipp]

Hmm, well you should *store* the names somewhere, so, where do you want to store them... in the table with the Comments?

 

[kmerc]

I don't want them in that table because I need it to be a sub table as one entry could have multiple comments. I have created a separate table to store only the names in but I'm not sure how to get them to both be entered into this table AND pull up this table from the same bound combo box.

 

[GinaWhipp]

So, the Table has a First Name and a Last Field? If this is true, you'll need another Form (in the below example, mine is FromAssociateProfile) but you can use the Not_In_List event with something like...

Private Sub cboAssociateID_NotInList(newdata As String, Response As Integer)
On Error GoTo Err_cboAssociateID_NotInList
        Dim intAnswer As Integer
        intAnswer = MsgBox("Would you like to add this Associate to the list?", vbYesNo + vbQuestion, "Not in List")
        If intAnswer = vbYes Then
            DoCmd.RunCommand acCmdUndo
            DoCmd.OpenForm "frmAssociateProfile", , , , acFormAdd, acDialog
            Response = acDataErrAdded
        Else
            DoCmd.RunCommand acCmdUndo
            Response = acDataErrContinue
        End If
Exit_cboAssociateID_NotInList:
        Exit Sub
Err_cboAssociateID_NotInList:
        MsgBox Err.Description
        Resume Exit_cboAssociateID_NotInList
End Sub

 

[kmerc]

Thank you very much. Where do I put this? In the events tab in the property sheet? or vba? Thanks

 

[GinaWhipp]

In the Not_In_List event (Event Procedure) of the Combo Box used to look up People. Note, copy/paste everything between the first line and the last line.

 

[kmerc]

I don't see a field for "Procedure". I only see On Click, Before Update, After Update, On Dirty, On change, on got focus, on lost focus, on dbl click, on mouse down, on mouse up, on mouse move, on key down, on key up, on key press, on enter, on exit, and on undo. I'm clicking on the right bound field but I'm not seeing it. Thanks

 

[GinaWhipp]

This is a Combo Box right? You need to look in the Properties Window, it's there...

 

[kmerc]

I looked under all the tabs (format, data, event, other) and "Event Procedure" is not listed anywhere. I am using Access 2010 and I'm positive it's a combo box.

 

[GinaWhipp]

You need to click on the Not_In-List event and from the Drop Down select Event Procedure.