Unanswered: Show first 10 records in subform problem
I have a problem that i have come across which is basically i have a subform which gets the information from a table now what i wish to do is firstly load the subform with only say the first 10 records from the table and then have a next and previous button which when clicked will show the next 10 records of table or when the previous button is clicked will show the prev 10.
i tried to docmd.gotorecord " " next etc but that doesnt work i think it doesnt bring out the details as each record so i think i may need to filter it and show the data as how it looks in the table.
Off hand I'ld say playing with the RecordSource by inserting a Query when needed is the way to go here and if you were doing this in MySQL life would be a little easier since its' SQL contains the LIMIT statement as in:
SELECT * FROM myTable LIMIT 1,10
or event a little help from Oracles' ROWNUM statement would be nice to simplify things.
Not to be for Access...To do this in Access the SQL statements won't look as simple as it might for MySQL (shown above). The only way I can see to do this is with nested queries. No matter which method is used, you will need to keep track of where you are within the table that you are viewing since both the Next and Previous option needs to play a role here.
Getting the first 10 records is easy:
SELECT TOP 10 * FROM yourTableName;
but getting the 11th to the 20th record then the 21st to the 30th record (and so on) gets to be a little more complicated:
SELECT TOP 10 *
WHERE TableIDField > (SELECT MAX(TableIDField) FROM yourTableName WHERE TableIDField IN (SELECT TOP 10TableIDFIeld FROM yourTableName ORDER BY TableIDField));
Looks a little more drastic doesn't it, but it works. I'm sure someone in this forum has a better and faster statement.
It would of been a lot easier looking Query if we can guarantee that our AutoNumber ID's will be sequentialy in numerical order as in: 1, 2, 3, 4, 5, 6, 7, etc., but as you know, this will not be the case since your Table could contain Record ID's of 1, 2, 3, 6, 12, 13, 14, 22, ...etc. Yes, they may increment but not necessarily in the fashion you would like.
All you need do now is snag when the Next & Previous buttons are selected. The number that is colored Blue and bold located within the query should be a variable which is to be incremented or decremnted depending upon which direction you are going or which navigation button is selected.
Self Taught In ALL Environments.....And It Shows!
couldn't you 'just' fake it
set the subform to continuous view and so that it will only show 10 records
then get the user to press the slider to select the next screenfull of records, which should be + or - 10.....
otherwise you are going to have to select the records that match your criteria, work out which ones you want to display.
unless you have a definate user requirement to only show 10 records I'd implemnet this and see what happens....
Thanks cyberlynx & healdem for your replys, yes there will be a autonumber in the form as when a user needs to change a item he just selects the name and changes it not the number, i was thinking something similar to do so i will give it a try tomorrow in the morning, also you said fake it how would i do that and just place a slider, is that you meaning a scroll bar? i can do that now but the problem there is the scroll bar will give it a tacky look at this moment they look like actual buttons which i have a onclick event on the textbox so which ever one is clicked in the query it does the same proceudre which is what i wanted its like a array of command buttons but as subforms because you cant create an array in access like in vb.
thanks guys and i would like to know more about the fake thing that you said.