By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 981 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

Access limit a combo to 36000 records

P: n/a
Anyone knows how to avoid the limit to 36000 records that access has
with a combo box?
Any help will be appreciated.
Many Thanks... Claudia

Dec 5 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
cl**************@gmail.com wrote:
Anyone knows how to avoid the limit to 36000 records that access has
with a combo box?
Any help will be appreciated.
Many Thanks... Claudia
Greetings Claudia,

Here's something I did for JobID's after a combobox list got too long:

tblJobIDBands (linked)
BID AutoNumber
BandName Text
StartingNumber Long
EndingNumber Long

BID BandName StartingNumber EndingNumber
....
50 17700-17799 17700 17799
51 17800-17899 17800 17899
52 17900-17999 17900 17999
....

frmJob
lstJobBands
RowSource SELECT BandName, StartingNumber, EndingNumber, BID FROM
tblJobIDBands WHERE StartingNumber <= (SELECT MAX(JobID) FROM tblJobs)
ORDER BY BID DESC;
ColumnCount 4
ColumnWidths 1.5";0";0";0"
BoundColumn 1

lstJobBands_AfterUpdate()
If lstJobBands.Column(3) <"1" Then
cbxJobId.RowSource = "SELECT JobID FROM tblJobCosting WHERE JobID >=
" & lstJobBands.Column(1) & " AND JobID <= " & lstJobBands.Column(2)
Else
'RowSource when the BID = 1 is selected, "Internal expenses"
End Sub

I can populate tblJobIDBands as far out as I'd like since the Form Load
event does:

lstJobBands.Selected(0) = True
cbxJobId.RowSource = "SELECT JobID FROM tblJobCosting WHERE JobID >= "
& lstJobBands.Column(1) & " AND JobID <= " & lstJobBands.Column(2)

The table starts at JobID = 13000 since jobs prior to that are
archived.

The subquery in lstJobBands selects the appropriate ranges.

For example, if the largest JobID in tblJobCosting is 17777, the
listbox shows

17700-17799
17600-17699
....
Internal Expenses

17700-17799 starts out selected and cbxJobID starts out with the
JobID's within that band.

This gives 50 choices instead of several thousand choices. With 36000
records I would likely divide into 1000's first, then 100's before
populating the combobox and have the latest 100 be the default. I
think any way of breaking down the list, such as dates or initial
letters, is amenable to this technique.

I hope this helps,

James A. Fortune
CD********@FortuneJames.com

Dec 5 '06 #2

P: n/a
On 4 Dec 2006 21:38:17 -0800, cl**************@gmail.com wrote:
Anyone knows how to avoid the limit to 36000 records that access has
with a combo box?
Any help will be appreciated.
Many Thanks... Claudia
See:
http://www.allenbrowne.com/ser-32.html
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Dec 5 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.