Input data into multiple tables using a single form - MS access 2013

[The Odyssey]

I have 5 tables that I would like to input data in. It can only be done with a single form. The fields I want to input in have the same names in all 5 tables, for example:

Table 1:
Name
Age
DOB

Table 2:
Name
Age
DOB

Table 3:
Name
Age
DOB

Table 4:
Name
Age
DOB

Table 5:
Name
Age
DOB

Is it possible to input data into all of these fields in each table using one textbox for each field?

Preferably without having to use code but if it cannot be done without it then that would be fine.

 

[jdraw]

Your database structure is not appropriate.
Can you please tell readers what this data base is for---it's purpose?
Can you show us some sample records for the proposed database?

You may wish to work through this tutorial which shows basic concepts, table design etc.

Good luck

 

[CJ_London]

Preferably without having to use code

hmm - pen and ink is a possibility

So you have one form, with three controls to update 5 different tables with the same data.

Sounds like bad database design to me, why not add the data once and then reference the table when required? Then you wouldn't need any code and you save yourself an awful lot of hassle when you want to change a value or delete a record.

Please explain the reason for needing to do this, it could have an impact on recommendations

 

[The Odyssey]

Hi

Thanks for the replies.

The database holds five tables in total. The tables are

Completions £
Completions Units
New Business £
New Business Units
Abortives £

The tables share the same fields

Name
Branch
Year

Then the tables each have their own data fields which is specific to the type of table. (ie Completions_£_Actual or Completion_£_Target) which relate to the Completion £'s table or (Completion_U_Actual) which relates to Completion Units table.... etc

When a user adds a new name to the database they will hopefully insert the persons Name Branch and Year into a form, hit submit and it will place them into all five tables for other users to be able to enter data specific to the tables.


I hope this makes sense?

 

[CJ_London]

Sounds to me like you should have one table with

TblNames
NamesPK autonumber
TName text
TBranch text
TYear number

and the other tables would have a single field with the primary key in it

OK, you might still want to create blank records in your 5 tables with just the link to the NamesPK in it, or the users can simply select it from a list of the records in tblNames which are not in the table being updated

Note that Name and Year are reserved words so should not be used as field or table names

 

[The Odyssey]

Just to add a bit more info as to what I am trying to achieve.

For clarification the shared fields are:

Negotiator
Branch
Year_

Each of the 5 tables require their own unique data as different data will be entered into each one. I have gotten pretty far using this design and It may be too far for a complete back end overhaul. The problem arose when displaying records from multiple tables, it works but only if the tables are linked by name (Negotiator).

This means that for the records to display in the report there has to be a record with the same name in each of the 5 tables. So by adding records into all 5 records at once it would make things much simpler, even if all the data except the name, branch and year is a default 0.

Any help on achieving this would be greatly appreciated.

 

[CJ_London]

you are not really taking on board the points being made so the code to do what you want is as follows, but please do not expect much support for all the issues you will get later on, they will only recommend what has already been suggested

assuming your form has a 'primary' table as the recordsource - I'll assume it is Completions £

This table will automatically update when you move to the next record, so append to the other tables your code will be something like

currentdb.execute "INSERT INTO [Completions Units] (Negotiator,Branch, Year_) Values('" & me.Negotiator & "', '" & me.Branch & "', " & Year_ & ")", dbfailonerror
currentdb.execute "INSERT INTO [New Business £] (Negotiator,Branch, Year_) Values('" & me.Negotiator & "', '" & me.Branch & "', " & Year_ & ")", dbfailonerror
currentdb.execute "INSERT INTO [New Business Units] (Negotiator,Branch, Year_) Values('" & me.Negotiator & "', '" & me.Branch & "', " & Year_ & ")", dbfailonerror
currentdb.execute "INSERT INTO [Abortives £] (Negotiator,Branch, Year_) Values('" & me.Negotiator & "', '" & me.Branch & "', " & Year_ & ")", dbfailonerror

which needs to go in your form beforeupdate event

Note also

 

[The Odyssey]

I understand what you are saying it just may be too late for a complete overhaul and it would be easier if inputting into 5 tables at once were possible.

If more problems arise from the database's design in the future and time permitted I will make the changes you suggested.

Thanks for the help