Form to Multiple Tables Issues

I have three tables and I want to use one form to enter data into all tables.
First table is actually going to be imported from a template excel file
Second table is going to be entered from the form manually
Third table is going to be a set of table with specific information based on
input from the first table and data is going to be entered from the form also
I set everything up and I have three tabs for each table

Note: all details from all tables are about complaints (primary key in first
table)

ISSUES:
1. After I import the complaints into the first table my form opens up but
it fails to fill the complaint number into the other tables
2. How do I set up my third tab for the third table to open the right
questions? I will probably have 5 types of complaints and this third tab will
ask specific questions for each type (so I guess my third table is a set of 5
tables)
3. Is there any way to link a button to an export path?

I will really appreciate some help on this...


Thank you
Alex

On Thu, 5 Nov 2009 16:36:02 -0800, Dinamo/Pistons
wrote:

I have three tables and I want to use one form to enter data into all tables.
First table is actually going to be imported from a template excel file
Second table is going to be entered from the form manually
Third table is going to be a set of table with specific information based on
input from the first table and data is going to be entered from the form also
I set everything up and I have three tabs for each table

Tab controls have NO relationship to tables. They just control how form
controls appear on the screen, allowing you to reused the same screen space
for different sets of controls.

Note: all details from all tables are about complaints (primary key in first
table)

ISSUES:
1. After I import the complaints into the first table my form opens up but
it fails to fill the complaint number into the other tables

It wouldn't, and you wouldn't want it to. What I'd recommend is putting two
Subforms (which could be on different tab pages, though it won't affect the
data whether they are or not). Use the Complaint Number as the Master and
Child Link Field of the subforms; this will cause the complaint number to be
inherited and put into the child tables when (but not before) any other field
on the subform is edited.


2. How do I set up my third tab for the third table to open the right
questions? I will probably have 5 types of complaints and this third tab will
ask specific questions for each type (so I guess my third table is a set of 5
tables)

WHOA!!! STOP. Step back.

If you think you need a *new table* for each question you're definitely off
track! You should have *one* table of questions with a field for the complaint
type; this table of questions would be related one-to-many to a table of
Answers, which would also be related one to many to the table of Complaints.

3. Is there any way to link a button to an export path?

ummm... yes. A command button can run VBA code and that code can specify an
export path... or pretty much anything you want.

Perhaps you should post a description of your current tables (tablename, key
field names and datatypes, relationships). The tables are the foundation of
your structure, and if the foundation is faulty the whole structure is at risk
of collapse!
--

John W. Vinson [MVP]

For issues #2 I need 5 tables for the 5 complaint types
Each complaint type has different prespecs and different questions
So for example if I have 100 complaints, 20 of each type I have to ask the
right questions
Though I will need to load the right subform when the complaint type is
loaded or selected (inputed)

Thanks
Alex

"John W. Vinson" wrote:

On Thu, 5 Nov 2009 16:36:02 -0800, Dinamo/Pistons
wrote:

I have three tables and I want to use one form to enter data into all tables.
First table is actually going to be imported from a template excel file
Second table is going to be entered from the form manually
Third table is going to be a set of table with specific information based on
input from the first table and data is going to be entered from the form also
I set everything up and I have three tabs for each table

Tab controls have NO relationship to tables. They just control how form
controls appear on the screen, allowing you to reused the same screen space
for different sets of controls.

Note: all details from all tables are about complaints (primary key in first
table)

ISSUES:
1. After I import the complaints into the first table my form opens up but
it fails to fill the complaint number into the other tables

It wouldn't, and you wouldn't want it to. What I'd recommend is putting two
Subforms (which could be on different tab pages, though it won't affect the
data whether they are or not). Use the Complaint Number as the Master and
Child Link Field of the subforms; this will cause the complaint number to be
inherited and put into the child tables when (but not before) any other field
on the subform is edited.


2. How do I set up my third tab for the third table to open the right
questions? I will probably have 5 types of complaints and this third tab will
ask specific questions for each type (so I guess my third table is a set of 5
tables)

WHOA!!! STOP. Step back.

If you think you need a *new table* for each question you're definitely off
track! You should have *one* table of questions with a field for the complaint
type; this table of questions would be related one-to-many to a table of
Answers, which would also be related one to many to the table of Complaints.

3. Is there any way to link a button to an export path?

ummm... yes. A command button can run VBA code and that code can specify an
export path... or pretty much anything you want.

Perhaps you should post a description of your current tables (tablename, key
field names and datatypes, relationships). The tables are the foundation of
your structure, and if the foundation is faulty the whole structure is at risk
of collapse!
--

John W. Vinson [MVP]
.

On Wed, 11 Nov 2009 17:33:01 -0800, Dinamo/Pistons
wrote:

For issues #2 I need 5 tables for the 5 complaint types

No, not if you properly design the tables.

Each complaint type has different prespecs and different questions
So for example if I have 100 complaints, 20 of each type I have to ask the
right questions

Certainly. But each question should be a separate *RECORD* in a table of
questions (with a foreign key to the five-row table of ComplaintTypes), not a
separate *FIELD* in a table.

Though I will need to load the right subform when the complaint type is
loaded or selected (inputed)

Again, no. *ONE* subform based on a query of the questions table will do very
nicely.

You are apparently having trouble designing your forms because your tables are
not properly normalized! Don't despair; this is a very common situation, and
there are better solutions than "spreadsheets".

For example, for an extremely flexible questioinnaire database, see Duane
Hookum's "At Your Survey":
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000'

or

Roger Carlson's Training Registration database:
http://www.rogersaccesslibrary.com/d...gistration.mdb

Thanks
Alex

"John W. Vinson" wrote:

On Thu, 5 Nov 2009 16:36:02 -0800, Dinamo/Pistons
wrote:

I have three tables and I want to use one form to enter data into all tables.
First table is actually going to be imported from a template excel file
Second table is going to be entered from the form manually
Third table is going to be a set of table with specific information based on
input from the first table and data is going to be entered from the form also
I set everything up and I have three tabs for each table

Tab controls have NO relationship to tables. They just control how form
controls appear on the screen, allowing you to reused the same screen space
for different sets of controls.

Note: all details from all tables are about complaints (primary key in first
table)

ISSUES:
1. After I import the complaints into the first table my form opens up but
it fails to fill the complaint number into the other tables

It wouldn't, and you wouldn't want it to. What I'd recommend is putting two
Subforms (which could be on different tab pages, though it won't affect the
data whether they are or not). Use the Complaint Number as the Master and
Child Link Field of the subforms; this will cause the complaint number to be
inherited and put into the child tables when (but not before) any other field
on the subform is edited.


2. How do I set up my third tab for the third table to open the right
questions? I will probably have 5 types of complaints and this third tab will
ask specific questions for each type (so I guess my third table is a set of 5
tables)

WHOA!!! STOP. Step back.

If you think you need a *new table* for each question you're definitely off
track! You should have *one* table of questions with a field for the complaint
type; this table of questions would be related one-to-many to a table of
Answers, which would also be related one to many to the table of Complaints.

3. Is there any way to link a button to an export path?

ummm... yes. A command button can run VBA code and that code can specify an
export path... or pretty much anything you want.

Perhaps you should post a description of your current tables (tablename, key
field names and datatypes, relationships). The tables are the foundation of
your structure, and if the foundation is faulty the whole structure is at risk
of collapse!
--

John W. Vinson [MVP]
.

--

John W. Vinson [MVP]