Combo Box Limited Records

I have an Access 2007 ADP application and many of the forms have a combo boxes on them to look up the records from our PAT Table to populate subforms.  Recently we have exceeded 10k active records within the database.

The problem is that only the first 10k records of the SQL statement will show to populate the combo box.  It is generally there so that the user can type in the Patients ID number or type in the Patients Last Name (depending on which box they choose) and it will isolate the record.  However now if that record is beyond the 10k limit in the SQL statement it will not find it.

I have tried changing my statement to
SELECT DISTINCT TOP 100 PERCENT
FAMILY, LASTN, FIRSTN FROM Pat ORDER BY LASTN, FIRSTN

and also

SELECT DISTINCT TOP 15000
FAMILY, LASTN, FIRSTN FROM Pat ORDER BY LASTN, FIRSTN

without any luck.  Am I stuck with the limitations of the combo box or is there another way around this?
JasBradAsked:
Who is Participating?
 
hnasrConnect With a Mentor Commented:
Here is a version, where you have two combos"cboBox sorted numeric" and "cboBox2 sorted alpha", and they are synchronized. You can search in one and resume in the other.

Have Fun!
cboLimitedRecords-2.accdb
0
 
aikimarkCommented:
Is the PatientID a number?

I haven't encountered this error, but I'm inclined to think that you will need to add some condition based on what the user has typed.

Example:
SELECT FAMILY, LASTN, FIRSTN 
FROM Pat 
WHERE LAST Like "'" & Forms!myformname.controls!cboLookup & "*'"
ORDER BY LASTN, FIRSTN 

Open in new window


You would requery the combobox as the user types, or wait until the user has typed 2-3 letters.
0
 
JasBradAuthor Commented:
Yes the patients ID is a number and is the field of Family.

The example I gave is a basic one where only the patient ID (Family) is available for the user to enter in.   The SQL Statement is entered in the property sheet under Row Source.  I did not use any code for the box.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
aikimarkCommented:
I think you might have to get a bit more creative and not set the row source of the combobox until one or more characters have been typed.  If the user typed digits, then your WHERE clause would compare the combobox value with the Family column.  Otherwise, the row source would look look like the SQL in my prior comment.
0
 
hnasrCommented:
I go with aikimark in filtering the data to include what you type of characters. I made use of his comment to type, in the same place, for both columns.

For a table a(aid, adesc) - aid numeric, adesc string

Private Sub cboBox_Change()
    cboBox.SelStart = Len(cboBox.Text)
   If Val(cboBox.Text) = 0 Then
        cboBox.RowSource = "SELECT aid, adesc from a where adesc like '*" & _
        Nz(cboBox.Text, 0) & "*' order by adesc"
    Else
        cboBox.RowSource = "SELECT aid, adesc from a where aid like '*" & _
        Nz(cboBox.Text, 0) & "*' order by aid"
    End If
End Sub

Open in new window

0
 
JasBradAuthor Commented:
I am trying to follow the example you gave, Aikimark but I have had to change the ! to . because the query errored on it.  It indicated that "Incorrect Syntax near '!'." But then it also errored on the .

It indicated that there was an "Invalid Column Name at " ' " ."

So then I tried the following:

SELECT FAMILY, LASTN, FIRSTN
FROM Pat
WHERE FAMILY = " & Forms!SelfPaysDataEntry!Family & "
ORDER BY LASTN, FIRSTN  

and got back "Invalid column name ' & Forms!SelfPaysDataEntry!Family & '".  I have tried a few different combinations and not been able to get it.
0
 
aikimarkCommented:
Where are you putting that text?

It might be easier if you used the expression builder to plug the textbox control into the property.
0
 
JasBradAuthor Commented:
I just opened my old version of the ADP which is still saved in an older version of Access, in fact the ADP indicates that it is Access 2000 compatible, and I can make it work.  However this scenario above is running under an "upgraded model" that I created in Access 2010 that indicates it is Access 2002-2003 compatible.  Does this make the difference that there is a Microsoft limitation?
0
 
JasBradAuthor Commented:
When you are working with an ADP you are attatched to a SQL database you do not have the option for that within the expression builder.  You can only choose from Tables, Views and Functions.
0
 
hnasrCommented:
Try this sample database, List box shows contents of combo.
Type in combo and watch changes in list box. These values appear in combo box.
cboLimitedRecords.accdb
0
 
JasBradAuthor Commented:
The query that you are using, hnasr, seems to work fine as long as you sort the records by the numeric ID.  As soon as I change the sort order to anything else it doesnt want to requery.  Even though the users are limited to entering the patients ID number (Family), I still want the ability for them to be able to scroll if necessary through the list in alpha.

It is looking like I am going have to redesign the form in order to give it more user friendliness.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.