how to use group by method in vba code

[sreedharb79]

dim section as string
dim admn as number
dim name as string
dim roll_no as number

table

rooll_no admn name section
1 --------- 99 ---- abc ---- a
2 --------- 100 ---- def ---- a
3 --------- 101 ---- ghi ---- a
1 --------- 102 ---- abc ---- b
2 --------- 103 ---- def ---- b
3 --------- 104 ---- jkl ---- b
1 --------- 107 ---- mno ---- c
2 --------- 109 ---- klm ---- c
4 --------- 111 ---- nok ---- c

this is table

so i want do a coding in vba using group by method
because
there is three sections a,b,c so
i like to generate section wise roll number
so please give me exampe

[MMcCarthy]

If I understand what you are asking correctly. You want to create the next roll_no based on each section.

Firstly, as these are fields in your table you don't need to create variable names for them. You need to build a query using recordsets in vba.

Check out this article on using recordsets.

Your query would need to be something like ...

SELECT Max([Roll_No]) As MaxRollNo 
FROM TableName
WHERE Section='a';

You could then set the next Roll_No for that section to MaxRollNo + 1