Cant add record

[arage]

I designed 3 tables that have a 1-1 relation between all three. I created a select query that essentially dumps all 3 tables’ fields into the query. I can’t update the query and my form is utterly based on it. Form properties edit settings allow for additions & can’t think how else to fix problem. I faced it before but have forgotten how I cleared it. The form was originally based on the 3 tables rather than the query, but that gave same results. Any ideas, I’ve pored over below answer to me, but still nothing.

BarkerD
Member posted 01-08-2001 11:56 AM
--------------------------------------------------------------------------------
Applying or removing a filter should have no effect on adding a new record. You should check a couple of things.
1. Security Settings
2. Form properties - set to edit only
3. Recordsource of the form may not be updateable if based on multiple tables.

 

[KevinM]

Option 3 is causing you the problem.

You can't just join any tables together in a query and hope to add a record to it.

The relationships need to be re-enforced and you need to use to the correct fields in order to update a record.

hth

 

[Matthew Snook]

What is meant by reinforcing relationships? I've been working with some multiple table joins and this is of interest to me, too.

Matt

 

[KevinM]

In the Relationship window if you click on a join between tow tables you have the option of making it reinforced. This basically means that the linking field value in the 'Many' table MUST exist in the main table FIRST.
You can aslo tick the cascading deletes and updates so changes in the main table are reflected in th elinked table.

HTH

 

[arage]

I’m assuming ofcourse Kevin that when you say reinforcing relationships in the relationship window, you mean the Enforce Referential Integrity tick box in the edit relationships window that occurs between tables?

 

[arage]

Kevin,
Your explnation on the refer. Integ. Between multiple table takes into account if the the query is based on the tables, but what if the form is based on a query and not a bunch of tables you join together while in query builder. Or is your advice something that applies to queries as well, that they (the queries) ought to be based on tables w/ref. integ.?

 

[arage]

I decided to go thru my tables & add referential integrity in 1-many relationships between my tables & found an error in one table that I corrected. That however did not help the query my form is based on, it still won’t add a record.

 

[arage]

Ok, I solved my problem & now I can add a rec. I would appreciate opinions on what criteria my incorrect table design met as to prevent me from updating my query thru the user form. Basically the tables behind my query were 3. The link was 1-1-2 and I changed it to 1-1-1. & that allows me now to enter data thru the user form.

The way I was understanding was thus, take this example for instance. 1 table is linked to 2 tables by the same index in both. I would consider this a 1-1 relationship b/c each record in the main table links to just 1 record in each of the other tables. I wouldn’t consider this a 1-many relationship b/c the main table records don’t link to more 1 record in each of the other tables. But this was wrong obviously as my situation dictates.

 

[GregP]

I'm experiencing exactly this same problem, but I can't seem to get the query to be updateable even after following the instructions provided here. What else can be causing this?

I have a two-table query which I can't update, but I have a three-table (unenforced joins) query elsewhere in the database which works perfectly (in this case, the fields are daisy-chained together, and the common field which joins the first two tables is not included in the query, but it still works fine!). The field which links the two tables in my faulty query is included in the query, but regardless of whether I include both, either or neither of the fields in the join, the query remains unupdateable. I also included a hidden field to automatically synchronise the two fields (if I understood the CustID example correctly), but this didn't seem to help. Previously I had enforced referencial integrity on the fields in question, but also to no avail.

Everything works fine when there's only one table in the query - but as soon as I add the second table to the query, it won't allow me to add extra records, even if none of the fields in the second table are being included in the query - simply having the second table sitting there is enough to break it. This even happens with only one field in the whole query! tblLicenseIssue is the first table, tblInventory is the second table. The SQL statement in this last case is:

SELECT tblLicenseIssue.LicenseIssueID
FROM tblLicenseIssue, tblInventory;

Not really rocket science is it? So what's the problem? It's fine as soon as you remove tblInventory from the SQL statement.

I have to implement this in another database I'm having the same problem with, and would really appreciate any help with this.

Thanks!

Greg

 

[GregP]

Hi Pat,

Thanks once again for your help (I don't think this is the first time you've come to my rescue!) That worked fine. I do actually need fields from tblInventory, but I removed them from the example statement for simplicity's sake, to remove as many factors as possible which weren't affecting the problem. I've now added the extra fields in which I need.

Originally my joins were unenforced, but in this example I've included a primary key field in the join, and thus made it a one-to-many join. This would seem to imply that KevinM's original post had some merit, but as I mentioned I have a three-table query in which all tables are joined by unenforced joins, but this works perfectly, so I discounted this. What criteria could my three-table query be following to allow it to be updateable? I can post the db if this will help. Is "you must make explicit joins between tables in order for the query to be updatable" the golden rule, or are there other factors which control this? The SQL statement is:

