I Can't Update Info in my subforms

[NikkiR]

Hi, I'm new to the forum and I am currently designing a database that my colleagues can use to book people onto our courses.

I have a form set up with two subforms. The main form is one that has the course details, I then have a subform with the individual dates throughout the year. I also have a further subform that shows who has booked onto each date when I am on the course and date applicable.

Unfortunately I do not seem to be able to add new records to either of the subforms I can only change details of current records. I can however add new records to the main form.

Where am I going wrong??

I would appreciate any help you can give me.

Nikki

[MMcCarthy]

There are a number of possibilities Nikki.

Firstly are your subforms based on queries. If so try running those queries outside the forms. If you cannot add a record at the end then the query is not updateable.

If this is the case post the sql for your queries and we'll take a look at them.

If not, let me know and we'll try something else.

Mary

Hi, I'm new to the forum and I am currently designing a database that my colleagues can use to book people onto our courses.

I have a form set up with two subforms. The main form is one that has the course details, I then have a subform with the individual dates throughout the year. I also have a further subform that shows who has booked onto each date when I am on the course and date applicable.

Unfortunately I do not seem to be able to add new records to either of the subforms I can only change details of current records. I can however add new records to the main form.

Where am I going wrong??

I would appreciate any help you can give me.

Nikki

[NikkiR]

The subforms are based on a query. I tried to update the form and the first part of the query could be updated but then when I got to what would be the first subform again it would not let me update.

Does that help with identifying the problem?

There are a number of possibilities Nikki.

Firstly are your subforms based on queries. If so try running those queries outside the forms. If you cannot add a record at the end then the query is not updateable.

If this is the case post the sql for your queries and we'll take a look at them.

If not, let me know and we'll try something else.

Mary

[MMcCarthy]

The subforms are based on a query. I tried to update the form and the first part of the query could be updated but then when I got to what would be the first subform again it would not let me update.

Does that help with identifying the problem?

Not really Nikki.

For the moment I want to forget about the form and see if there is a problem just with the query. The only way to do that is to run the query in the query window and see if you can add a complete record to the end of it and save it. You can always delete the record later.

What I'm worried about is that the problem is with the query and not the subform.

Mary

[NeoPa]

Make sure you do this with the query used for the subform though (the one with the problem) and not the query for the main form if that doesn't have a problem.

[NikkiR]

Right,

I have tried that and I can only get half way along the record and then it stops me entering the information.

Nikki

[MMcCarthy]

Right,

I have tried that and I can only get half way along the record and then it stops me entering the information.

Nikki

Ok your query is not updateable. At a guess I would say it has to do with the relationships between your tables and the joins. If you would like to post the sql of the query we will have a look at it.

If you're not sure how to get this then in query design view change the view to sql and copy and paste the code here.

Mary

[NikkiR]

As requested:

SELECT Courses.CourseN ame, Courses.Duratio n, Courses.Cost, Courses.MaxNo, Courses.MinNo, Events.Date, Events.StartTim e, Events.EndTime, Tutors.TutorNam e, Venues.VenueNam e, Attendees.Foren ame, Attendees.Surna me, Contacts.[Company Name]
FROM (Venues INNER JOIN (Tutors INNER JOIN (Courses INNER JOIN Events ON Courses.CourseI D = Events.CourseID ) ON Tutors.TutorID = Events.TutorID) ON Venues.VenueID = Events.VenueID) INNER JOIN ((Contacts INNER JOIN Attendees ON Contacts.Compan yID = Attendees.Conta ctID) INNER JOIN Bookings ON Attendees.Atten deeID = Bookings.Attend eeID) ON Events.EventID = Bookings.EventI D;

[NeoPa]

Try changing the FROM clause to this.

FROM (((((Venues INNER JOIN Events
    ON Venues.VenueID = Events.VenueID) 
    INNER JOIN Tutors 
    ON Events.TutorID = Tutors.TutorID) 
    INNER JOIN Courses 
    ON Events.CourseID = Courses.CourseID) 
    INNER JOIN Bookings 
    ON Events.EventID = Bookings.EventID) 
    INNER JOIN Attendees 
    ON Bookings.AttendeeID = Attendees.AttendeeID) 
    INNER JOIN Contacts 
    ON Attendees.CompanyID = Contacts.ContactID;

[MMcCarthy]

Try changing the FROM clause to this.

FROM (((((Venues INNER JOIN Events
ON Venues.VenueID = Events.VenueID) 
INNER JOIN Tutors 
ON Events.TutorID = Tutors.TutorID) 
INNER JOIN Courses 
ON Events.CourseID = Courses.CourseID) 
INNER JOIN Bookings 
ON Events.EventID = Bookings.EventID) 
INNER JOIN Attendees 
ON Bookings.AttendeeID = Attendees.AttendeeID) 
INNER JOIN Contacts 
ON Attendees.CompanyID = Contacts.ContactID;

To make it updatable, it may be required to change all the INNER JOINS to LEFT JOINS.