Combo box search/navigation and add new

I've spent far too much time screwing around with this-but I finally found
success. As I have gotten countless answers out of this discussion board for
numerous things (almost always from searching the existing postings) - I
figured I would post my method should someone else be looking to accomplish
this same task far more quickly than I did (which probably wouldn't be
terribly difficult-I’m not going to share how long it has taken me).

If anyone happens to read this and realize that the database will
self-destruct or otherwise suffer some kind of implosion due to something in
my code-please reply (I have on occasion done some things which made Access
very unhappy- lately, we’ve been working things out though and I think
there’s hope for us).


I was looking to have a combo box by which to navigate on a form as well as
function as a "data entry" field if the user wanted to add a record not
already in the combo box list. I have no troubles setting up a combo box for
form navigating, and I have no troubles adding an item to the list, but my
attempts to combine the two were less than successful. I found other
postings for people who also wanted a multi-purpose combo-box, but they all
seemed to encounter the same issues I did-but either abandoned the pursuit or
found alternate methods. I was about to post with my own details and hope
for assistance when I thought of one last thing-which turned out to be the
winning idea.

I attempted countless iterations of code and combinations of various
postings and methods in my pursuit of what seems like such a simple thing.
As I combined my usual approaches to get the dual-purpose combo box, I
encountered phantom navigation issues, items not being added to the list, and
my personal favorite was the NotInList loop (encountered when I attempted
someone’s suggestion to use me.requery in the notinlist procedure before the
dataerradded-not such a good idea).

The form on which this combo box resides is based on tblClass (auto-number
PK ClassID, CName, skill, type, dates, etc.). The form is a single form view
as there are multiple subforms attached for various junction tables (for
students, trainers, locations, etc). My super-special combo box is unbound –
and uses the following as a rowsource:
SELECT tblClass.ClassID, tblClass.CName FROM tblClass ORDER BY
tblClass.CName;

All of the following code still needs error handling (it probably wouldn’t
kill me to use some real names for the combo box either) but even I can
handle that on my own.

For the NotInList procedure, I used this (which may or may not be the exact
same thing I started with-except for using an append query rather than adding
to a rs):

Private Sub Combo22_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim strTmp As String

strMsg = "Do you want add " & NewData & " as a Class?"
strMsg = strMsg & vbCrLf & "Click 'Yes' to add or 'No' to select an existing
class"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new class?") = vbYes Then
On Error Resume Next
strTmp = "INSERT INTO tblClass(CName)VALUES(""" & NewData & """)"
DBEngine(0)(0).Execute strTmp, dbFailOnError
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
Me.Combo22.Value = NewData
End If
Else
Response = acDataErrContinue
End If

End Sub



For the after update event of my combo box I used this:

Private Sub Combo22_AfterUpdate()
Dim rs As DAO.Recordset
Dim cmbval As Variant

If Not IsNull(Me.Combo22) Then
cmbval = Me.Combo22
If Me.Dirty Then
Me.Dirty = False
End If
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "[ClassID] = " & cmbval
If rs.NoMatch Then
MsgBox "Class Name Not Found"
Else
Me.Bookmark = rs.Bookmark
MsgBox "Class Name Added"
End If
Set rs = Nothing
End If
End Sub


The two little tiny lines that made the whole difference were setting the
combo box to a variable and the me.requery before the setting the
recordsetclone (in the afterupdate event). Without the combo box as a
variable-the “new� value was never found (even when changing the findfirst
line to find the exact ClassID (numeric value to match the autonumber) that
was just added in the NotInList procedure). And while without the
me.requery-the parent form navigated to the correct record-the subforms did
not (I also tried various iterations of requerying the recordsetclone, and
then the recordset but that did not work either). We won’t mention the ten
thousand things I tried that really didn’t accomplish anything.

Hope this helps someone along the way-I’ve certainly gotten more than a
little help from this discussion board over the years.