1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access Database Problem

Discussion in 'Business Applications' started by Eadinator, May 2, 2010.

Mark Solved
Thread Status:
Not open for further replies.
Advertisement
  1. Eadinator

    Eadinator Guest Thread Starter

    As part of my coursework, I have to create an Access Database. I have a slight problem where, if you go to the form 'frmCustomer' and try to add a new record in the frmAllBookings subform within the frmBooking subform, it will not let you add the new record and will display the following error:

    Cannot add record(s); join key of table 'tblBooking' not in recordset.

    I have gone through all the settings and I can't figure out what's causing it. Everything else seems to work fine.

    I have uploaded a copy of the file. Any help on this would be greatly appreciated.

    Matthew.
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Mathew, I do not normally help posters with school or course work as that is not fair to other students.
    Your Relationships and Table design are Good.
    However your approach to the Main/Subform design is not what I would normally expect.
    The following points need looking at.
    1. Both Main & Sub forms are using the same query, "qryBooking" and joined (Master Child Links) via the Customer ID. Normally the Main form would be using the Customer Table or Query as it's Record Source and you would enter a new or select an old Customer on that form and then "Select" the Course they would attend.
    2. The subform would normally use the tblBooking table or query to receive the Customer ID (from the Master Child Link) and the CourseID from a Combo box selection. Where the Course had been previously entered elsewhere.
    3. You could have the Course on the Subform and enter it at the same time as making the booking, but that is not the normal situation.
     
  3. Eadinator

    Eadinator Guest Thread Starter

    Thanks for the reply.

    I'm having trouble understanding how you think it should be set up. I apologise for this, there are 6 tasks and this task is the hardest. It is stressing me out as a whole. I have made the design a bit simpler and uploaded the new version.

    Basically, on the form frmCustomer, the user uses the Select Customer Combo Box to display a customer record on the form. In the frmBooking subform, all of the bookings made by that customer are displayed. A user can change the course of a booking record with the Change Course Combo Box next to the associated record in the datasheet.

    A user should also be able to add a new booking record in the datasheet for that customer, basically they just, on a new record, choose the course from the change course combo box and the record is created.

    I have done this in an old copy of the database, in where the booking subform contains the records as a continuous form along with the subtotal, discount and total fields in the subform footer. In the new database, I have displayed the records in a datasheet and the combo box is displayed as a field within the datasheet. I have had to display the price fields as a single form as a subform of the frmCustomer because they will not appear within the datasheet.

    As far as I can see, the links in both versions are identical, but yet the old version works. I am thinking it could be because I am displaying the fields as a datasheet in the new version?

    I have also uploaded a copy of the old version for reference. You can get them both at http://tech3.me/db.zip

    Thanks for any help.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Mathew, these 2 are much better, personally I prefer the subform on the old version (changed to Continuous Form mode).
    So what is your current problem?
     
  5. Eadinator

    Eadinator Guest Thread Starter

    The continuous forms one was version 1, the one I originally posted was version 2, the updated version of that was version 3. I need to be able to add a new booking record in the booking subform of version 3. Version 1 does this. I can't understand what's causing the problem, maybe it is that version 3 has a datasheet and version 1 is in continuous forms. Maybe there is some technical difference there? All the other settings and links seem to be the same in both versions.

    Thanks,

    Matthew.

    NB: I chose to switch to a datasheet because I found this to be much more scalable and easier to manage.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes, datasheets are easier for the developer, but not so user freindly for the User. The recordset of the latest Subform is Not Updatable due to the Combo for Changing the Course using the CourseID as it's "Control Source" which Conflicts with the "tblBooking.CourseID" field called CourseID.
     
  7. Eadinator

    Eadinator Guest Thread Starter

    But is that definitly because it is a datasheet because the same control source is used in the continuous forms one (version 1) and there is no conflict. If so, would I be able to move this combo box away from the datasheet to get it to work or would I just have to use a continuous form?

    Matthew.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The new version has the CustomerID in the qryBooking Query from the Customer Table, whereas the Old one is using the CustomerID from the tblBooking table, in fact the old query does not even have the Customer table in it. That is stopping the Subform from updating.
     
  9. Eadinator

    Eadinator Guest Thread Starter

    Thanks! That sorted it. I can't believe I missed that.

    That's a big relief anyway, thanks for your help.

    Matthew.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/920644

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice