Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problems with 'Add Record' in Access 2000 Form

    I've got an Access database set up involving, basically ,3 tables.
    Originally, I created a master form (pointing to the master table) with an embedded form (which points to a separate table). The primary key in both tables is an autonumbered longinteger.
    I then created a relationship between the master table and the table for the embedded subform.

    When I clicked the "Add Record" button, (*), next to the navigation buttons, it created a new record in both tables.

    Then, I decided to add a third table, which would be accessed via an additional embedded subform, so I added to the existing relationship (between the master and first subform's table) the table for the additional subform. When I clicked Enforce Referential Integrity and clicked okay, I didn't get any messages, but clicking the add record button on the master form would not create a new record in the second subform. When I went back to check off "Cascade Update Related Fields" and "Cascade Delete Related Records" and then clicked OK, I get the following error message:

    "Invalid field definitiion <<name of the second subtable's key>> in definition of index or relationship"

    There is currently one key in the second subform's table for every key in the master table, and that link seems to hold. I just can't get the second subform's table to produce a new record whenever a new master record is produced.

    If someone could help me out, I'd greatly appreciate it. I've got quite a bit of information I need to enter tonight.

    Thanks,
    Steve Weber

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Problems with 'Add Record' in Access 2000 Form

    Several questions here -
    It appears from what you indicate here that you have a one-to-one relationship between the master table and both of the other tables - is that true?
    If that's true, why do you have three tables? (If your master table already has a large number of fields, and you have some fields that aren't used very often, that might be a reason - but then do you want to insist on having an entry for each master record in the other two tables?)
    You say each table has an autonumbered long int as it's primary key? Are they separate fields, or is the autonumber field from the master table used to populate the subsidary tables?
    Why are you using subforms if that is the case - the subform will only ever show one record, and the overhead of a subform is significantly greater than having all data on one form - create a query that includes the fields from all three tables?

    In general, subforms do not add a record to their associated data source when you add a new master record - that would appear to be happening because of the referential integrity you set up for the second table. Hope this helps get the debugging process started.
    Wendell

  3. #3
    New Lounger
    Join Date
    Dec 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems with 'Add Record' in Access 2000 Form

    Perhaps it would help if I gave you a more detailed view of the scheme.

    My database (of attorneys) consists of primarily a [master form] table, and two [imbedded subform] detail tables. The first [imbedded subform] detail table is linked to the master table via a one-to-many relationship.

    The second [imbedded subform] detail table is linked to the master table via a one-to-one relationship.

    The master table of attorneys contains only four attributes <id>, <last name>, <first name> and <middle initial>. This table is keyed on the <id> attribute (defined as an autonumbered long integer).


    The additional credentials of the attorneys (Area of expertise, Year Law Degree Received, Date Hired, etc. . .) are all stored in the first [imbedded subform] detail table with basically three attributes, <id>, <credential> name> and <credential value>. Eg. 1234567, "Date Hired", "05/07/01"

    This table is also keyed on the <id> attribute (also defined as an autonumbered long integer). I set the database up in this manner because, while "most" of the current attorneys' credentials apply to all the attorneys, there will be new attorneys coming in with credentials that will only apply to them, and not to the others. The idea is to eliminate the need to add additional columns/attributes to the master table every time an attorney joins the firm with a unique credential that doesn't apply to the others. Now, adding a credential for a new attorney, (that none, or few, of the existing attorneys have), is as easy as inserting a new record into the credentials table, since the TYPE of credential is a data value and not part of the master table's structure.

    eg. Let's say an attorney holds a certificate in commercial litigation. The new row would look something like this: 1234567, "Certificate", "Commercial Litigation".

    Incidentally, since this table is imbedded as a subform on the master table, all I need to do is keep scrolling down in the subform to open up new rows that are all tied back to the master table record for that attorney.

    The third table is for the purpose of my keeping track of which attorneys have/have not had their photo taken (as each attorney will have a photo stored in JPG format, with a unique file name created by combining the attorney's <last name> with his/her <id>). I don't want to go messing around with the master table (especially since the database will be primarily accessed via .ASP pages on a web browser, and those .ASP pages depend fairly heavily on the existing field structure of the master table.)

    For this reason, I created a separate, third table containing two fields <id> (autonumbered . . .), and <photo needed> (of type YES/NO). As I mentioned, this table is linked to the master via a one-to-one type
    relationship. HERE IS WHERE THE PROBLEM COMES IN. Whenever I click the add new record[*] button on the master form, Access grays out this third table's subform from the master form (probably to indicate that it did not automatically create a new, linked record in this third table for that attorney.)

    "Question":

    How can I get Access 2000 to automatically create a new record in this third table whenever I create a new [master] attorney record by clicking on the add record button in the master form, so that I don't have to manually go into the third table, type in the <id> (on top of first having to go through the trouble of locating the key which the master table had assigned to that attorney) and then indicate whether that attorney's photo is needed? By the way, existing links (between the master and third table) are holding okay. I thought that the solution to the problem would be to turn on that update option under "Enforce Referential Integrity", but when I do that, Access displays the error message that I indicated in my original posting.

    Thanks,
    Steve Weber

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Problems with 'Add Record' in Access 2000 Form

    I don't have much time this morning, but here's two suggestions for solving your problem.
    1 - use a query to base your form on, and it will enforce the 1-to1 referential integrity as you add records.
    2 - don't try to store the picture in the database - instead, use a text field that contains a link to the photo file, and then put that field in the main table so you don't have the third table at all (that assumes you only want one photo for each attorney.

    Will post more details about why later today. Hope this helps.
    Wendell

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Problems with 'Add Record' in Access 2000 Form

    To continue with my post from this morning -
    Referential integrity in Access will only prevent you from deleting a record in a subsidiary table, but will not automatically add a record to a table. If you use a query as your data source, and you do appends using that data source, a record will be added in both tables. However you also need to be sure to not have the key column in the subsidary table not be a autonumber; just make it a long int. Then Access will fill it in at the time you populate the autonumber field in the main table. (I think you will need to include it in your query, but it's been long enough since I did one of those that I may be wrong - try it both ways and see what works and what doesn't.)

    The second solution seems easier all things considered. No query is required, and as long as you use a SQL statement in your ASP pages, they won't care if you've added a new field that they don't use. You are approaching the photo correctly - you definitely don't want to store it in the table. Even small JPG images, say 10K or so will cause the database to bloat by 1MB. So all you store is the file path to the image, and then set the form control dynamically to link to the appropriate image. In either case you don't need a second subform, as the form can be on the main form. Hope this fills in some of the gaps.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •