Add new record via form where data entered is NotInList

[JHite]

I am using Access 2003. My Form has 4 fields:
MailingListID, auto generated – primary key
LastName
FirstName
NickName

I used the Combo box wizard to set up look-up box on the form. I answered the wizards question by saying “find a record in the form”, I choose the first 3 fields for the combo box and made field 1 width = 0. The wizard then created the unbound control box and generated the code shown below

Private Sub Combo14_AfterUp date()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Cl one
rs.FindFirst "[MailingListID] = " & Str(Nz(Me![Combo14], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This seems to work fine. I can pull down on the list and click on the name I want and info for that record shows up in form or if I enter a last name not on the pull down list, I get the usual error message for not in list.

I thought it would be simple to modify Combo14 to simply add a new record if it didn’t find the LastName I typed in. So I modified Combo14 as you see below. But I still get the not in list error message when I try it - instead of the form for the new record – which should have the next autonumber in the MailingListID field and “Doe” in the LastName field. [Actually in real life I would capture the LastName the user typed and put that in instead of Doe but I’m just trying to get this to work.]

Private Sub Combo14_AfterUp date()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Cl one
rs.FindFirst "[MailingListID] = " & Str(Nz(Me![Combo14], 0))
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
rs.AddNew
rs!LastName = "Doe"
rs.Update
Me.Bookmark = rs.Bookmark
End If

End Sub

What should the procedure look like to do what I want?

PostScript: I also tried leaving Combo_14 as the wizard created and putting a macro GoToRecord (new) in the NotInList event field. But this caused Access to abruptly shut down.

Thanks for any help. J Hite

[MMcCarthy]

You cannot add value by typing into combo box. Type last name in an unbound text box. Then check this out ...

 
 ' change to after update event on new textbox 
Private Sub textboxname_AfterUpdate()
' Find the record that matches the control.
Dim rs As Recordset ' declare as recordset rather than object
 
 ' this assumes that form is bound to relevant table or query and fields are bound on the form 
Set rs = Me.RecordsetClone ' no dot between recordset and clone
rs.FindFirst "[LastName]='" & Me.[textboxname] & "'" ' lookup lastname in records on form
 
If rs.NoMatch Then ' if name not found then
rs.AddNew ' add new record
rs!LastName = Me.[textboxname] ' set LastName to value typed in
rs.Update
End If
 
 ' always reset recordset
rs.Close
Set rs = Nothing
 
Me.Combo14.Requery ' requery the combo box to show new record
 
End Sub