Linked Tables

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Simon

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Simon,

I’ll admit I’m confused. You said [indent]

---

however the user that inputs the data into the first query/table does not have access to the data in the related table

---

[/indent]but now you’re saying that both tables are in the query.

Your statement [indent]

---

each user is able to look at bits of the others info

---

[/indent] seems to tie back to your original post, which I just reread. I skated right over your original statement that[indent]

---

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

---

[/indent]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?

Reply | Quote

Sorry for the confusion; this is what is really happening.

Users are 4000 miles apart, UK & USA, an item arrives in UK for repair, a record is created to track its progress thru a form. That form contains UK and USA info related thru a query. The USA bit will be empty at this time. The form has write permission for the UK bit and read permission for the USA bit.

Item goes to USA for repair, when it arrives User 2 inputs data into second form which has info on it (read only) sourced from the first table (UK info) thru a query. it shows itme type part number etc. The second table will not get any data until a record is created in the first

That is why I want to automatically create the record in the related table so when the unit arrives in the USA the record is available for input.

I have created a workaround along the lines I said earlier. I have created a numerical field in the related table, added it to the query for the first form, added it to the UK form with visible set to no, when a new record is added and unit type is entered into one of the fields, an on exit procedure adds a “1” to my new field thus creating the link and making the record available in the second table.

Thanks again – it may not be clean or elegant but it now works – perhaps when I get more experienced I will clean it up.

Simon

Reply | Quote

Hi Simon: I’ve read and re-read this thread many times and keep coming back to the same question. Perhaps I’m missing something but, why wouldn’t you simply drop a ComboBox on the second form linked to a unique key in the original table (part#,work order# or such). On selecting the proper ComboBox field a new record is “Then” created in the second table related to the first. I really can’t see why you feel you need to create the record in the UK. The US form would not be able to create a record unless related to an existing record in the UK table. That’s pretty standard procedure, I use this process every day relating Claims to Contracts. But again, maybe I’m missing something.

Reply | Quote

Brian, I agree with everything that you have said but my aim is to make the task as simple as possible. The database is located in UK – linked thru the intranet and is pretty slow in the US. I am also having a tough time to get this updated on a regular basis. I think to make life that bit easier if the form is ready to enter data. Do you see anything wrong in my thinking or execution of this?

Thanks,

Simon

Reply | Quote