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: Autonumbering in MS Access 2003

Discussion in 'Business Applications' started by saggyguts, Jan 23, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. saggyguts

    saggyguts Thread Starter

    Joined:
    Jan 23, 2006
    Messages:
    2
    Somebody please help me before I finally crack up!!!

    For a course assignment, I have to design and build a database using Access 2003 which stores information about bands and concerts. After finally getting my head round Normalization (sort of!!) I've created 6 tables, Manager, Act, Act Booking, Concert, Concert Booking, and Venue, with the Primary keys set as ManagerID, ActID, Act_BookingID, ConcertID, Concert_BookingID, and VenueID respectively. I've also set them all to Autonumber. All the tables are related in the order above. Now I'm trying to create the forms to input the data to the Act table, but I keep getting an error message stating that I can't update a record, as a related record is required in another table. I've narrowed the problem down to the fact that although the autonumbering is working in the One table, it's not updating the same field (set as a Foreign key) in the Many table. I've tried using subforms and queries, but they still come up with the same result. I discovered that if I manually type in the Number, I can update the record. Could someone please tell me how I can get the Numbered field to correspond to the Autonumbered field.
     
  2. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Hey there, welcome to TSG (y)

    It sounds as though you haven't established referentially integral relationships between your tables. In a relational database management system, you use relationships to link different relations (read: tables) to one another; that way Access handles the cascading data (like your foreign keys).

    • Open your application and navigate to the Database window.
    • From the menu bar, select Tools-->Relationships... (this opens the Relationships window).
    • In order to establish true 1-to-many relationships between your tables, click-and-drag the primary keys onto the foreign keys in your related tables. Right-click the indicator lines and select Edit Relationship... from the context menu.
    • In the Edit Relationships window, check Enforce Referential Integrity. Don't worry about the data cascading right now.
    That should be enough to get you going. If you have trouble with that, post back please.

    HTH

    chris.
     
  3. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    It could also be the fact that when using subforms you need to use the "linking" field in your relationships in the "master/child" link betwen the main form and the subform.
    Chris, I think the error quoted is when you have referential integrity set and that is why it needs the "common" record.
    The other cause can be entering the data in to the child table/form before the "Master" table has the record created.
     
  4. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    *slaps his head* duh.

    Yeah, OBP has it, you must already have your relationships established. So, erm, ignore my last post. The point is, you can't create records for the many side of a 1-to-many relationship unless the parent record has been created. So somewhere you're trying to create, say, a concert booking, without having the concert set. Or, you're trying to do any of that without having a venue or manager record.

    You'll have to incorporate into your design some user restrictions such that it is impossible to create a record for a child relation without its parent record already existing. As OBP mentioned, form/subform constructions are good candidates--as long as you disable the subform until the parent form has been completed or otherwise control the user's ability to enter data in fields that would cause errors. One great way to do it is to create a form that has a combo box or list box in the header listing all possible parent record items--like all the venues, for example, or all the concerts. That establishes the master/child link OBP mentions.

    Sorry for the misdirection :eek:

    chris.
     
  5. saggyguts

    saggyguts Thread Starter

    Joined:
    Jan 23, 2006
    Messages:
    2
    Thanks for the input guys, but unfortunately I'm still having the problem. Due to time restrictions, I've put an input field for the ManagerID on the child form with a note telling the user to check the Manager form for the relevant ID. Not exactly the most efficient way of working, but my tutor's cool with it. According to her if I've enforced referential integrity, the two should update automatically, but she thinks that because there's been a few changes during the build, that may have caused things to awry. She's happy for me to hand it in as it is as long as I mention the problems, but if I'm really bothered about it working properly (which I'm not!!!) then the best bet is to build a new database.

    Once again thanks for your help and the warm welcome.:D
     
  6. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    No problem, if you have any more questions you know where to find us (y)

    For now, if you're satisfied with this issue's resolution, you may mark this thread Solved using the Thread Tools at the top of the page.

    chris.
     
  7. 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!

Thread Status:
Not open for further replies.

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

  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