How to display more than 999 records in Access 2007 form ?

[spendy]

Hello,

I am using Access 2007 and i have this table, Orders, which contains more than 1000 records. I can see all the records in the table, but in the attached form i can see only 999 records. I have changed the option in the Database Tools to show 99999 records, but still I won't see these records over 999 in the attached form.

I have to say that the Record Source for the form does only select all the records in the table, so I'm pretty sure that cannot be the cause that limits displaying records after 999.


On the other hand, I have another table, called Invoices. In this table, again, I have over 1000 records, but in the attached form I get to see the Invoice starting 1000 as a FIRST record. When I hit the "First Record" button in the navigation panel, I get to see the invoice starting 1000.


So, I would be very grateful if anyone could help me with this problem, as I don't see the bottom of it, no matter how much I tried. The primary keys in both tables are Autonumber, if that's helpful.


Thank you in advance,
Sandra

[twinnyfo]

Sandra,

That sounds incredibly bizarre. Forms should default to showing all records--regardless of how many records there are. Although my DB settings also list 1,000 records, my forms still display all my records (about 4,000).

Have you tried creating a new form and just throwing the fields on the form to see if a new form will cycle through all records?

[Stewart Ross]

Difficult to know sight unseen, but although you mention the record source property I suggest you check it again to confirm that the forms are indeed using the table or query which contains an unfiltered version of the dataset. Access's wizards can 'helpfully' put a SELECT statement in place of a direct reference to your record source, and this in turn can be altered by other developers, so you need to rule out the Recordsource property being set to a SQL statement which has, say, a SELECT TOP 1000 condition or a WHERE clause that could potentially be restricting your records.

Another, perhaps distant, potential cause for which you can check the properties is that you don't have a filter condition of some kind set. This would only be a problem if the form's Filter on Load property is set to True/Yes instead of False/No (the default).

Also worth checking is whether or not there is any event code running as a result of the form's On Load event, as it is possible for the recordsource to be changed dynamically under program control even though the Record Source property is set to something else.

-Stewart

[jimatqsi]

Also of interest would be any sorting specification on the form or in the recordset. You don't say what you see when you click the "Last" button. It would be interesting to know that.

Jim

[jimatqsi]

Something I do sometimes when I have recordsource issues or just want to confirm what's in the recordsource is this. I'll pick an object and change the double-click event for that object to contain

Msgbox me.recordsource

or

me.sometextbox=me.recordsource

That gives me the flexibility to look at the recordsource at any time without jumping into debug mode. I can copy the resulting text into the query editor to execute it and fiddle with it.

Then don't forget to remove the temporary double-click event code.

Jim

[twinnyfo]

Jim,

You are the king of trouble-shooting and debugging! Always full of great, uesful ideas!

[jimatqsi]

Thank you, twinnyfo. I've earned a bunch of these gray hairs the hard way.