is there a limit to how many records can be viewd in a subform?

[hard2findparts]

I have a rather large db that I have created myself. For the most part it
works fine, but I do have a few annoying things I would like to fix if
possible.

I have 71425 records in my INV table. I use a query to sort that
information and a subform that displays it when needed. For some reason
approximately 65535 records can be viewed in the subform.

Is there a limit on records that can be viewed in a subform. Did I do
something wrong when I created the db?

Can someone please help?
Thanks in advance
david

 

[Ken Snell \(MVP\)]

I'm not aware of any specific limitation for how many records can be
displayed in a subform. The number that you cite as a "limitation" is the
maximum number of rows that can be in EXCEL 97/2000/2002/2003 spreadsheets,
but I assume you're not linking to a spreadsheet for the data.

Tell us more details about what you're doing and how you've set up the
form/subform.

 

[hard2findparts]

Hi Ken

The data is stored in access. When I created the db, I did import it from
an excel database.

Here is the sql for the query that the info comes from:

SELECT INV.QTY, INV.CONDITION, INV.MFG, INV.[P/N], INV.DESCRIPTION,
INV.LOCATION, INV.COST, INV.LIST, INV.WEIGHT, INV.NOTES, INV.FITS, INV.SOS,
INV.[DATE IN]
FROM INV
ORDER BY INV.MFG, INV.[P/N];

I am not sure what info i can give you on the form and subform. My database
is used to store my parts inventory information. When I type in the part
number, I can click on a combo box and show 10 lines of information. This
helps if I can remember the first few digits of a part number. I type the
first digits in, click on the combo box, and choose the entry that I am
looking for.

I just started keeping a new manufacture of parts in my inventory. All of
these part numbers start with letters, therfore they end up at the bottom of
the query and I cannot see them on the subform.

Thanks in advance
david

 

[Ken Snell \(MVP\)]

Obvious question: are you sure that the new parts are in the table? I
assume that you've visually looked at the datasheet view of the table and
confirmed that those records are there?

What is the Format property of the textboxes that are bound to the subform's
query's fields?

The SQL statement that you posted is the query that is the subform's
RecordSource property?

What are the LinkMasterFields and LinkChildFields properties of the subform
control?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken

The data is stored in access. When I created the db, I did import it from
an excel database.

Here is the sql for the query that the info comes from:

SELECT INV.QTY, INV.CONDITION, INV.MFG, INV.[P/N], INV.DESCRIPTION,
INV.LOCATION, INV.COST, INV.LIST, INV.WEIGHT, INV.NOTES, INV.FITS,
INV.SOS,
INV.[DATE IN]
FROM INV
ORDER BY INV.MFG, INV.[P/N];

I am not sure what info i can give you on the form and subform. My
database
is used to store my parts inventory information. When I type in the part
number, I can click on a combo box and show 10 lines of information. This
helps if I can remember the first few digits of a part number. I type the
first digits in, click on the combo box, and choose the entry that I am
looking for.

I just started keeping a new manufacture of parts in my inventory. All of
these part numbers start with letters, therfore they end up at the bottom
of
the query and I cannot see them on the subform.

Thanks in advance
david

I'm not aware of any specific limitation for how many records can be
displayed in a subform. The number that you cite as a "limitation" is the
maximum number of rows that can be in EXCEL 97/2000/2002/2003
spreadsheets,
but I assume you're not linking to a spreadsheet for the data.

Tell us more details about what you're doing and how you've set up the
form/subform.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message

 

[hard2findparts]

Hi Ken

I am sorry, I misspoke, this is not a subform. I type my part numbers into
a Combo Box, not a subform.

Yes I have looked the contents of the table. It now shows 71587 lines of
data. If I type the part number into the combo box and press "enter", then
the data shows up on the form just like it is supposed to. I just cannot
preview all of the data in the combo box.

I looked at the last line that I could view in the combo box, then went into
the query and that line was line number 65535. I can view all of the records
in the query, but not in the combo box.

Thanks
david

Obvious question: are you sure that the new parts are in the table? I
assume that you've visually looked at the datasheet view of the table and
confirmed that those records are there?

What is the Format property of the textboxes that are bound to the subform's
query's fields?

The SQL statement that you posted is the query that is the subform's
RecordSource property?

What are the LinkMasterFields and LinkChildFields properties of the subform
control?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken

The data is stored in access. When I created the db, I did import it from
an excel database.

Here is the sql for the query that the info comes from:

