Adding A Name in the Combo Box

[jdwm2310]

HI,

I have a combobox binded to a table. I want the users to add new names that doesn't appear in the combobox. So if the name is new an ddoesn't appear in the list, the user can add the name to the table and update the combo box so it displays the new name in the list.

Private Sub Caller_Name_NotInList(NewData As String, Response As Integer)
Dim intNewName As Integer, strTitle As String
Dim intMsgDialog As Integer, strMsg As String
Const conClrWhite = 16777215
Const conNormal = 1

strTitle = "Customer Not in List"
strMsg = "Do you want to add name?"
intMsgDialog = "vbYesNo + vbExclamation"
intNewName = MsgBox(strMsg, intMsgDialog, strTitle)

If intNewName = vbYes Then
Caller_Name.Undo
Contact_Name_Query.Enabled = True
Contact_Name_Query = NewData
Phone.Enabled = True

MsgBox "Enter the phone number of the new caller."

Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If

Else
strMsg = "To modify this caller's name, edit the name in the box below the Caller Name Combo Box."
strMsg = strMsg & "To add a new customer, click Undo Record on the Records menu and then type the "
strgMsg = strMsg & "new name in the Caller Name combo box."
MsgBox strMsg
Caller_Name.Undo

Response = acDataErrContinue
End If
End Sub

 

[TrojanRabbit]

What's your question? I don't actually see a specific question here. I noticed that you have one If but two Else...End If. That can cause problems if it's not just a typo here.

 

[jdwm2310]

The question is How can I add a new name in a combo box list?

 

[Rolliee]

We need to know what populates the combo box to be of help. If you want to send me some code, I will see if I can help. I can add to the recordsource of a combo box.

rollie@bwsys.net

 

[TrojanRabbit]

You have the right idea using NotInList. You can use a recordset object to add it, then Requery to update. Something like:

Dim rst As Recordset
Set rst = Me.Recordset.Clone
rst.Addnew
... 'Set values for required fields
rst.Update
rst.MoveLast
Me.Bookmark = rst.Bookmark

One way of doing it. I know there are other ways, but this is what I can come up with.

 

[jdwm2310]

TrojanRabbit,
How do i get a recordset object?

 

[jdwm2310]

Rolliee,

The combobox is populated by query called Contact Name
This is the code I am using, which is giving me a compile error. Hope you could help...thanks
Private Sub Caller_Name_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
Set ctl = Me.Caller_Name
If MsgBox("Value is not in List, Add it??", vbOKCancel) = vbOK Then MsgBox "Yes"
Me.Caller_Name = NewData
Response = acDataErrContinue
ctl.Undo
End Sub

 

[jdwm2310]

Guys,

I am using the code posted below and it works great however When the Caller Name is selected the phone number should automatically be filled in the text box labeld "Phone Number". I could I do this?

Private Sub Caller_Name_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Caller_Name_NotInList
Response = acDataErrContinue
NewData = UCase(NewData)
If MsgBox(NewData & "is not an existing caller. Would you like to add it now?", vbYesNo + vbQuestion + vbDefaultButton1, "Caller not found....&quot = vbNo Then
Me.Caller_Name.Undo
Me.Caller_Name.Dropdown
Exit Sub
Else
DoCmd.OpenForm "ContactDetails", , , , acFormAdd
Forms![ContactDetails].[ContactName] = NewData
DoCmd.Close , , acSaveYes
Me.Caller_Name.Undo
Me.Caller_Name.Requery
Me.Caller_Name = NewData
End If

Exit_Caller_Name_NotInList:
Exit Sub

Err_Caller_Name_NotInList:
MsgBox Err.Description
Resume Exit_Caller_Name_NotInList
End Sub

 

[TrojanRabbit]

Use the AfterUpdate event of the combobox to update the phone number. If the phone number is coming from a table, the best way I can think of is a recordset. You need reference Microsoft DAO 3.6 Object Library (or latest DAO Object Lib) for recordsets.

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(&quot;<tablename>&quot;,dbOpenDynaset)
rst.FindFirst &quot;Caller_Name = '&quot; & Me.Caller_Name & &quot;'&quot;
If Not rst.NoMatch Then
Me.Phone_Number = rst![Phone_Number]
End If
rst.Close

If the form is bound to the table, then you can just update the bookmark.

Me.RecordsetClone.FindFirst &quot;Caller_Name = '&quot; & Me.Caller_Name & &quot;'&quot;
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

 

[jdwm2310]

TrojanRabbit,

Would you by any chance understand why my code doesn't allow the form to open?Private Sub Caller_Name_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Caller_Name_NotInList
Response = acDataErrContinue
NewData = UCase(NewData)
If MsgBox(NewData & &quot;is not an existing caller. Would you like to add it now?&quot;, vbYesNo + vbQuestion + vbDefaultButton1, &quot;Caller not found....&quot = vbNo Then
Me.Caller_Name.Undo
Me.Caller_Name.Dropdown
Exit Sub
Else
DoCmd.OpenForm &quot;ContactDetails&quot;, , , , acFormAdd
Forms![ContactDetails].[ContactName] = NewData
DoCmd.Close , , acSaveYes
Me.Caller_Name.Undo
Me.Caller_Name.Requery
Me.Caller_Name = NewData
End If

Exit_Caller_Name_NotInList:
Exit Sub

Err_Caller_Name_NotInList:
MsgBox Err.Description
Resume Exit_Caller_Name_NotInList
End Sub