By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,160 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

I Can't Update Info in my subforms

P: 7
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
Nov 23 '06 #1
Share this Question
Share on Google+
17 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 23 '06 #2

P: 7
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
Nov 24 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 25 '06 #4

NeoPa
Expert Mod 15k+
P: 31,308
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.
Nov 25 '06 #5

P: 7
Right,

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

Nikki
Nov 25 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 25 '06 #7

P: 7
As requested:

SELECT Courses.CourseName, Courses.Duration, Courses.Cost, Courses.MaxNo, Courses.MinNo, Events.Date, Events.StartTime, Events.EndTime, Tutors.TutorName, Venues.VenueName, Attendees.Forename, Attendees.Surname, Contacts.[Company Name]
FROM (Venues INNER JOIN (Tutors INNER JOIN (Courses INNER JOIN Events ON Courses.CourseID = Events.CourseID) ON Tutors.TutorID = Events.TutorID) ON Venues.VenueID = Events.VenueID) INNER JOIN ((Contacts INNER JOIN Attendees ON Contacts.CompanyID = Attendees.ContactID) INNER JOIN Bookings ON Attendees.AttendeeID = Bookings.AttendeeID) ON Events.EventID = Bookings.EventID;
Nov 26 '06 #8

NeoPa
Expert Mod 15k+
P: 31,308
Try changing the FROM clause to this.
Expand|Select|Wrap|Line Numbers
  1. FROM (((((Venues INNER JOIN Events
  2.     ON Venues.VenueID = Events.VenueID) 
  3.     INNER JOIN Tutors 
  4.     ON Events.TutorID = Tutors.TutorID) 
  5.     INNER JOIN Courses 
  6.     ON Events.CourseID = Courses.CourseID) 
  7.     INNER JOIN Bookings 
  8.     ON Events.EventID = Bookings.EventID) 
  9.     INNER JOIN Attendees 
  10.     ON Bookings.AttendeeID = Attendees.AttendeeID) 
  11.     INNER JOIN Contacts 
  12.     ON Attendees.CompanyID = Contacts.ContactID;
Nov 26 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Try changing the FROM clause to this.
Expand|Select|Wrap|Line Numbers
  1. FROM (((((Venues INNER JOIN Events
  2. ON Venues.VenueID = Events.VenueID) 
  3. INNER JOIN Tutors 
  4. ON Events.TutorID = Tutors.TutorID) 
  5. INNER JOIN Courses 
  6. ON Events.CourseID = Courses.CourseID) 
  7. INNER JOIN Bookings 
  8. ON Events.EventID = Bookings.EventID) 
  9. INNER JOIN Attendees 
  10. ON Bookings.AttendeeID = Attendees.AttendeeID) 
  11. INNER JOIN Contacts 
  12. ON Attendees.CompanyID = Contacts.ContactID;
To make it updatable, it may be required to change all the INNER JOINS to LEFT JOINS.
Nov 27 '06 #10

NeoPa
Expert Mod 15k+
P: 31,308
I would have thought the reverse, but I just tried it both ways with a pair of simple tables and it stayed updatable in both scenarios.
I don't know which is better with a more complex structure as is found here.
My suggestion : Experiment both ways (don't quote me out of context on this) and see what works - post back when you have an answer.
Nov 27 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
I would have thought the reverse, but I just tried it both ways with a pair of simple tables and it stayed updatable in both scenarios.
I don't know which is better with a more complex structure as is found here.
My suggestion : Experiment both ways (don't quote me out of context on this) and see what works - post back when you have an answer.
Truthfully, it depends on the joins, relationships and cascades. The fact that the poster can currently update part of the query and not the other part leads me to believe that the left join might work. Without seeing the data it's very hard to tell.

As you say try both and see what works.

Mary
Nov 27 '06 #12

P: 7
Try changing the FROM clause to this.
Expand|Select|Wrap|Line Numbers
  1. FROM (((((Venues INNER JOIN Events
  2.     ON Venues.VenueID = Events.VenueID) 
  3.     INNER JOIN Tutors 
  4.     ON Events.TutorID = Tutors.TutorID) 
  5.     INNER JOIN Courses 
  6.     ON Events.CourseID = Courses.CourseID) 
  7.     INNER JOIN Bookings 
  8.     ON Events.EventID = Bookings.EventID) 
  9.     INNER JOIN Attendees 
  10.     ON Bookings.AttendeeID = Attendees.AttendeeID) 
  11.     INNER JOIN Contacts 
  12.     ON Attendees.CompanyID = Contacts.ContactID;
This is now asking for Value Parameters on the attendees and contacts.
Nov 28 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
This is now asking for Value Parameters on the attendees and contacts.
Try this out ....

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Courses.CourseName, Courses.Duration, Courses.Cost, 
  3. Courses.MaxNo, Courses.MinNo, Events.Date, Events.StartTime, 
  4. Events.EndTime, Tutors.TutorName, Venues.VenueName, 
  5. Attendees.Forename, Attendees.Surname, Contacts.[Company Name]
  6. FROM ((((Courses INNER JOIN Events 
  7. ON Courses.CourseID = Events.CourseID)
  8. INNER JOIN Tutors 
  9. ON Tutors.TutorID = Events.TutorID)
  10. INNER JOIN Venues
  11. ON Venues.VenueID = Events.VenueID)
  12. INNER JOIN (Bookings INNER JOIN 
  13. (Attendees INNER JOIN Contacts
  14. ON Attendees.CompanyID = Contacts.ContactID) 
  15. ON Attendees.AttendeeID = Bookings.AttendeeID) 
  16. ON Bookings.EventID = Events.EventID);
  17.  
Nov 28 '06 #14

P: 7
Try this out ....

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Courses.CourseName, Courses.Duration, Courses.Cost, 
  3. Courses.MaxNo, Courses.MinNo, Events.Date, Events.StartTime, 
  4. Events.EndTime, Tutors.TutorName, Venues.VenueName, 
  5. Attendees.Forename, Attendees.Surname, Contacts.[Company Name]
  6. FROM ((((Courses INNER JOIN Events 
  7. ON Courses.CourseID = Events.CourseID)
  8. INNER JOIN Tutors 
  9. ON Tutors.TutorID = Events.TutorID)
  10. INNER JOIN Venues
  11. ON Venues.VenueID = Events.VenueID)
  12. INNER JOIN (Bookings INNER JOIN 
  13. (Attendees INNER JOIN Contacts
  14. ON Attendees.CompanyID = Contacts.ContactID) 
  15. ON Attendees.AttendeeID = Bookings.AttendeeID) 
  16. ON Bookings.EventID = Events.EventID);
  17.  

No, still coming up with the value parameters
Nov 28 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
No, still coming up with the value parameters
Nikki,

Are these fields actual joins in your tables that you are using to join the tables on. Have they got the same data type. e.g. Are you joining number fields of the same format?

If you can't figure this out, you will have to post each of the tables with a list of the fields and their data types and formats for us to work it out.

Mary
Nov 28 '06 #16

P: 7
Nikki,

Are these fields actual joins in your tables that you are using to join the tables on. Have they got the same data type. e.g. Are you joining number fields of the same format?

If you can't figure this out, you will have to post each of the tables with a list of the fields and their data types and formats for us to work it out.

Mary
Hi Mary,

I've taken a look and the fields in the query are not actually the ones that are joined by relationships.

I have below the tables involved and the data types and formats as requested.

COURSES
CourseID Number PRIMARY KEY
CourseName Text
Duration Number
Cost Currency
MaxNo Number
MinNo Number
CategoryID Number (Lookup Field)

EVENTS
EventID Autonumber PRMIARY KEY
CourseID Number (Lookup field so that coursename shows only)
Date Date/Time
StartTime Date/Time
EndTime Date/Time
TutorID Number (Lookup field so that tutorname shows only)
VenueID Number (Lookup field so that venuename shows only)

TUTORS
TutorID Number PRIMARY KEY
TutorName Text
Address1, Address2, Address3, Address4 & Postcode all Text
TelNo Text with a telephone number input mask
Email Hyperlink
MobileNo Text with telephone number input mask

VENUES
VenueID Number PRIMARY KEY
VenueName Text
Address1, Address2, Address3, Address4 & Postcode all Text
TelNo Text with a telephone number input mask
FaxNo Text with a telephone number input mask
Email Hyperlink
ContactName Text

ATTENDEES
AttendeeID Autonumber PRIMARY KEY
Forename Text
Surname Text
JobTitle Text
ContactID Number (Lookup field so that contact name shows only
SpecialRequirements Text

CONTACTS
CompanyID Autonumber PRIMARY KEY
CompanyName Text
First Name Text
Last Name Text
Work Phone Text (with telephone number input mask)
Address Line 1, Address Line 2, Suburb, Town, County, Postal Code all text
Mobile Phone Text (with telephone number input mask)
Fax Number Text (with telephone number input mask)
Email Address Hyperlink


These are the tabls that are used in the query.

Nikki
Nov 29 '06 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
You haven't included the bookings table but I'll assume the fields are as the query.

You will have to change your relationships in the relationships window to reflect the joins in the queries. Based on your table descriptions these should be the relationships anyway.

Mary
Nov 30 '06 #18

Post your reply

Sign in to post your reply or Sign up for a free account.