SELECT INV.QTY, INV.CONDITION, INV.MFG, INV.[P/N], INV.DESCRIPTION,
INV.LOCATION, INV.COST, INV.LIST, INV.WEIGHT, INV.NOTES, INV.FITS,
INV.SOS,
INV.[DATE IN]
FROM INV
ORDER BY INV.MFG, INV.[P/N];

I am not sure what info i can give you on the form and subform. My
database
is used to store my parts inventory information. When I type in the part
number, I can click on a combo box and show 10 lines of information. This
helps if I can remember the first few digits of a part number. I type the
first digits in, click on the combo box, and choose the entry that I am
looking for.

I just started keeping a new manufacture of parts in my inventory. All of
these part numbers start with letters, therfore they end up at the bottom
of
the query and I cannot see them on the subform.

Thanks in advance
david

I'm not aware of any specific limitation for how many records can be
displayed in a subform. The number that you cite as a "limitation" is the
maximum number of rows that can be in EXCEL 97/2000/2002/2003
spreadsheets,
but I assume you're not linking to a spreadsheet for the data.

Tell us more details about what you're doing and how you've set up the
form/subform.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
I have a rather large db that I have created myself. For the most part
it
works fine, but I do have a few annoying things I would like to fix if
possible.

I have 71425 records in my INV table. I use a query to sort that
information and a subform that displays it when needed. For some
reason
approximately 65535 records can be viewed in the subform.

Is there a limit on records that can be viewed in a subform. Did I do
something wrong when I created the db?

Can someone please help?
Thanks in advance
david

 

[Douglas J. Steele]

Yes, combo boxes have a limit, and, as you've found, that limit is 65,535.
It's the maximum number of values that can be addressed in a 2 byte Integer
variable.

There's no way to change the bevahiour in Access.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Ken

I am sorry, I misspoke, this is not a subform. I type my part numbers
into
a Combo Box, not a subform.

Yes I have looked the contents of the table. It now shows 71587 lines of
data. If I type the part number into the combo box and press "enter",
then
the data shows up on the form just like it is supposed to. I just cannot
preview all of the data in the combo box.

I looked at the last line that I could view in the combo box, then went
into
the query and that line was line number 65535. I can view all of the
records
in the query, but not in the combo box.

Thanks
david

Obvious question: are you sure that the new parts are in the table? I
assume that you've visually looked at the datasheet view of the table and
confirmed that those records are there?

What is the Format property of the textboxes that are bound to the
subform's
query's fields?

The SQL statement that you posted is the query that is the subform's
RecordSource property?

What are the LinkMasterFields and LinkChildFields properties of the
subform
control?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message

Hi Ken

The data is stored in access. When I created the db, I did import it
from
an excel database.

Here is the sql for the query that the info comes from:

SELECT INV.QTY, INV.CONDITION, INV.MFG, INV.[P/N], INV.DESCRIPTION,
INV.LOCATION, INV.COST, INV.LIST, INV.WEIGHT, INV.NOTES, INV.FITS,
INV.SOS,
INV.[DATE IN]
FROM INV
ORDER BY INV.MFG, INV.[P/N];

I am not sure what info i can give you on the form and subform. My
database
is used to store my parts inventory information. When I type in the
part
number, I can click on a combo box and show 10 lines of information.
This
helps if I can remember the first few digits of a part number. I type
the
first digits in, click on the combo box, and choose the entry that I am
looking for.

I just started keeping a new manufacture of parts in my inventory. All
of
these part numbers start with letters, therfore they end up at the
bottom
of
the query and I cannot see them on the subform.

Thanks in advance
david


:

I'm not aware of any specific limitation for how many records can be
displayed in a subform. The number that you cite as a "limitation" is
the
maximum number of rows that can be in EXCEL 97/2000/2002/2003
spreadsheets,
but I assume you're not linking to a spreadsheet for the data.

Tell us more details about what you're doing and how you've set up the
form/subform.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
I have a rather large db that I have created myself. For the most
part
it
works fine, but I do have a few annoying things I would like to fix
if
possible.

I have 71425 records in my INV table. I use a query to sort that
information and a subform that displays it when needed. For some
reason
approximately 65535 records can be viewed in the subform.

Is there a limit on records that can be viewed in a subform. Did I
do
something wrong when I created the db?

Can someone please help?
Thanks in advance
david

 

[John W. Vinson]

I looked at the last line that I could view in the combo box, then went into
the query and that line was line number 65535. I can view all of the records
in the query, but not in the combo box.

As Douglas says, you can't display over 64K records in a combo. However, you
can do a "trick" to display hundreds of thousands of records, by changing the
rowsource of the combo after typing the first few characters. See

http://allenbrowne.com/ser-32.html

for an example.

 

[hard2findparts]

Hi John

I looked at your example. It sounds like it should work, although I am
still reletively new to Access, and I am clueless on some terms for example
where do I find: "the General Declarations section of your form?s module"

Thanks
david

 

[David W. Fenton]

Yes, combo boxes have a limit, and, as you've found, that limit is
65,535. It's the maximum number of values that can be addressed in
a 2 byte Integer variable.

There's no way to change the bevahiour in Access.

Nor should you ever even *encounter* that limit, as a combobox with
that number of records is not only a performance hog, but is
profoundly user-hostile.

The usual solution is to filter on the fly, having no Rowsource
until after 2 or 3 characters are typed. You use the OnChange event,
and check the length of the .Text property of the combo box. When
it's the length you want to use to filter, you then construct the
Rowsource with a LIKE operator and assign it.

The downside of this is that if you have no Rowsource, values that
have already been entered won't be displayed. This can be finessed
by assigning a rowsource in the OnCurrent event that includes the
data that's already entered. Then you'd when performing the
operation outlined in the previous paragraph, you'd UNION the SQL
for the existing value with the new SQL (so that if the user cancels
the edit before choosing something from the new rowsource, the old
value will still be displayed).

 

[Ken Snell \(MVP\)]

As Douglas notes, this is a limit of comboboxes.

I have a sample database that combines a textbox and combobox to allow you
to filter a combobox based on a few characters into the textbox. The textbox
and combobox interchange automatically via a class module:

Class module to combine a text box and combo box for limiting the rows in
combo box
http://www.accessmvp.com/KDSnell/SampleDBs.htm#CombinedTextComboBoxes

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken

I am sorry, I misspoke, this is not a subform. I type my part numbers
into
a Combo Box, not a subform.

Yes I have looked the contents of the table. It now shows 71587 lines of
data. If I type the part number into the combo box and press "enter",
then
the data shows up on the form just like it is supposed to. I just cannot
preview all of the data in the combo box.

I looked at the last line that I could view in the combo box, then went
into
the query and that line was line number 65535. I can view all of the
records
in the query, but not in the combo box.

Thanks
david

Obvious question: are you sure that the new parts are in the table? I
assume that you've visually looked at the datasheet view of the table and
confirmed that those records are there?

What is the Format property of the textboxes that are bound to the
subform's
query's fields?

The SQL statement that you posted is the query that is the subform's
RecordSource property?

What are the LinkMasterFields and LinkChildFields properties of the
subform
control?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message

Hi Ken

The data is stored in access. When I created the db, I did import it
from
an excel database.

Here is the sql for the query that the info comes from:

SELECT INV.QTY, INV.CONDITION, INV.MFG, INV.[P/N], INV.DESCRIPTION,
INV.LOCATION, INV.COST, INV.LIST, INV.WEIGHT, INV.NOTES, INV.FITS,
INV.SOS,
INV.[DATE IN]
FROM INV
ORDER BY INV.MFG, INV.[P/N];

I am not sure what info i can give you on the form and subform. My
database
is used to store my parts inventory information. When I type in the
part
number, I can click on a combo box and show 10 lines of information.
This
helps if I can remember the first few digits of a part number. I type
the
first digits in, click on the combo box, and choose the entry that I am
looking for.

I just started keeping a new manufacture of parts in my inventory. All
of
these part numbers start with letters, therfore they end up at the
bottom
of
the query and I cannot see them on the subform.

Thanks in advance
david


:

I'm not aware of any specific limitation for how many records can be
displayed in a subform. The number that you cite as a "limitation" is
the
maximum number of rows that can be in EXCEL 97/2000/2002/2003
spreadsheets,
but I assume you're not linking to a spreadsheet for the data.

Tell us more details about what you're doing and how you've set up the
form/subform.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
I have a rather large db that I have created myself. For the most
part
it
works fine, but I do have a few annoying things I would like to fix
if
possible.

I have 71425 records in my INV table. I use a query to sort that
information and a subform that displays it when needed. For some
reason
approximately 65535 records can be viewed in the subform.

Is there a limit on records that can be viewed in a subform. Did I
do
something wrong when I created the db?

Can someone please help?
Thanks in advance
david

 

[John W. Vinson]

I looked at your example. It sounds like it should work, although I am
still reletively new to Access, and I am clueless on some terms for example
where do I find: "the General Declarations section of your form?s module"

That just means to scroll up to the very top of the module. You should see

Option Compare Database

Edit that to

Option Compare Database
Option Explicit

and then the code from the webpage.