[AccessD] CBO Not In List

...hhhmmm ...I approach this differently JC ...I almost always use a
separate text box to set the cbo row source ...using the change event on the
text box to count the records found as each character beyond two or three
(choose your number) is typed into the text box ...if the ListCount is zero,
I can do a number of things depending upon the app ...this way I can limit
the records found to a reasonable size and still have be very responsive to
the user making changes ...watch the wrap :)

Me!txtSearchExpression = Me!txtEntry.Text & "*"
    lstFound.RowSource = "SELECT DISTINCTROW tblCompany.CompanyID,
tblCompany.CompanyName, tblCompany.City, tblCompany.StateOrProvince,
tblCompany.Country FROM tblCompany WHERE (((tblCompany.CompanyName) Like
[txtSearchExpression])) ORDER BY tblCompany.CompanyName;"
    lstFound.Requery
        Dim i As Integer
        i = lstFound.ListCount
    Me!txtCount = i

William Hindman
<http://www.freestateproject.org> - Do you want liberty in your lifetime?


----- Original Message ----- 
From: "John Colby" <jcolby at colbyconsulting.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Thursday, October 02, 2003 11:13 AM
Subject: RE: [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
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>