SELECT tblInventory.EE_No, tblProjectBookings.CurrentlyAllocated, tblProjectBookings.SuperInitials, tblSupervisors.SuperLastName, tblSupervisors.SuperFirstName
FROM tblSupervisors RIGHT JOIN (tblInventory LEFT JOIN tblProjectBookings ON tblInventory.InventoryID = tblProjectBookings.InventoryID) ON tblSupervisors.SuperInitials = tblProjectBookings.SuperInitials
WHERE (((tblProjectBookings.CurrentlyAllocated)=False))
ORDER BY tblInventory.EE_No;

Hmm, maybe it's easier to post a sample db?

Thanks again for your help Pat, it's fixed my problem but I'd like to be certain I can avoid it next time around.

The only other issue I faced I've been able to get around (relational databases seem incredibly involved, which is why I'd like to know the 'golden rules' if there are any). The field EE_No is the asset number of our equipment, and the field tblInventoryID is the primary key in the inventory table. Not every piece of equipment has an asset number, so I can't use it as the primary key, so I've chosen an autonumber field which isn't used for anything else apart from being a unique primary key and ensuring the integrity of the database. But since this number isn't used for anything, no-one will ever see it or know what it is, but to be able to complete a new entry, you have to type in what this inventoryID is, as it's the field required for the join. There will only ever be one inventoryID for one EE_No, and vice versa, and the user will know and enter the EE_No, so the entry of the EE_No should be able to be translated to the inventoryID. But such is not the case - to be able to complete entry of a record, I have to explicity enter the inventoryID, even after the EE_No has been entered in the same record.

The fix was: tblInventory.EE_No is in the same table as tblInventory.InventoryID, which is the primary key for tblInventory (the 'one' part of the join). tblLicenseIssue.EE_No is in the same table as tblLicenseIssue.InventoryID (the 'many' part of the join). Originally I included tblLicenseIssue.EE_No in the query (same table as has the 'many' part of the join), but once I removed this and replaced it with tblInventory.EE_No (same table as the 'one' part of the join), the inventoryID fields are automatically populated with their correct values.

Hope this helps someone out there, but two questions remain:
1) what are the 'golden rules' for linking tables?
2) why did my three-table scenario work?

Greg

 

[Spira]

The reason why the third table approach works is becasue it can provide a link between two or more tables.
The third table allows you to enter fields that may be secondary or even foreign resultsing in one to many relationships.
With a two table database your data is restricted therefore getting the 1-1 relationships.
To make a system you have to have a minimum of 3 tables where at least one provides some sort of a lnk between the tables

 

[GregP]

Hi Pat,

Yes, thanks for what you said - I realised why my 2 table example didn't work, and it did what I wanted it to, but there had been a couple of earlier suggestions about how to fix it, so I was very interested to know whether the key is wholly and solely the join type, or whether some of the other factors are also required (e.g. Me.txtCustID3 = Me.txtCustID1; Me.txtCustID2 = Me.txtCustID1: only if multiple tables aren't joined (implied by KevinM)). What I was requesting was I guess dot points of things to do to make sure the recordset is updateable, including everything you need and discarding anecdotal stuff which doesn't actually make any difference. I've got my present example working now, but a condensed list may help someone for next time.

Greg

 

[GregP]

Great, thanks!

Greg

 

[GregP]

Now I really feel I'm going crazy. After what I thought was a success, I got to work this morning and found the changes I'd made to the database were all gone. The results I now have are different.

I have two tables: tblLicenseIssue and tblInventory. The purpose of this query is to add information to tblLicenseIssue, some of which relates to information in tblInventory. So tblLicenseIssue is where I'm wanting the new data to go. Yesterday when I typed in the EE_No into the tblInventory.EE_No, the query would add a new record to tblLicenseIssue, and tblInventory.InventoryID would be displayed. I could achieve the same by entering tblLicenseIssue.InventoryID (which is the many part of a one-to-many link with tblInventory.InventoryID). Now I have the situation that whenever I type a value into tblInventory.EE_No, rather than adding a record to tblLicenseIssue and extracting the appropriate information from tblInventory, it adds a new record to tblInventory, even when I type a EE_No which already exists in tblInventory. It will behave as it did yesterday if I type a number into tblLicenseIssue.InventoryID, but as previously described, this number wouldn't normally be shown anywhere, and is meaningless to the user.

