asked on

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?

aikimark

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.

jason bradway

ASKER

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.

aikimark

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.

Hamed Nasr

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

jason bradway

ASKER

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.

aikimark

Where are you putting that text?

It might be easier if you used the expression builder to plug the textbox control into the property.

jason bradway

ASKER

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?

jason bradway

ASKER

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.

Hamed Nasr

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

jason bradway

ASKER

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.

ASKER CERTIFIED SOLUTION

Hamed Nasr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial