"join key of tblcars not in recordset"

[STaL1N]

This is the error message that is displayed in my form when i i try and add new details. The form runs of a query which runs of two tables which are related appropriatly. The whole point of the form being to add a company and attach a car to it. I can type in the company details fine but i cannot enter the car registration. Any help would be appreciated.

i thought it was that i had to add certain fields to the query but i am not certain.

more details can be found here

http://www.v-loaded.com/stal1n

 

[scriverb]

Look at the relationship between the two tables in your Relationships window. Make sure that you have included in your query all of the matching fields for this relationship. Especially the table tblCars as the error message indicates that the field necessary to link to that table is not available in the query.

Post back with the actual query SQL if you need more assistance.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[STaL1N]

SELECT tblCompany.CompanyName, tblCompany.ContactName, tblCompany.Address, tblCompany.PostCode, tblCompany.TelephoneNumber, tblCompany.DateLeftIn, tblCars.Registration, tblCars.CompanyID, tblCars.CarID, tblCompany.CompanyID
FROM tblCompany INNER JOIN tblCars ON tblCompany.CompanyID = tblCars.CompanyID;

i added every field from both those tables, with no joy

 

[scriverb]

What is the relationship between the tblCompany and the tblCars? One-to-one or One-to-Many? If one to one why not just have the fields from tblCars directly in your tblCompany. No real need to have them in a seperate table. The tblCars if being entered on the same form and tblCompany must have a way of having the Foreign Key entered . The FK of tblCars would be the PrimaryKey(PK) of tblCompany. At this time I suspect that is not happening.

Post back with comments.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[STaL1N]

the relationship between tblcompany and tblcars is a 1 to many relationship between a companyID primary key in tblCompany and another in tblCars as can be seen
here:

http://www.v-loaded.com/stal1n/relate.gif

 

[scriverb]

Well I am stumped on this one at this point. I have created two tables as you relationship screen described and joined them with the same type of join. Created a query with all fields from both tables. Then created a form with all fields from the queries.

Everything is working just fine. I can add data to a new record and the keys are updated appropriately and the record is saved.

I will keep thinking about this one but I don't have any answer just yet. I would take the step of creating a new test form with the query as the RecordSource and adding all fields to the form. This is just as a test to see if you original form is corrupted. Try adding records to this new form.

Post back with any comments or results of the suggestion.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]