Trying to follow the logic through, I can see why it's doing what it is, but I can't see how it could have done what it did yesterday. Is there something simple I've missed in the configuration somehow? How can I instruct Access to extract existing data when I enter a number into tblInventory.EE_No, rather than creating a new record in tblInventory?

Hope this hasn't confused completely!

Greg

 

[GregP]

Hi Pat,

Yes, as you suggested, my scenario was like scenario 3. Translating this into my situation, I can't really see how it worked the way I described. Every time I entered data into tblB.Datafld (tblInventory.EE_No), a new record would be added to tblB (tblInventory). Maybe I am insane???

My final implementation seems to work now - what you suggested in the examples works in the queries so long as I enter the tblA.PKBfld. Then, in the form, I've got two combo boxes which looks up two sets the required data (tblB.Datafld1 and tblB.Datafld2). The combo box queries take with them the tblA.PKBfld and store the corresponding values in the appropriate fields. I've also made use of the hidden fields in the BeforeUpdate property as you advised, to populate EENo with EE_No.

For anyone else's reference, here is the sample database Pat proposed. It helps to be able to see how the joins / updates work.

Thanks again!

Greg

 

[Banana]

Ok I'm in similar situation here now.

I have a form for adding new clients that is based on a query of three tables. As per Pat's suggestion for enabling multiples entities (of which client is one of those) to hold multiple address, client table is based on the entity table and address table is related to client table:

tblEntity
EntityID
*attributes relating to all entities*

tblClient
EntityID (FK to tblEntity; 1-1 relationship)
*attributes relating to all clients*

tblAddress
EntityID (FK to tblEntity; 1-M relationship)

Because the Client and Address aren't directly related, does that render the query nonupdatable?

What can I do to allow someone to enter a Client record with an address on single form?

Thanks

 

[Banana]

I should add that before a Client record can be created, Entity record must be created first in order for Client record to relate.

However, Access doesn't seem to recongize EntityID as a PK and propagate the keys, even though I included tblEntity.EntityID in the form's recordsource query, while specifying that both Client and Address must have have same EntityID. Because of this, query is nonupdatable since there is no PK to exist.

Does that mean I have to write VBA code to insert in a Entity record in order to allow users to create a new Client Record? (I would prefer to keep tblEntity mroe or less transparent to the users as it is more for Access's benefit than for user's benefit).

Thanks.

 

[Banana]

Played with my queries a bit. I could create two separate updatable queries that joins Entity table with Address and Contact tables respectively. Howevering joining those queries together only allows for partial updates (meaning I can update only one table's fields, but not the other tables depending on how I define the joins), regardless how I join the tables together.

Any suggestions?

 

[Banana]

#1, Yes; I figured that out.

#2 is exactly what I did with my first two queries; query 1 is Entity-Client and 2 is Entity-Address. Both are updatable.

But the thing is to get those two queries joined together to update three tables from single form. Doing so only allows for partial updates...

BTW, Address is a 1-m relationship.

 

[Banana]

Partial updates as in I can enter data only for Entity table and Client table but not for Address table. That is even if I use the two beforementioned queries toghether, which are fully updatable independently but not when joined together.

First Query

SELECT tblEntityRecord.EntityRecordID, tblEntityRecord.EntityID, tblClients.LastName, tblClients.FirstName, tblClients.SSN, tblClients.DOB, tblClients.CategoryID, tblClients.GenderID, tblClients.EthnicityID, tblClients.IntakeDate
FROM tblEntityRecord LEFT JOIN tblClients ON tblEntityRecord.EntityRecordID = tblClients.EntityRecordID;

Second Query

SELECT tblEntityRecord.*, tblAddress.StreetAddress, tblAddress.City, tblAddress.State, tblAddress.ZipCode, tblAddress.CountyID, tblAddress.MailingAddress
FROM tblEntityRecord LEFT JOIN tblAddress ON tblEntityRecord.EntityRecordID = tblAddress.EntityRecordID;

Third query to join two queries together

SELECT qtrClientEntryA.*, qtrClientEntryB.StreetAddress, qtrClientEntryB.City, qtrClientEntryB.State, qtrClientEntryB.ZipCode, qtrClientEntryB.CountyID, qtrClientEntryB.MailingAddress
FROM qtrClientEntryA INNER JOIN qtrClientEntryB ON qtrClientEntryA.EntityRecordID = qtrClientEntryB.EntityRecordID;

I left the join for last query unspecified, as no matter how I specify the join, it's either totally nonupdatable or partially updatable. If I join A to B, I can update A but not B, and if I join B to A, I can update B but not A.