Updating Tables After Event - Help!!

[Solaron]

Hi,

i have this situation:

[Table] called 'RAP Courses'
[Table] called 'RAP Course CEQ Demands'

for each course there may be up to 140 demands (basically where the students are coming from) which is why i haven't included the demands as a static field in the Courses table - would look too much like a spreadsheet and lots of reduntant data.

I currently display the courses in Datagrid view with the demands embedded into it so when i press the + sign, i can see their demands.

Each course has a maximum capacity, and each course demand specifies how much capacity it needs.

I want to be able to have it so that when i view these two tables in the dataset fashion, i can change the course demand capacity and it automatically updates the fiend in table [Courses] called 'Spare Capacity'.

I know this can be done with a query and all this can be done with views made up of queries - but the trouble is, if ii use queries in a one-many relationship - i cannot edit any details on the 'one' side of the relationship meaning i cannot change things like 'Course Start Date'.

Here is my line of code i have so far for the 'After Update' on the [Course Demands] subform:

CurrentDb.Execute "UPDATE [RAP Courses] set [RAP Courses].[SPARE] = (sum(ALLOCATION) FROM [RAP Course CEQ Demands] WHERE [RAP Course CEQ Demands].[COURSE ID] = [RAP Courses].[COURSE ID])"

it doesn't work


Any help is greatly appreciated!

[Solaron]

Okay, sorry for this:

i have discovered this is the correct code:

CurrentDb.Execute "UPDATE [RAP Courses] set [TEST] = (SELECT sum(ALLOCATION) FROM [RAP Courses CEQ Demands] WHERE [RAP ID] = [RAP Courses].[COURSE ID])"

However this does not work in access - it says "This needs to be an updatable query"

Any suggestions?

Thanks,

Chris