Problem creating a form from multiple tables (1 Viewer)

[KirRoyale]

I am trying to create an input form for input to tables.
There is a main ‘TblContacts’ table with fields such as: ‘LastName’, ‘FirstName’, ‘Address’, etc. There are also ID fields for the Company and Country and joins to the ‘Company’ and ‘Country’ tables, each with just ID and name fields.
I want to have all the fields from the ‘TblContacts’ table and the ‘Name’ fields from the ‘Company’ and ‘Country’ tables on the form. I want to be able to select the ‘Name’ fields from the ‘Company’ and ‘Country’ tables from combo boxes and for these also to populate the ‘Company’ and ‘Country’ ID fields (from the ‘TblContacts’ table).
To create the form, I selected ‘Create’ – ‘Form’ whilst the ‘TblContacts’ was open.
Then, I added the combo boxes pulling the names from the ‘Company’ and ‘Country’ tables.
My problem is that the selection of the ‘Company’ and ‘Country’ names above is NOT populating the ‘Company’ and ‘Country’ ID fields on the form (taken from the ‘TblContacts’ table. I have checked in ‘relationships’ that the joins have been created.
I was able to achieve something similar before on a different form from other tables and cannot see what’s different.
Could somebody please let me know where I’m going wrong?

 

[KirRoyale]

In addition, if I select a value for the ‘Company’ field for e.g. record 2, it overwrites the entries for records already input.

 

[eirman]

It's always best to base a form on a query rather that a table ... even if you are only using one table.

Assuming that your relationships are set up ...create a blank query. This will be SELECT by default which is what you want. Add the tables that you want, then drag the fields that you need from the tables downwards. You can then set the sort order. Save the query,

Create your form and set the query as the record source, then add your fields.

 

[CJ_London]

It looks like you need two forms - a main form and a sub form

The recordsource for your main form would be Companies and the recordsource for your subform would be contacts.

Typically your main form will be single view and your subform will be either continious or datasheet. Use the linkchild and linkmaster properties of your subform control to link the two datasets together - looks like it will be the companyID

 

[missinglinq]

What CJ said! While I agree with eirman about using a single Table Query, as a Record Source, Multi-Table Queries are frequently Read-Only; for the reasons why, have a look at Allen Browne's excellent article:

Why is my query read-only?

Linq ;0)>

 

[CJ_London]

Multi-Table Queries are frequently Read-Only

To get round this, changing the recordset type to indeterminate updates can sometimes work if there is not another reason for readonly

 

[KirRoyale]

Thank you all for your help and comments. I have tried the 2 methods as follows:

Creating form from query
I created a query with the ‘Contacts’ table as well as the joined ‘Company’ and ‘Country’ tables. Then I created a form from the query. The form input updates the query and the company table.
For new records, I can type in the Country or Company ID and the Company and Country names appear.
Changing the Recordset type to ‘Dynaset (Inconsistent Updates)’ worked and I was able to add / amend records in the Country or Company forms and tables.
However:
· There is no lookup so I cannot know which ID number relates to each Country or Company name.
· I cannot add / change records by entering the Country or Company name field.

Creating form and sub form from tables
I created a form from the Contacts table and a subform from the Company table.
The subform shows the Company ID and Company Name that correspond to the Company ID shown on the main form.
By typing in the Company ID number field, saving and moving to a different record and back, the ID and Name shown in the subform have updated (for new or existing records alike).
However, the same problems exist as with the method of creating the form from the query:
· There is no lookup so I cannot know which ID number relates to each Country or Company name.
· I cannot add / change records by entering the Country or Company name field.
as well as the additional problem that I cannot add new Country or Company records in this form.
Could somebody please help further?

 

[eirman]

At this point I would recommend that you post a sample database with all personal info removed and some sample data entered.

We will then have a much better chance of understanding and resolving your problem.