Using linked tables in forms.

[DJ81]

You're about to witness a grown man cry.

Thanks to this forum I've managed to put together my database. I believe it's fairly good (for me at least. I'm sure you Access MVPs could whip something twice as good in half the time!).

Anyway. My table done, I thought I'd design a form. Easy peasy!
So I thought.
Nothing has brought me quite as close to killing myself.

Let me give you a small example of my problem.

Tables:

tblCharity
CharityID
CharityName
TitlesID
FirstName
LastName
PositionID

tblTitles
TitlesID
Title

tblPosition
PositionID
Position

OK. So my relationships are all set up. Everything should work. I go into designing my form and that's where it all goes wrong. I'm going to take it slow and explain step by step. I'm not doing this because I think you're all as thick as I am (that's impossible) but because I want to make sure I explain it properly because the Samaritans are just about fed up with listening to me.

- I create a blank form.
- I insert the fields CharityName, FirstName and LastName.
- I go into Form View and check it works: it does. I try and enter some text. It does. I don't create any records though.

- On the right hand side of my window I see the Field list. Under "Fields avaliable in related tables" I see tblTitles and tblPosition. I click the little plus sign next to them and am shown the fields that those tables contain.

- I drag over Title and then Position.
- I go into Form View to test.
- I can enter information into the Title & Position fields. I cannot enter anything into the CharityName, FirstName and LastName. If I try to do so I am given the error message: "Cannot add record(s); join key of table 'tblCharity' is not in recordset'.

- I cry.

Now I have found that by using a subform I can input all the data needed but I think the subform layout is awfully ugly for something as simple as Title and Position (especially a "Mr" and then "Chairman" or something like that).

Now I've actually got 12 tables, but basically I need to know how I can put fields from different tables onto one form.

I hope I've explained that well. If not I'll zip up the DB and post it if it's easier.

 

[Oldsoftboss]

It will be something simple, but probably too difficult to explain. Can you post a zip of the Db?
(If it is a linked Db, simply import the tables back into the front end)

Dave

 

[neileg]

I think there are two issues, here. I suspect you have set up lookup fields in your tables. These cause more trouble than they are worth.

Secondly, you should be basing your form on a query that joins your tables together into one recordset.

 

[DJ81]

Here is the zip.

It seems in my form testing I have created one or two records (oops!). I can fix that though. I created a simple form using the fields I wrote about in the post.

neileg - Oh, I didn't know that

By the way - I am running Access 2007.

 

[neileg]

Sorry, don't do A2007!

Build your query and then do the form. The fields you need will then be in the recordset.

 

[DJ81]

I did as you suggested, but I got the same message. I created a query with the fields I would need, then I created a from based on that query.

 

[neileg]

Can you post the SQL of the query.

 

[DJ81]

Can I thank you again for taking the time out to help. Unfortunately I'm the only one who can do this (well..."attempt" to do it) so I can't shove it on to someone else. Plus in a way I like learning about where I am going wrong. Books are great, and they're helping but nothing beats a bit of help from someone who is a bit better at it.

Anyway, to the SQL. I've not broken it up to make it easier to read. It's copied and pasted exactly.

SELECT tblCharity.CharityName, tblTitles.Title, tblCharity.FirstName, tblCharity.LastName, tblPosition.Position, tblAddress.StreetAddress, tblCity.City, tblCity.Postcode, tblCounty.County, tblNoContact.NoContact, tblReceived.Notes
FROM (tblTitles INNER JOIN ((tblAmount INNER JOIN tblReceived ON tblAmount.AmountID = tblReceived.AmountID) INNER JOIN (tblPosition INNER JOIN (tblNoContact INNER JOIN (tblClosing INNER JOIN (IssMth INNER JOIN tblCharity ON IssMth.IssMthID = tblCharity.IssMthID) ON tblClosing.ClosingID = tblCharity.ClosingID) ON tblNoContact.NoContactID = tblCharity.NoContactID) ON tblPosition.PositionID = tblCharity.PositionID) ON tblReceived.ReceivedID = tblCharity.ReceivedID) ON tblTitles.TitlesID = tblCharity.TitlesID) INNER JOIN (tblCounty INNER JOIN (tblCity INNER JOIN tblAddress ON tblCity.CityID = tblAddress.CityID) ON tblCounty.CountyID = tblCity.CountyID) ON tblCharity.CharityID = tblAddress.CharityID;

I hope that means more to you than it does to me.

Thanks.

PS - If that looks like a load of spagetti, then I could always try and save the database under the old access extension .mdb.

 

[DJ81]

OK, I managed to get it working when I created my query and included the various PKs and FKs. Here is the new SQL:

SELECT tblCharity.CharityID AS tblCharity_CharityID, tblCharity.CharityName, tblCharity.TitlesID AS tblCharity_TitlesID, tblCharity.FirstName, tblCharity.LastName, tblCharity.PositionID AS tblCharity_PositionID, tblCharity.ClosingID AS tblCharity_ClosingID, tblCharity.IssMthID AS tblCharity_IssMthID, tblCharity.NoContactID AS tblCharity_NoContactID, tblCharity.ReceivedID AS tblCharity_ReceivedID, tblPosition.PositionID AS tblPosition_PositionID, tblPosition.Position, tblReceived.ReceivedID AS tblReceived_ReceivedID, tblReceived.Notes, tblTitles.Title, tblTitles.TitlesID AS tblTitles_TitlesID, tblNoContact.NoContactID AS tblNoContact_NoContactID, tblNoContact.NoContact, tblCounty.CountyID AS tblCounty_CountyID, tblCounty.County, tblClosing.ClosingID AS tblClosing_ClosingID, tblClosing.ClosingText, tblCity.CityID AS tblCity_CityID, tblCity.City, tblCity.CountyID AS tblCity_CountyID, tblCity.Postcode, tblAddress.AddressID, tblAddress.CharityID AS tblAddress_CharityID, tblAddress.StreetAddress, tblAddress.CityID AS tblAddress_CityID, IssMth.IssMthID AS IssMth_IssMthID, IssMth.IssMthText
FROM (tblCounty INNER JOIN tblCity ON tblCounty.[CountyID] = tblCity.[CountyID]) INNER JOIN ((tblTitles INNER JOIN (tblReceived INNER JOIN (tblPosition INNER JOIN (tblNoContact INNER JOIN (tblClosing INNER JOIN (IssMth INNER JOIN tblCharity ON IssMth.[IssMthID] = tblCharity.[IssMthID]) ON tblClosing.[ClosingID] = tblCharity.[ClosingID]) ON tblNoContact.[NoContactID] = tblCharity.[NoContactID]) ON tblPosition.[PositionID] = tblCharity.[PositionID]) ON tblReceived.[ReceivedID] = tblCharity.[ReceivedID]) ON tblTitles.[TitlesID] = tblCharity.[TitlesID]) INNER JOIN tblAddress ON tblCharity.[CharityID] = tblAddress.[CharityID]) ON tblCity.[CityID] = tblAddress.[CityID];

Some questions:

1 - Has the inclusion of the PK's made the difference, or was it the FK's (I've not had time to fiddle around as yet)?

2 - Why wouldn't it work without either the PKs or FKs?

3 - Does that SQL look "efficient"? If not, how would you suggest I clean it up?

4 - Are there any ID fields that I can remove and still keep my form working?

Thanks.