If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Form to Multiple Tables Issues
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] |
#3
|
|||
|
|||
Form to Multiple Tables Issues
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] . |
#4
|
|||
|
|||
Form to Multiple Tables Issues
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] |
Thread Tools | |
Display Modes | |
|
|