CODEWITHSUNDEEP
databaseformsms-access
user6716575
user6716575

Reputation: 21

MS Access form to update multiple tables without subforms

I am quite new to creating dbs in Access but I am not a fan of subforms, I can already tell that much, they are nice for one to many relationships (one customer, many orders) but when I just want to reduce redundancy and create a one to one relationship between tables and I only at all times would need one record from another table, subforms no longer feel so nice.

My example:

I have 2 tables, 1 for companies (ID, company name, country ID) and one for countries (country ID, name of the country). To eliminate repetition of country names I use country ID to link the 2 tables, and only add the ID of the country, not its name.

When I create a form for the "companies" table I want one field that says where that company is located instead of a subform (because I still havent figured out how to hide the box around it), so practically having one field that's connected to a different table.

And then through the one to one connection, when a record is selected the form would show in one field where the company is located. Is this possible?

Extra: I have 2 countries in my "country" table, the UK and Germany, but a new company I am adding a record from this form, is located in France, is it possible that I just enter France into the field and it automatically creates a new record in the "country" table for France, and also adds the new record's ID to the "company" table, to the company that's located in France

Upvotes: 2

Views: 10866

Answers (2)

Jon
Jon

Reputation: 56

It seems your form and subform are based directly on tables. Try basing your main form on a query:

SELECT
    company.ID,
    company.CompanyName,
    company.CountryID,
    country.CountryName
FROM
    company
    INNER JOIN country ON
        company.CountryID = country.CountryID

This will eliminate the need for a subform.

Extra: You may actually want to have the 'country creation' function on a separate form, or invoked from a button push on this main form.

Having Country IDs to force users to pick a standard country from a list is a very good idea. However if you also let them arbitrarily enter new ones in the same box you're trying to prevent them from mistyping in, that good work can come undone.

Upvotes: 2

Michael
Michael

Reputation: 3229

Yes this is absolutely possible. The way I do this is with unbound forms, so I control all of the SQL statements to tell the data what to do and where to go.

Since you have 3 fields being sent into a row in the "companies" table and 2 fields being sent into a row in the countries table with 1 of the fields being in a relationship (I'm assuming 1 to many), create 4 textboxes (or whatever other control) on your form. The controls will be for: ID (assuming this is inputted by the user and not an autonumber. If it is, please comment below), company name, country ID, name of country. When you fill all of them out, have a button with a Click event. In here you will have 2 SQL statements to insert records, 1 for each table.

The code will look like this:

Private Sub button_Click()

DoCmd.RunSQL "INSERT INTO companies VALUES ([ID].Value, [company name].Value, [country ID].Value)"
' The words in the [] are the names of your controls on the form
DoCmd.RunSQL "INSERT INTO country VALUES ([country ID].value, [country name].value)"

End Sub

Upvotes: 1

Related Questions