Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Linked Tables

  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked Tables

    My secure database has related tables. One table is accessed thru a form and data input by one group. The second table shows related data input thru a second form by a second group. When a new record is added on the first form the second related table is not showing it exists. How is the second table updated automatically to show the new record.

    I have looked at all the permissions for the forms / queries / tables and all seem to be set correctly.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked Tables

    Relating tables only means that a unique key in one table may be in a foreign key field in the other table. it does not mean that a new record will automatically be inserted in to the second table. You'll have to explain more about what you're doing to get a possible solution to your problem, but the existence of a record in table1 would only be visible to table2 through the use of something like a combobox on the table2 form that uses table1 as its rowsource.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables

    Charlotte, thanks for the reply. My database is structured so two different departments can enter data in the progress of a unit thru its repair cycle. The two depts are remote from each other and enter very different data. When the unit comes in it is logged into the datbase using the first form. It is then shipped to the other dept who uses the second form to record its progress. Info for each dept is kept in two separate tables linked by the primary and foreign key. What I want is, when the unit comes in and info first entered into the form I would like the second table to recognise a new record exists and automatically create a record for data entry. I am sure during the development that I was able to do this. Currently new records are created in the first table but no new ones are created in the second table. I hope I have explained myself

    Simon

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked Tables

    It isn't usually a good idea to create empty records so that someone can fill them in. It's better practice to allow them to create the record when they need to fill it in.

    However, it is usually possible to accomplish this by inserting the new records into a query rather than directly into a table. The query should have both tables in it with an outer join between the PK on table1 and the FK (PK if this is a one-to-one relationship) field on table2, and you must enforce referential integrity with at least cascading updates between table1 and table2. You'll need to have all the fields for both tables in the query if this is Access 2000 (Access 97 behaved a bit differently). If you insert a record into table1, a new record should be created automatically in table2 with the table1 PK inserted.

    This is off the top of my head, so you may run into a few hiccups. I don't have anything at hand that does this right now, although I've used this technique in the past and it should work for you.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables

    Charlotte, My apologies the forms are based on queries of tables. I have the one to one relationship setup with enforce referential and cascade. The database is at work so I do not have access to it today will look again tomorrow. I am using Access 97 at work.

    Thanks again

    Simon

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables

    Its hair pulling time!!! I have done everything suggested but still it refuses to create the record in the second table. Where could I be going wrong?

    Thanks,

    Simon

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked Tables

    Post the SQL for the query and explain how you're inserting the record (i.e., through a form, running it from the query grid, running it from code, etc.). Oh, and if there are any required fields in the second table, you need to tell us what they are and the datatypes they contain.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables

    Charlotte, attached is the SQL as requested. Things to note. It is a split database with data entry thru forms based on queries in the front end. To troubleshoot I have copied the queries to the back end and entered the data directly into the query, the results are the same. The main table is Tbl_RCUK_repair data with the primary key set to the auto number. The secondary (linked) table is Tbl RCGS repair data with the primary key set to long integer.

    The table relationship is set as one to one with referential integrity and cascade update & delete set.

    The database has been secured but logging on as the administrator has no effect.

    I annoying thing is I am sure that this has worked in the
    past. I would like this facility to make data entry as simple as possible for the RCGS people.

    Thanks for your help

    Simon
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked Tables

    Try using an outer join from your parent table to your child table. The parent table should be the one that enforces referential integrity to the child table. An inner join only gives you records that already exist in both tables, when what you need is to insert a record in the main table and have it pass its key into the second table. I'm not sure what your second query is doing, since it seems to be going the other direction. Depending on whether you have any required fields in the child table, you may need to actually enter a value into another field in order to get the record to "take" in that table.
    Charlotte

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables

    Thanks, I have tried all types of join and tried entering values into other fields - still no good.

    Simon

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked Tables

    If you open the select query and click the new record button, then type something into a field in your main table, it creates a new autonumber key, right? Then if you move to the fields in the child table and type something in a field, it should insert the autonumber from the parent record into the child record, assuming you have all the necessary/required fields from both tables (Access 97) or all the fields from both tables (Access 2000). If it doesn't, can you copy the key and paste it into the field in the child table? If so, then the query structure is your problem.

    One trick that may be necessary is to insert a default record in the main table. Use an append query to add a record with an autonumber of 0 and something like "(undetermined)" in the identifying field. Then set the default value of the key field in the child table to 0. That way, when you try to create a new record in the child table, the default value of zero has a valid parent record to relate to.
    Charlotte

  12. #12
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables

    If you open the select query and click the new record button, then type something into a field in your main table, it creates a new autonumber key, right?

    Yes

    Then if you move to the fields in the child table and type something in a field, it should insert the autonumber from the parent record into the child record, assuming you have all the necessary/required fields from both tables (Access 97) or all the fields from both tables (Access 2000).

    This works a treat, (when I did it as an administrator) however the user that inputs the data into the first query/table does not have access to the data in the related table (by design (bad design???)). Is there a workaround to create an event procedure to automatically take the autonumber from query 1 and enter it into the related field? Or place an entry into a dummy field to create the link?

    Simon

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked Tables

    You're going to have to create your own workaround for that. If the user doesn't have access to the necessary table, there isn't any way to have this work automatically.

    Links between tables can be implied, but not enforcing referential integrity so that the second table inherits a key. At least, not that I know of. In fact, unless you have both tables in the query, this won't work at all, although referential integrity will at least keep you from inserting non-existent keys into table2.

    You would have to write code to insert a record into the second table using the primary key/autonumber returned from the new record in table1.
    Charlotte

  14. #14
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables

    Charlotte, thanks for sticking with this but I think I am starting to lose where you are. Both tables are in the query as each user is able to look at bits of the others info. So if I create a field in the related table, put that field into the first query and use it to hold data (say "1"). Can you suggest code that, as soon as a new record is created, and data is entered into it, the code puts the "1" into this field, thus relating the two records. This field need not be viewed by anybody?

    Simon

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked Tables

    Simon,

    I'll admit I'm confused. You said
    <hr>however the user that inputs the data into the first query/table does not have access to the data in the related table<hr>
    but now you're saying that both tables are in the query.

    Your statement
    <hr>each user is able to look at bits of the others info<hr>
    seems to tie back to your original post, which I just reread. I skated right over your original statement that
    <hr>One table is accessed thru a form and data input by one group. The second table shows related data input thru a second form by a second group<hr>
    Are your users entering new records into the first table without any connection to the related table while at the same time other users are entering data into the second table? If so, what is the purpose of stratifying the data like this?
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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