Help with NotInList Event for Continuous Form

[FiniteCircle]

Good morning, afternoon, or evening.

I've been wracking my brains over this and finally decided to get some help (AA = Accessor's Anonymous?). Alright, let's get to this very complex db, at least that's what I tell myself. To begin, I am not a VB expert and have only been at it since March 1st so I apologize for my ignorance.


Table structure:

I have various tables that are related to [tblAccount_List]. I realized that some records need to be associated to 2+ accounts so I created a join table simply called [tblAccounts] composed of several foreign keys related to the various tables that need to connect to [tblAccount_List]. To simplify this and reduce confusion, I am only working with three tables: [tblAccount_List], [tblAccounts], and [tblVisitor_Agreements].


Forms:

"frmVisitor_Agreements" has subform "frmAccounts_Subform" which is a continuous form and only has "cboAccounts" in it.

The Record Source for "frmAccounts_Subform" is "qryAccounts" which queries the two primary keys.

The Record Source for "cboAccounts" is AccountID (from qryAccounts) and the Row Source is [tblAccount_List].

NOTE: When I began, the Primary key for [tblAccount_List] was "Account" and nothing else. I found, and I don't know why, that I had better results with the code below when I made "AccountID" the primary field set to autonumber and another column with the Account # called "Account".

The Problem

Table structure seems to be correct considering that I can select Accounts already in the list. The problem is when I try to code the ability to enter a new account using the NotInList event of the cboAccounts.

The code below allows me to enter new Accounts after prompting but that's about it. It does not allow me to select existing records and after I confirm an addition it throws error "The Text you entered isn't an item in the list." When I tab to the next record in the subform it states that the "value isn't valid for this field." Saying no throws an error #91 (Object variable or with block variable not set). After both I get an error stating "The text you entered isn't an item in the list."

The Code

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

Dim dbs As DAO.Database
Dim rstAccount As DAO.Recordset
Dim intAnswer As Integer

On Error GoTo ErrorHandler

intAnswer = MsgBox("Add " & NewData & " to the list of Accounts?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then

' Add Account stored in NewData argument to the Account_List table.
Set dbs = CurrentDb
Set rstAccount = dbs.OpenRecordset("Account_List")
rstAccount.AddNew
rstAccount!Account = NewData
rstAccount.Update

Response = acDataErrAdded ' Requery the combo box list.


Else
Response = acDataErrDisplay ' Require the user to select
' an existing Account.
End If

rstAccount.Close
dbs.Close

Set rstAccount = Nothing
Set dbs = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description


End Sub

Any help is appreciated.