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_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
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_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
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