[AccessD] CBO Not In List

Ok, my solution:

Where cboperson is a combo box, with AutoExpand and LimitToList set true, of
a list of people in the database.  tfrmPeople is a tabbed form of people in
the database, and frmPAIntake is a form for entering new people in the
database.

Basically the combo change event checks the selected length.  As you type in
the combo, if a match is being found there is selected text after the
cursor.  If you reach a point where the data entered no longer matches
anything in the list, there is no selected length so pop up a message box
asking if they want to open an IDE form.  If they find the person desired
they just hit enter or tab and AfterUpdate fires, causing the tab form to
open, with a where clause that finds the PE ID (goes to that person).

'
'If AfterUpdate fires, then the user selected existing data to edit.
'
Private Sub cboPerson_AfterUpdate()
    DoCmd.OpenForm "tfrmPeople", , , "PE_ID=" & cboPerson
End Sub
'
'The change event fires only when the data in the combo changes in response
to keyboard input
'
'Check for data selected.  If data is still selected then we are still
finding matching data
'
Private Sub cboPerson_Change()
    If cboPerson.SelLength = 0 Then
        cboPerson = ""
        DoCmd.Beep
        If MsgBox("Person not found.  Open intake form?", vbDefaultButton1 +
vbYesNo, "DATABASE SEARCH") = vbYes Then
            DoCmd.OpenForm "frmPAIntake"
        End If
    End If
End Sub
'
'Performing the checks above should minimize actually getting the NotInList
'However if we do, then "Ask and Open"
'
Private Sub cboPerson_NotInList(NewData As String, Response As Integer)
    Response = acDataErrContinue
    cboPerson = ""
    If MsgBox("Person not found.  Open intake form?", vbDefaultButton1 +
vbYesNo, "DATABASE SEARCH") = vbYes Then
        DoCmd.OpenForm "frmPAIntake"
    End If
End Sub

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Thursday, October 02, 2003 9:30 AM
To: AccessD
Subject: [AccessD] CBO Not In List


I want to use the NotInList to open an initial data entry form if the person
searched for isn't in the database, else open a tabbed form if they are.  I
have this working using NotInList to open the IDE form and the after update
to open the tabbed form to the record of the person found in the combo.

However, what I want to do now is to have the combo start beeping as the
person types if the item is not in the list.  IOW, NotInList only fires as
the user EXITS the combo and the item is not in the list.  However if the
user is typing in a person's name - JOHN COLBY, and by the time she types
the O in COLBY the combo is not finding the name, the combo should start
beeping so that the user is notified to stop typing and tab out to trigger
NotInList and open the data entry form - or even programatically tab out and
trigger the NotInList.

It seems like it could be accomplished by checking the insertion point and
the length of the data in the combo.  If the data exists, the insertion
point is in the middle of the data that the combo is following, whereas as
soon as the data is not found, the insertion point is at the end of the
data.  IOW, if the insertion point is at the end of the data TWICE, then we
are at a true "not found" and trigger NotInList.  In fact it seems like the
combo should have been built this way to begin with.

Has anyone ever done this?

John W. Colby
www.colbyconsulting.com

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com