?
Solved

Combo Box Limited Records

Posted on 2012-08-30
11
Medium Priority
?
709 Views
Last Modified: 2012-09-05
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?
0
Comment
Question by:JasBrad
  • 5
  • 3
  • 3
11 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 38351898
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
 

Author Comment

by:JasBrad
ID: 38351970
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
 
LVL 46

Expert Comment

by:aikimark
ID: 38352296
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:hnasr
ID: 38352471
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
 

Author Comment

by:JasBrad
ID: 38355498
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
 
LVL 46

Expert Comment

by:aikimark
ID: 38355808
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
 

Author Comment

by:JasBrad
ID: 38355960
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
 

Author Comment

by:JasBrad
ID: 38355987
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38356085
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
 

Author Comment

by:JasBrad
ID: 38365915
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
 
LVL 31

Accepted Solution

by:
hnasr earned 2000 total points
ID: 38366079
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question