I have a table that holds costing categories which are grouped using a groupID:

CatID Cat GroupID
0001 Tables 1
0002 Chairs 1
1001 Sofas 2
1002 Rugs 2
1003 Cushions 2

I have built a form to allow users to be able to add a new categories. The form has a drop down list derived from the group table and a text box to allow the user to enter the category. I need to be able to find the TOP CatID according to the group that has been selected and increment it by one and then add the groped, CatID and the Cat to the Category table. Can anyone help please.

You will need to query your table for the freshly entered catgegory: look to help for FindFirst function. If that does not return then there are no existing records with the same category.
Creating a new CatID looks to be a bit more difficult as it will need to follow some predefined business rule which is not redilly apparent by your example.

I think all i really need to do is find the top CatID where groupID = n, assign a variable to it and then increment it by one - this new value would then be added to the table as CatID along with the GroupID and the Category text.

... And CatID is the primary key for the table? Now you have just wandered off into that vast arena very hotly debated, concerning natural and derived keys.

Be that as it may, what determines which GroupID the new user defined (scary territory) Category belongs to? Does the GroupID get defined at runtime as well?

The user is presented with a form. There is a drop down containing groups (Dining Room Furniture (ID = 1), Lounge Furniture (ID = 2)). There is also a text field to enter the Name of the category. If they choose Lounge furniture from the drop down and enter "Side Board" in the text field then on submit i need to write code that will take the highest CatID in Group 2 (i.e. 1003) and increment it by 1. I can then update the Category table with - CatID = 1004, Cat = Side Board, GroupID = 2.

I hope this explains the problem better.

Cheers

Dave

SELECT
MAX( CatID ) + 1 as new_id
FROM
YourCatTable
WHERE
GroupID = GroupIdSelected


That would be your SQL string returning you new CatID. Use this then to update as you wanted.

Still a bit confused as to the why of it all but the above should return the next following CatID by GroupID