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 not carrying names to table behind the subform.
I have a subform linking to a tbl_class. The tbl_class has an ID, LastName,
FirstName, and ClassDate. ClassDate is date completed. (Access 2003) The relationship is one on the main form to many on the subform. The ID is populated from the main form and the ClassDate is entered. However, the LastName and FirstName does not carry over from the main form. I must be doing something wrong? People leave the organization every three years or so. They take many classes and their names should remain in the tbl_class for historical and validation purpose. How can I get the LastName and FirstName to populate the tbl_class after I have entered the ClassDate but before I move onto the next record? Appreciate any help! -- Gil Silva |
#2
|
|||
|
|||
Form not carrying names to table behind the subform.
On Mon, 19 Apr 2010 18:56:01 -0700, Gil Silva
wrote: I have a subform linking to a tbl_class. The tbl_class has an ID, LastName, FirstName, and ClassDate. ClassDate is date completed. (Access 2003) The relationship is one on the main form to many on the subform. The ID is populated from the main form and the ClassDate is entered. However, the LastName and FirstName does not carry over from the main form. I must be doing something wrong? Just your expectation. Relationships do NOT copy data from one table to another. That's not what they're for and not how they work. People leave the organization every three years or so. They take many classes and their names should remain in the tbl_class for historical and validation purpose. How can I get the LastName and FirstName to populate the tbl_class after I have entered the ClassDate but before I move onto the next record? Appreciate any help! Why do you want to store the names redundantly in a second table??? The name should exist in a Student table *and noplace else*; you would use a query joining the Student table to the Class table to see the name in conjunction with class information. What are your Tables, with relevant fieldnames? What is each table's Primary Key? How are the tables related? -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Form not carrying names to table behind the subform.
Why do you want to store the names redundantly in a second table?
Ans: The main table contains new hires and new report-ins. If they do not travel to specific places in the three years and they leave the organization, they are deleted from the database. They are required to take class prior to traveling to specific places. The info is to be maintained to determine sizes of class and historically who attended to budget for future type classes. If I delete names off the master file I loose the names in the tbl_class when I run a query. Other words the tbl_class should far exceed the number of records in the main table. Looking the Properties Data tab the Class form is: Link Master Fields ID Link Child Fields ID Filter on Empty Master Yes Enable Yes Locked No There is no primary key in master and no primary key in tbl_class because I have to delete records in the master if no activity. The tables are tbl_members and tbl_class tbl_members (relevant fields) ID, Last_Name, First_Name tbl_class (relevant fields) ID, LastName, First_Name, ClassDate There is another subform on the main form which displays trips (tbl_trips) and that is working fine but it does not contain names, just ID because if the members take trips the record is saved and not deleted so I can do the query you mentioned. Tha tables are not related except tbl_trips and tbl_members. They show relationships to the ID in both tables. Hope I supplied what you requested. Thanks for responding so quickly. -- Gil Silva "John W. Vinson" wrote: On Mon, 19 Apr 2010 18:56:01 -0700, Gil Silva wrote: I have a subform linking to a tbl_class. The tbl_class has an ID, LastName, FirstName, and ClassDate. ClassDate is date completed. (Access 2003) The relationship is one on the main form to many on the subform. The ID is populated from the main form and the ClassDate is entered. However, the LastName and FirstName does not carry over from the main form. I must be doing something wrong? Just your expectation. Relationships do NOT copy data from one table to another. That's not what they're for and not how they work. People leave the organization every three years or so. They take many classes and their names should remain in the tbl_class for historical and validation purpose. How can I get the LastName and FirstName to populate the tbl_class after I have entered the ClassDate but before I move onto the next record? Appreciate any help! Why do you want to store the names redundantly in a second table??? The name should exist in a Student table *and noplace else*; you would use a query joining the Student table to the Class table to see the name in conjunction with class information. What are your Tables, with relevant fieldnames? What is each table's Primary Key? How are the tables related? -- John W. Vinson [MVP] . |
#4
|
|||
|
|||
Form not carrying names to table behind the subform.
=?Utf-8?B?R2lsIFNpbHZh?= wrote
in : Why do you want to store the names redundantly in a second table? Ans: The main table contains new hires and new report-ins. If they do not travel to specific places in the three years and they leave the organization, they are deleted from the database. Design error here. They should stay in the databsae, but have a dateLeftOrganization field. Query the table for current members is done by filtering on your new field 'is null' Bob Q They are required to take class prior to traveling to specific places. The info is to be maintained to determine sizes of class and historically who attended to budget for future type classes. If I delete names off the master file I loose the names in the tbl_class when I run a query. Other words the tbl_class should far exceed the number of records in the main table. Looking the Properties Data tab the Class form is: Link Master Fields ID Link Child Fields ID Filter on Empty Master Yes Enable Yes Locked No There is no primary key in master and no primary key in tbl_class because I have to delete records in the master if no activity. The tables are tbl_members and tbl_class tbl_members (relevant fields) ID, Last_Name, First_Name tbl_class (relevant fields) ID, LastName, First_Name, ClassDate There is another subform on the main form which displays trips (tbl_trips) and that is working fine but it does not contain names, just ID because if the members take trips the record is saved and not deleted so I can do the query you mentioned. Tha tables are not related except tbl_trips and tbl_members. They show relationships to the ID in both tables. Hope I supplied what you requested. Thanks for responding so quickly. |
#5
|
|||
|
|||
Form not carrying names to table behind the subform.
On Tue, 20 Apr 2010 05:49:02 -0700, Gil Silva
wrote: Why do you want to store the names redundantly in a second table? Ans: The main table contains new hires and new report-ins. If they do not travel to specific places in the three years and they leave the organization, they are deleted from the database. They are required to take class prior to traveling to specific places. The info is to be maintained to determine sizes of class and historically who attended to budget for future type classes. If I delete names off the master file I loose the names in the tbl_class when I run a query. Other words the tbl_class should far exceed the number of records in the main table. Looking the Properties Data tab the Class form is: Link Master Fields ID Link Child Fields ID Filter on Empty Master Yes Enable Yes Locked No There is no primary key in master and no primary key in tbl_class because I have to delete records in the master if no activity. The tables are tbl_members and tbl_class tbl_members (relevant fields) ID, Last_Name, First_Name tbl_class (relevant fields) ID, LastName, First_Name, ClassDate There is another subform on the main form which displays trips (tbl_trips) and that is working fine but it does not contain names, just ID because if the members take trips the record is saved and not deleted so I can do the query you mentioned. Tha tables are not related except tbl_trips and tbl_members. They show relationships to the ID in both tables. I'll absolutely agree with Bob Quintal. It's inappropriate to delete the record in the master table *because you still need those names*. You can instead add a Yes/No field Current to the table, and (where you don't need historical data) use a query selecting only current employees. The alternative would be to use the AfterUpdate event of the combo box to "push" the names into tbl_class but I'd say that this would be A Very Bad Design. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Form not carrying names to table behind the subform.
Thanks Bob,
DB Design is not one of my strong points. I'll stop deleting members when they leave and put the date they leave to save the design and keep the database somewhat stable. -- Gil Silva "Bob Quintal" wrote: =?Utf-8?B?R2lsIFNpbHZh?= wrote in : Why do you want to store the names redundantly in a second table? Ans: The main table contains new hires and new report-ins. If they do not travel to specific places in the three years and they leave the organization, they are deleted from the database. Design error here. They should stay in the databsae, but have a dateLeftOrganization field. Query the table for current members is done by filtering on your new field 'is null' Bob Q They are required to take class prior to traveling to specific places. The info is to be maintained to determine sizes of class and historically who attended to budget for future type classes. If I delete names off the master file I loose the names in the tbl_class when I run a query. Other words the tbl_class should far exceed the number of records in the main table. Looking the Properties Data tab the Class form is: Link Master Fields ID Link Child Fields ID Filter on Empty Master Yes Enable Yes Locked No There is no primary key in master and no primary key in tbl_class because I have to delete records in the master if no activity. The tables are tbl_members and tbl_class tbl_members (relevant fields) ID, Last_Name, First_Name tbl_class (relevant fields) ID, LastName, First_Name, ClassDate There is another subform on the main form which displays trips (tbl_trips) and that is working fine but it does not contain names, just ID because if the members take trips the record is saved and not deleted so I can do the query you mentioned. Tha tables are not related except tbl_trips and tbl_members. They show relationships to the ID in both tables. Hope I supplied what you requested. Thanks for responding so quickly. . |
#7
|
|||
|
|||
Form not carrying names to table behind the subform.
Thanks, John
DB Design is not my strong point. But I am learning. I will stop deleting records to keep the database stable. It probably would be better to append the ID, Names, and Date of Class to a different database altogther if it can be done. I'll have to study up on appending records. Still learning. Thanks again. -- Gil Silva "John W. Vinson" wrote: On Tue, 20 Apr 2010 05:49:02 -0700, Gil Silva wrote: Why do you want to store the names redundantly in a second table? Ans: The main table contains new hires and new report-ins. If they do not travel to specific places in the three years and they leave the organization, they are deleted from the database. They are required to take class prior to traveling to specific places. The info is to be maintained to determine sizes of class and historically who attended to budget for future type classes. If I delete names off the master file I loose the names in the tbl_class when I run a query. Other words the tbl_class should far exceed the number of records in the main table. Looking the Properties Data tab the Class form is: Link Master Fields ID Link Child Fields ID Filter on Empty Master Yes Enable Yes Locked No There is no primary key in master and no primary key in tbl_class because I have to delete records in the master if no activity. The tables are tbl_members and tbl_class tbl_members (relevant fields) ID, Last_Name, First_Name tbl_class (relevant fields) ID, LastName, First_Name, ClassDate There is another subform on the main form which displays trips (tbl_trips) and that is working fine but it does not contain names, just ID because if the members take trips the record is saved and not deleted so I can do the query you mentioned. Tha tables are not related except tbl_trips and tbl_members. They show relationships to the ID in both tables. I'll absolutely agree with Bob Quintal. It's inappropriate to delete the record in the master table *because you still need those names*. You can instead add a Yes/No field Current to the table, and (where you don't need historical data) use a query selecting only current employees. The alternative would be to use the AfterUpdate event of the combo box to "push" the names into tbl_class but I'd say that this would be A Very Bad Design. -- John W. Vinson [MVP] . |
#8
|
|||
|
|||
Form not carrying names to table behind the subform.
On Wed, 21 Apr 2010 11:31:01 -0700, Gil Silva
wrote: Thanks, John DB Design is not my strong point. But I am learning. I will stop deleting records to keep the database stable. It probably would be better to append the ID, Names, and Date of Class to a different database altogther if it can be done. Sorry, but creating an entire new *DATABASE* would be substantially worse than just deleting the records. Just put a field in the table indicating whether the employee is current or not. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|