August 9, 2005 at 8:09 am
Hi
I am new to Access Projects, but am beginning to find my way around - apart from a few problems.
I have a combo which looks up a list of students. The list is about 12000 rows. I have created an sp so I can use a variable for the current year -and it all works well. However, I can only see the first 10000 rows from the form.
I have saved the sp with 0 as the max rows - but still only see the first 10000. I changed the max to 20000 and I can now see them all when I open the sp.
I fill the combo when on the On open event of the form.
Me!cboStudentSurname.RowSource = "Exec sp_lststudent_surname '" & Forms!frmacyear!AcYear & "'"
All seems to work fine, but only for 10000 rows. Has anybody any idea how show all rows for just this combo. I have tried TOOLS Options and changed the list amount from 1000 to 0 (and various amounts) - to no avail.
I am also concerned about the amount of data traffic. I could use some pointers on this.
Thanks
Paul
August 9, 2005 at 8:19 am
This seems to be an access bug but caused by a design error. You should never load 10K students in a list. You should have the users enter a search condition or go by at least 1st letter. I don't know if you could cut 'em down by program/group too. That way you don't load too much data and it takes less time to find the student in question.
August 9, 2005 at 8:37 am
Thanks, Remi
I'll make the change.
Also, if I have a table for a subform and have it linked by Master and Child fields to to the main form - does this just pull out the one required record from the table, or a large amount of data?
Thanks again
Paul
August 9, 2005 at 8:50 am
Check with the profiler to be sure... but it should fetch the data on a need to show basis (for the subform).
August 9, 2005 at 8:57 am
Hi
OK will do.
I said yes I'll change the list, but I think I am getting something wrong.
I have changed the sp fine. That will now work on a letter. However, I'm not sure how to add a second parameter to a combo Rowsource - it has no Inputparameters.
I tried adding a further parameter to the code below (using a comma and the reference to the field with the first letters of the name in) , but it didn't work ('can't find the second parameter). Is there a better way to do this.
Me!cboStudentSurname.RowSource = "Exec sp_lststudent_surname '" & Forms!frmacyear!AcYear & "'"
Thanks again
Paul
August 9, 2005 at 9:04 am
Me!cboStudentSurname.RowSource = "Exec dbo.sp_lststudent_surname '" & Forms!frmacyear!AcYear & "', '" & "letter" & "'"
btw don't use sp_ as a predixe, the server will go look into master first instead of the db to find the sp.
August 9, 2005 at 9:43 am
Thanks, Remi
That works great.
However, the problem isn't quite solved.
I have been putting the combo's Recordsource code in the On Open event of the form, not the On Click event of the combo - because for some reason I couldn't get that to work. (In fact I've never been able to get this to work at all).
If I go down the route where I am thinning down the rows by letters in a field, I need it to work on the combo's OnClick event - (I guess).
Is there any reason why it shouldn't work from the OnClick event? Is there anything I might try?
I will amend the sp_ as you suggest.
Paul
August 9, 2005 at 9:49 am
I never tried to do it on the click... maybe there's an exec time limit on that event (if such a thing exists). However in this case you have to filter when the user choses the letter (from another combo/list), not on the click.
August 9, 2005 at 9:55 am
Thanks Remi
OK, I'll stick with what works.
It's all working well thanks to your help - once again.
Best wishes
Paul
August 9, 2005 at 9:56 am
HTH.
August 9, 2005 at 5:06 pm
Hi Paul,
there appears to be a bit of disinformation here...
1) I think you are changing the wrong option - you are probably changing the option that controls the list drop downs for the "Query by Forms" objects.
Access does default to 10,000 records for forms and combo boxes. You can change the default as follows: Go to Tools-->Options--Advanced-->Default Max Records. If you like, you can return all rows, by setting this value to 0. This will fix your main problem. (The fact that this setting affects -combo boxes- is another undocumented present from MS. Good idea - it would be nice if they told us about it though!)
2) In Access projects, you can return any number of records you want in a combo box. Access does not load the combo unless you actually open it - thus there is really no time lag in opening a form with 1M records in a combo. Also, Access generally returns SQL Server records asynchronously, so time lags are minimal. Access will NOT fill the combo with all of your records at once, however. If your text is not present in the combo records, Access will fill the combo with more records until it finds your text. This technique is amazingly fast. For example, I routinely load 75,000 records into a combo box, and there is no discernable lag (i.e. it's instantaneous) in loading the combo or searching for records. If you will be using the combo for a large number of lookups (and you have a fast modern network), it is often better to load all of your records into the combo, and let the client handle the lookups rather than the server - it takes the load off of the server, and this is an easy job for the Access client. If you have millions of rows, I'm not sure how fast it would be - you would need to test it before investing time writing code to do custom partial lookups.
HTH,
Rich
August 9, 2005 at 5:12 pm
"I have been putting the combo's Recordsource code in the On Open event of the form, not the On Click event of the combo - because for some reason I couldn't get that to work. (In fact I've never been able to get this to work at all)."
Hmmm, I thought I answered this one for you before. OnClick only works when you select an item from the list. It does not fire by just clicking on the combo. This is the intended behaviour. You could always use GotFocus or MouseDown/Up instead.
HTH,
Rich
August 10, 2005 at 12:27 am
Paul, you say you are worried about data traffic so just something to watch out for: I havn't used Access for about 3 years, but one of the things it used to do, is download all the data of a linked table to a temp file in your local profile. It is because of this reason that we still discourage the use of Access in our company.
5ilverFox
Consulting DBA / Developer
South Africa
August 10, 2005 at 1:16 am
Forms Usually only show the first 10000 records by default. If you show the navagation bar and click end you will get a slider to increase the number of records. This may be your problem
August 10, 2005 at 2:54 am
Thanks everybody for excellent comments.
Thanks Rich for your very complete reply once again.
It seems from this that I can use the combo loading all records - without causing undue network traffic. This would actually make life a lot simpler - particularly as this is how I have produced most of my combos so far.
Also, the On Click - I thought I had it working originally - but was mistaken (I'd left the code in the OnOpen event of the form). I still, for some unfathomable reason, can't get it to work from the OnClick. However, it works excellently from the GotFocus.
A great many thanks again
Paul
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply