Listbox doesn't display all records

[mvkrein]

I am using a SQL statement as the rowsource of a listbox. the expected result should be a list of 452 items which I verified by pasting the SQL statement into the Access query design and running the query directly. For some reason, in most cases, the listbox will only display 21 records. Other times it may display more than that (with the same SQL statement). What's really unusual is when I click on the form's "Find" button after the first set of 21 lines appears: it will then append more records to the list. Sometimes by doing this I can display all 452 records. The results seem to be somewhat random. I've tried adding delay loops, DoEvent statements, multiple requeries, repaint, etc. -- nothing I've tried seems to help. Any ideas?

 

[mresann]

Can you post the db with just the form, query, and table(s) that the query is attached to?

 

[mvkrein]

I'll have to wait until Monday when I'm back in the office. I need to alter the data in the table before I post as well. Thanks.

 

[mvkrein]

In the process of cleaning up my data so I could post it here, it became evident that I have a problem with my SQL statement. What I don't understand yet is why I can copy and paste the statement into Access query design, run it manually, and it works fine.

What I am attempting to do is vary the SQL statement, based on user input on a form, to query one, two or three tables of information. The reason I want to do this is because a vendor may have information in one table (name, address, contact information), but not necessarily in another table (license information). If the third table has no entry for that vendor, the query comes up blank, like expected. So, the user needs to be able to exclude the table.