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
|
|||
|
|||
Main Form with Subforms
I am creating a form with the main form at the top - data comes from a
specific query within the database so that I am able to sort and place filters within the query to get the data I want, the main record source is Project ID.. The first subform is side by side with the second subform. The 1st has data that comes from a table within the database, and the child link key is the project number that links to the master project ID. The second subform again comes from a different table within the database, the child is project Number, to Master of Project ID. The idea is the main form is a continuous form, the two subs are single and data entry capable. What I want this to do, but seem unable to do is to be able to click on a record in the main form and have it display in the two subforms...but for some reason this is not doing what I need it to do. Please, Please...does anyone have any suggestions? |
#2
|
|||
|
|||
Main Form with Subforms
How are your tables and queries related? Are you saying project ID is the
primary key in the main table and the others use project number? Are the project numbers the same as the project id? Do you have relationships defined on these? Do you think it will be a one-to many with the mainform being the one side and subforms being the many side? Since you want two subforms, is there data in one table and one query that both relate to the main table? I am asking all these questions because if all your relationships and keys were set properly, it would probably work. -- Milton Purdy ACCESS State of Arkansas "SaM" wrote: I am creating a form with the main form at the top - data comes from a specific query within the database so that I am able to sort and place filters within the query to get the data I want, the main record source is Project ID.. The first subform is side by side with the second subform. The 1st has data that comes from a table within the database, and the child link key is the project number that links to the master project ID. The second subform again comes from a different table within the database, the child is project Number, to Master of Project ID. The idea is the main form is a continuous form, the two subs are single and data entry capable. What I want this to do, but seem unable to do is to be able to click on a record in the main form and have it display in the two subforms...but for some reason this is not doing what I need it to do. Please, Please...does anyone have any suggestions? |
#3
|
|||
|
|||
Main Form with Subforms
Milton,
Thanks for responding first of all. Project ID is primary, which is in the query used in the Main form, and Project Number (used from two different tables in same database) used in Subforms, Project Number being the same as Project ID. I don't know why they named it differently in that query, but...regardless. They are one and the same. I guess maybe I don't have relationships defined - I thought that by selecting the one to be master (in properties in each form) and the one as child, this would define the relationship. Is this not correct? To be perfectly honest, all the information in the Query pertains to all the information in the tables - that is to say they all relate in some form or another. Project ID, Project Number, Project Description...these are in both the query and both tables. The main form lists several (continuous) projects, when you click on one, it would display (supposedly) in both subforms. The first subform would have more details about the project, the second subform I can enter in approved dollar amounts or take away funds from the project. The second form has less info. -- Susan M. Project Controls Data Coordinator "golfinray" wrote: How are your tables and queries related? Are you saying project ID is the primary key in the main table and the others use project number? Are the project numbers the same as the project id? Do you have relationships defined on these? Do you think it will be a one-to many with the mainform being the one side and subforms being the many side? Since you want two subforms, is there data in one table and one query that both relate to the main table? I am asking all these questions because if all your relationships and keys were set properly, it would probably work. -- Milton Purdy ACCESS State of Arkansas "SaM" wrote: I am creating a form with the main form at the top - data comes from a specific query within the database so that I am able to sort and place filters within the query to get the data I want, the main record source is Project ID.. The first subform is side by side with the second subform. The 1st has data that comes from a table within the database, and the child link key is the project number that links to the master project ID. The second subform again comes from a different table within the database, the child is project Number, to Master of Project ID. The idea is the main form is a continuous form, the two subs are single and data entry capable. What I want this to do, but seem unable to do is to be able to click on a record in the main form and have it display in the two subforms...but for some reason this is not doing what I need it to do. Please, Please...does anyone have any suggestions? |
#4
|
|||
|
|||
Main Form with Subforms
I thought it would be easiest if I displayed fields in the tables and query:
Query1 – Main Form Project ID Project Description Project Type Original Plan RLT Approved YTD Current Plan Current Forecast Table1 – Subform1 Project ID Project Description Project Type Project Status Venture Initiative YR_2010 YR_2011 YR_2012 YR_2013 YR_2014 Notes Project_MGR_NM Owner_NM Sponsor_NM YR_2015 YR_2016 Table2 - Subform2 Project Number Description Project Type 2010 Notes RLT Approved Change Approved Date Will that help any? -- Susan M. Project Controls Data Coordinator "golfinray" wrote: How are your tables and queries related? Are you saying project ID is the primary key in the main table and the others use project number? Are the project numbers the same as the project id? Do you have relationships defined on these? Do you think it will be a one-to many with the mainform being the one side and subforms being the many side? Since you want two subforms, is there data in one table and one query that both relate to the main table? I am asking all these questions because if all your relationships and keys were set properly, it would probably work. -- Milton Purdy ACCESS State of Arkansas "SaM" wrote: I am creating a form with the main form at the top - data comes from a specific query within the database so that I am able to sort and place filters within the query to get the data I want, the main record source is Project ID.. The first subform is side by side with the second subform. The 1st has data that comes from a table within the database, and the child link key is the project number that links to the master project ID. The second subform again comes from a different table within the database, the child is project Number, to Master of Project ID. The idea is the main form is a continuous form, the two subs are single and data entry capable. What I want this to do, but seem unable to do is to be able to click on a record in the main form and have it display in the two subforms...but for some reason this is not doing what I need it to do. Please, Please...does anyone have any suggestions? |
#5
|
|||
|
|||
Main Form with Subforms
On Tue, 9 Mar 2010 13:54:01 -0800, SaM wrote:
Comments and concerns inline. I thought it would be easiest if I displayed fields in the tables and query: Query1 – Main Form Project ID Project Description Project Type Original Plan RLT Approved YTD If this is a Year To Date sum of (something) then the field should simply NOT EXIST. Instead it should be calculated on the fly in a Query. Current Plan Current Forecast Table1 – Subform1 I'm confused. What is the relationship between Query1 and Table1? Tables are *fundamental* - queries are based on Tables, Forms are based on Queries. I worry that you might be building your house upside down...! Project ID Project Description Project Type Project Status Venture Initiative YR_2010 YR_2011 YR_2012 YR_2013 YR_2014 "Fields are expensive, records are cheap". What will you do in 2015? Redesign all your tables, all your forms, all your queries, all your reports? go out of business?? If each Project has multiple values for different years, then you should - must, I'd say! - have *two tables* in a one to many relationship. Each year's data would have a ProjectID foreign key link to Table1 (Projects, I'd call it; meaningful names make life much easier), with one record per year. You would then be able to use Totals queries or DSum() functions to add up amounts from the multiple years. Notes Project_MGR_NM Owner_NM Sponsor_NM I'd really suggest a table of People, with fields PersonID, LastName, FirstName, and other biographical data; this table should have number fields as links to the PersonID. YR_2015 YR_2016 Yep. There you go... you ran out of years and had to fix your table. That is NOT necessary if you have a properly normalized design! Table2 - Subform2 Project Number Description Project Type 2010 Notes RLT Approved Change Approved Date Will that help any? Is 2010 a fieldname? Is Project Number a link to Table1.Project ID or an unrelated number? More info please! Meaningful table names, and a brief description of what Entity Type (real-life person, thing or event) each table represents would be very helpful. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Main Form with Subforms
Mr. John W. Vinson - I apologize - assuming you have worked in databases long
enough that even you should know that most generally you are not in the ground up process of creating or building a database - this is one I inheritated 8 months ago and have been instructed not to spend much time reworking the tables and queries, but simply work with what I have as this database will be replaced by an oracle database in the very near future. So, I am working with what I have. I have a generalized make table query that dates the years - future 1, future 2, so on and so forth. If you would have read on further, some of the questions you asked would have been answered - I realize I am the one asking for help, but quite frankly this shouldn't be that big of a deal - I had it working once, renamed a query and replaced it and now I cannot get it to work. As most of this is pertaining to Projects and the management of such, the title of each field is pretty descriptive I would think. The Year fields are what the projects forecast for spending on those projects, each project has an owner, a project manager, and a sponsor, each project has a description, project type - capital or expense. Each project has an original approved amount of spending - Current Plan is what is currently approved, Current forecast is what they think they will spend that year, YTD is what they have spent that year and then we go to meetings and funds are either added or shaved off and this is the RLT Approved field. The notes are just that, notes on the project, approved date is the date the new data entered was approved. Very simple. Susan M. Project Controls Data Coordinator "John W. Vinson" wrote: On Tue, 9 Mar 2010 13:54:01 -0800, SaM wrote: Comments and concerns inline. I thought it would be easiest if I displayed fields in the tables and query: Query1 – Main Form Project ID Project Description Project Type Original Plan RLT Approved YTD If this is a Year To Date sum of (something) then the field should simply NOT EXIST. Instead it should be calculated on the fly in a Query. Current Plan Current Forecast Table1 – Subform1 I'm confused. What is the relationship between Query1 and Table1? Tables are *fundamental* - queries are based on Tables, Forms are based on Queries. I worry that you might be building your house upside down...! Project ID Project Description Project Type Project Status Venture Initiative YR_2010 YR_2011 YR_2012 YR_2013 YR_2014 "Fields are expensive, records are cheap". What will you do in 2015? Redesign all your tables, all your forms, all your queries, all your reports? go out of business?? If each Project has multiple values for different years, then you should - must, I'd say! - have *two tables* in a one to many relationship. Each year's data would have a ProjectID foreign key link to Table1 (Projects, I'd call it; meaningful names make life much easier), with one record per year. You would then be able to use Totals queries or DSum() functions to add up amounts from the multiple years. Notes Project_MGR_NM Owner_NM Sponsor_NM I'd really suggest a table of People, with fields PersonID, LastName, FirstName, and other biographical data; this table should have number fields as links to the PersonID. YR_2015 YR_2016 Yep. There you go... you ran out of years and had to fix your table. That is NOT necessary if you have a properly normalized design! Table2 - Subform2 Project Number Description Project Type 2010 Notes RLT Approved Change Approved Date Will that help any? Is 2010 a fieldname? Is Project Number a link to Table1.Project ID or an unrelated number? More info please! Meaningful table names, and a brief description of what Entity Type (real-life person, thing or event) each table represents would be very helpful. -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|