Cannot add records to database form

[Account Tech-Safety]

This is my first database. I have 3 tables which only duplicate one field which I am using to create my relationships. I have made sure that the data types are the same in the connecting fields. I used a query to make my form to pull all the fields from all three tables. I get the following error message when trying to add data to my form: "Cannot add records; join key of tblMain is not in recordset. I am using #3 of the join properties "Include all records from TblMain and only those records from Tbl Categories where fields are equal." I have checked referential int. and the two small tables show One-to-many to the large table.
The first field on my form is "AccidentID" a text field, and it is only in the tblMain. TblMain is related by fields: SiteID on one table, and CategoryID on the other table.

 

[jwindon]

I don't think I'm getting a clear picture of your tables. What are their names. What field is common in each of them. What kind of joins and where are they in your query.

I'm not sure, but I think you have to have the common key relating in your query between all three tables for the recordset to be updateable.

 

[R. Hicks]

Access is telling you that the Foreign Key is not included in the recordset (query). You will need to include it to make the recordset updateable. This is the field in the secondary table that is joined to the primary table (Primary Key) with a join line.

HTH
RDH

 

[Account Tech-Safety]

Thanks for the 2 replies. I do believe the probelm is in the join line, but do not know which type of join to use and how to create it. My tables have the following fields:
Tbl Main
AccidentID - Primary Key (This is the field that seems to have the problem)
School Year, CategoryID,SiteID,DateInjured,LName,FName,Age,Grade,InjuryType,BodyPart,Comments
TBLSite:
SiteName, SiteID - Primary Key and connects to TBLMain, Year Round,GradeLevel
TBLCategories:
CategoryID - Primary Key and connects to TBL Main, Category Name

I do not have the Accident ID in either of the other two Tables. I did try adding them, but it still did not work. I have not put joins in yet. Thanks for any help

 

[Jack Cowley]

Lets use tblMain and tblSite for this example. Add a field to tblSite called AccidentID. In the Relationship window drag AccidentID from tblMain to AccidentID in tblSite. Access will create a One to Many join for you. Click the Referential Integrity check box.

I would strongly suggest that you set AccidentID in tblMain to an AutoNumber and make it your Primary Key. In tblSite set the AccidentID Data Type to Number but do not make it the Primary Key.

[This message has been edited by Jack Cowley (edited 08-26-2001).]

 

[jwindon]

So you are wanting to pull information from all 3 tables into your query.

Guessing here:

You are trying to view the actual Name of the Category and the actual Site Name from your two lookup tables.

AccidentID should be a primary key in tblMain.
SiteID should be a primary key in tblSite.
CategoryID should be a primary key in tblCategories

When you create the lookups in tblMain, you should be including both the ID and the Name part of your subtables and binding the value to the ID part, hiding key so that only the category name or site name shows.

In your query, you should have EQUAL joins (choice 1) between

tblMain - SiteId and tblSites - SiteID
tblMain - CategoryID and tblCategories - CategoryID

You can select to show fields below:

AccidentID from tblMain
SiteName from tblSites
CategoryName from tblCaterogories

The record set is updateable.


I changed the joins around a bit and still had no problems. Even with ref ing set between SiteID and CatId to tblMain.

 

[jwindon]

Jack:

I see from AccountTech's orginal post that he has the primary keys set to the right fields. I agree that AccidentID should be an autonumber.

Joins are definetly his problem, but I think maybe he didn't bind the correct value in his lookups from the tblMain and that he is trying to join Name to Name instead of ID to ID if he did save the number value.

AccountTech: Please let us know if you get your joins together.

 

[Pat Hartman]

DO NOT add AccidentId to the site and category tables. It does not belong there. Your table definitions are correct as they stand.

You want to do an inner join between tblMain and tblSite on SiteID and another inner join between tblMain and tblCategory on CategoryId. SiteId and CategoryId should both be required in tblMain and you should define the relationships between each of these tables and tblMain in the relationship window and enforce referential integrity. You do not want to select cascade update or cascade delete since both of these tables fall into the "Lookup" category.

When building the query, you need to select the CategoryId and SiteId columns from tblMain (which is the "many" side of the relationship) rather than from their native tables (which are considered the "one" side of the relationship). This should make the quer updateable.

 

[Jack Cowley]

Embarrassed I am as I blew it again, big time. Guess I will have to start reading a lot more carefully.... Glad somebody is reading over my shoulder, but they shouldn't have to....

[This message has been edited by Jack Cowley (edited 08-26-2001).]

 

[Account Tech-Safety]

Toat Hartman and others: I worked out my problem with the adding of records to my database. The things Pat Hartman said to do worked, and I finally worked out my problem with the macro, which was the final glitch. I am very happy with this web site. I will read others questions and learn from them. Thanks again.

 

[snowlep]

I have a similar problem. I can add new records as long as the previous record is not set to read only (closed for editing). I am not sure why but when I make the last new record read only I cannot add new records.

Does anyone know why or have suggestions?

Thanks.