only part working as macro - think need code!

[Kirstie Adam]

Hi All,

I have a combo box [Contact], which when the user types in a contact name
which is not
in the list, i want a msgbox saying "Contact not in database, please enter
now" then a pop up form [frmContacts] to open up for the user to type the
details in. Then the pop up box can be closed and the contact just added
appear in the combo box to be selected.
I have the first half of this working in a macro, but keep getting the
access error message about not in
list as well as mine - how do i suppress this?
I tried using SetWarnings in a macro, before the msgbox action and also
before the openform action but neither of these worked.
I think it would be better in code and wonder if someone can tell me how?

Thanks,

Kirstie

 

[sam]

Try this in the not in list event of your combo. Replace YourComboName and
YourTableName with the appropriate names.


Private Sub YourComboName_NotInList(NewData As String, Response As Integer)

Dim rs As DAO.Recordset
Dim strSql As String
Dim strNoTtlMessage As String
Dim strNoTtlTitle As String
Dim lResponse As Long
'Set up message box to confirm
strNoTtlMessage = "'" & NewData & "' was not found " & Chr(10) & "Do you
want to add this Contact?"
strNoTtlTitle = "Contact not Found"
lResponse = MsgBox(strNoTtlMessage, vbYesNo + vbInformation,
strNoTtlTitle)
'If response is Yes, Add new item to list
If lResponse = vbYes Then
Set rs = CurrentDb.OpenRecordset("YourTableName", dbOpenDynaset)
rs.AddNew
rs!TtlDesc = NewData
rs.Update
Response = acDataErrAdded
' Clean up
rs.Close
Set rs = Nothing
'If response is no, show list for another selection
Else
Response = acDataErrContinue
End If

End Sub