Counting records consecutively and resetting to 1 if named field value changes

[MrCaptain]

Hi

I am constructing a database for running courses.

I have a main form called Courses and two subforms, Sessions and Bookings.

The Sessions form is intended to allow the user to enter the number of sessions the course lasts. Many will be one, some will vary from one upwards to perhaps twenty or more. Once a session has been established then bookings can be made.

I have tried in vain and searched far and wide to find a way of entering a value in a combobox on the Sessions form and then gaving Access create that many records each numbered as follows

CourseID SessionID SessionDate SessionNo
3, 31, 12/11/12, 1
3, 32, 12/11/12, 2
3, 36, 12/11/12, 3

The date is entered after the records are created. I have shown changes in SessionID because records might be deleted or SessionNo changed. Needs to happen automatically.

Finally if the CourseID (this is the named field in the title) changes then a new Sessions record starts at SessionNo 1 and so on.

The furthest I have got is to count thenumber of sessions using the following formula:

=IIf(IsError(DCount("SessionsID","qrySessions","SessionsID <=" & [SessionsID])),"",DCount("SessionsID","qrySessions","SessionsID <=" & [SessionsID]))


Clearly this is not as straightforward as I had imagined it to be, but then what is unless you are a good programmer?

Any help or guidance much appreciated.

Thank you in advance to anyone who can help

Regards

V