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
|
|||
|
|||
Bound Fields
In my database I have two tables:
T-Personnel Data and T-Annual Training used to track how many days my guardsmen deploy each year. I created a form F-Annual Training Summary that has the following fields: Last Name, First Name, Rank, Social Security and all the fields are filled in when the form opens. I have a macro button next to each name so when you click on it , it opens a pop-up form F-Annual Training which is used to enter each persons tour of duty Start - Stop dates. The form opens based on a Query so that it only shows their records. When I created the pop-up form F-Annual Training I didn't want the user to have to fill in any of the fields such as Last Name, First Name just the tour of duty Start -Stop dates, so I bound them to the same fields in F-Annual Training Summary. This is my problem - When I look in the table that stores the data, T-Annual Training, the only data in the table is the Start - Stop dates, The info from the bound fields, Last Name, First Name does not flow over into the table. Is there and easy fix for this - my programming skills are limited... George |
#2
|
|||
|
|||
Bound Fields
The root of the problem is your database design. You should not be carrying
duplicate data in your tables. For example, you have name, rank, and SSN i the Personnel table in the Annual Training Summary table. You are experiencing the pain of that mistake. Also, a form can have only one record source. That is one table or one query. The query can be based on multiple tables or other queries, but then you run the risk of creating an unupdatable recordset. That means you can display it, but you can't change it. Here is how you should do it. Your Personnel table should have a primary key field. In this case, I would suggest an Autonumber field. Each time you add a record to a table, the Autonumber field will generate a number for the field. This technique is known as an Artificial Primary key. That is because it is not based on any actual data in the table. Note that autonumber fields should never be exposed to the user or used for anything other than relating tables. Now, you Training Summary table should not have any personnel demographic data in it at all. It should only have data pertinent to the training. Instead, you add a field of Numeric Long data type. It should have the value of the Autonumber primary key field in the Personnel table that relates to the training record. This type of field in known as a Foreighn Key. That is, it identifies to which Personnel record the Training record belongs. Now, to get this to work efficiently, I suggest a form/subform construct. First, you use the Personnel table as the record source for the main form and the Training table for the subform. In the subform control on your main form, you identify the form name you want to use for the Training records in the Source Object property. Now, the really important part. There are two other properties of a subform control. They are the Link Master Field(s) and Link Child Field(s) properties. This is how you make the record or records displayed in the suborm stay in sync with the record displayed in the main form. The Link Master Field(s) property should have the name of the Primary key field in the recordset of the main form, which would be your new autonumber field in the Personnel table. The Link Child Field(s) property should have the name of the field in the subform's recordset that relates it to the personnel record. That would be the field that holds the foreign key I described earlier. You don't have to worry about managing the foriegn key field in th training table if you set it up like this. When you create a new training record, it will autmaticcally populate the foreign key field with the primary key of the parent record. Please post back if you need more help. You guys are taking care of us and I am happy to give back a little. -- Dave Hargis, Microsoft Access MVP "George" wrote: In my database I have two tables: T-Personnel Data and T-Annual Training used to track how many days my guardsmen deploy each year. I created a form F-Annual Training Summary that has the following fields: Last Name, First Name, Rank, Social Security and all the fields are filled in when the form opens. I have a macro button next to each name so when you click on it , it opens a pop-up form F-Annual Training which is used to enter each persons tour of duty Start - Stop dates. The form opens based on a Query so that it only shows their records. When I created the pop-up form F-Annual Training I didn't want the user to have to fill in any of the fields such as Last Name, First Name just the tour of duty Start -Stop dates, so I bound them to the same fields in F-Annual Training Summary. This is my problem - When I look in the table that stores the data, T-Annual Training, the only data in the table is the Start - Stop dates, The info from the bound fields, Last Name, First Name does not flow over into the table. Is there and easy fix for this - my programming skills are limited... George |
#3
|
|||
|
|||
Bound Fields
Dave - Wow what a response, this will keep me busy
on the flight back..Thanks It's our pleasure to serve - glad it doesn't go unnoticed.... George "Klatuu" wrote: The root of the problem is your database design. You should not be carrying duplicate data in your tables. For example, you have name, rank, and SSN i the Personnel table in the Annual Training Summary table. You are experiencing the pain of that mistake. Also, a form can have only one record source. That is one table or one query. The query can be based on multiple tables or other queries, but then you run the risk of creating an unupdatable recordset. That means you can display it, but you can't change it. Here is how you should do it. Your Personnel table should have a primary key field. In this case, I would suggest an Autonumber field. Each time you add a record to a table, the Autonumber field will generate a number for the field. This technique is known as an Artificial Primary key. That is because it is not based on any actual data in the table. Note that autonumber fields should never be exposed to the user or used for anything other than relating tables. Now, you Training Summary table should not have any personnel demographic data in it at all. It should only have data pertinent to the training. Instead, you add a field of Numeric Long data type. It should have the value of the Autonumber primary key field in the Personnel table that relates to the training record. This type of field in known as a Foreighn Key. That is, it identifies to which Personnel record the Training record belongs. Now, to get this to work efficiently, I suggest a form/subform construct. First, you use the Personnel table as the record source for the main form and the Training table for the subform. In the subform control on your main form, you identify the form name you want to use for the Training records in the Source Object property. Now, the really important part. There are two other properties of a subform control. They are the Link Master Field(s) and Link Child Field(s) properties. This is how you make the record or records displayed in the suborm stay in sync with the record displayed in the main form. The Link Master Field(s) property should have the name of the Primary key field in the recordset of the main form, which would be your new autonumber field in the Personnel table. The Link Child Field(s) property should have the name of the field in the subform's recordset that relates it to the personnel record. That would be the field that holds the foreign key I described earlier. You don't have to worry about managing the foriegn key field in th training table if you set it up like this. When you create a new training record, it will autmaticcally populate the foreign key field with the primary key of the parent record. Please post back if you need more help. You guys are taking care of us and I am happy to give back a little. -- Dave Hargis, Microsoft Access MVP "George" wrote: In my database I have two tables: T-Personnel Data and T-Annual Training used to track how many days my guardsmen deploy each year. I created a form F-Annual Training Summary that has the following fields: Last Name, First Name, Rank, Social Security and all the fields are filled in when the form opens. I have a macro button next to each name so when you click on it , it opens a pop-up form F-Annual Training which is used to enter each persons tour of duty Start - Stop dates. The form opens based on a Query so that it only shows their records. When I created the pop-up form F-Annual Training I didn't want the user to have to fill in any of the fields such as Last Name, First Name just the tour of duty Start -Stop dates, so I bound them to the same fields in F-Annual Training Summary. This is my problem - When I look in the table that stores the data, T-Annual Training, the only data in the table is the Start - Stop dates, The info from the bound fields, Last Name, First Name does not flow over into the table. Is there and easy fix for this - my programming skills are limited... George |
#4
|
|||
|
|||
Bound Fields
Post back if you have any more questions, George
-- Dave Hargis, Microsoft Access MVP USN '62 - '66 "George" wrote: Dave - Wow what a response, this will keep me busy on the flight back..Thanks It's our pleasure to serve - glad it doesn't go unnoticed.... George "Klatuu" wrote: The root of the problem is your database design. You should not be carrying duplicate data in your tables. For example, you have name, rank, and SSN i the Personnel table in the Annual Training Summary table. You are experiencing the pain of that mistake. Also, a form can have only one record source. That is one table or one query. The query can be based on multiple tables or other queries, but then you run the risk of creating an unupdatable recordset. That means you can display it, but you can't change it. Here is how you should do it. Your Personnel table should have a primary key field. In this case, I would suggest an Autonumber field. Each time you add a record to a table, the Autonumber field will generate a number for the field. This technique is known as an Artificial Primary key. That is because it is not based on any actual data in the table. Note that autonumber fields should never be exposed to the user or used for anything other than relating tables. Now, you Training Summary table should not have any personnel demographic data in it at all. It should only have data pertinent to the training. Instead, you add a field of Numeric Long data type. It should have the value of the Autonumber primary key field in the Personnel table that relates to the training record. This type of field in known as a Foreighn Key. That is, it identifies to which Personnel record the Training record belongs. Now, to get this to work efficiently, I suggest a form/subform construct. First, you use the Personnel table as the record source for the main form and the Training table for the subform. In the subform control on your main form, you identify the form name you want to use for the Training records in the Source Object property. Now, the really important part. There are two other properties of a subform control. They are the Link Master Field(s) and Link Child Field(s) properties. This is how you make the record or records displayed in the suborm stay in sync with the record displayed in the main form. The Link Master Field(s) property should have the name of the Primary key field in the recordset of the main form, which would be your new autonumber field in the Personnel table. The Link Child Field(s) property should have the name of the field in the subform's recordset that relates it to the personnel record. That would be the field that holds the foreign key I described earlier. You don't have to worry about managing the foriegn key field in th training table if you set it up like this. When you create a new training record, it will autmaticcally populate the foreign key field with the primary key of the parent record. Please post back if you need more help. You guys are taking care of us and I am happy to give back a little. -- Dave Hargis, Microsoft Access MVP "George" wrote: In my database I have two tables: T-Personnel Data and T-Annual Training used to track how many days my guardsmen deploy each year. I created a form F-Annual Training Summary that has the following fields: Last Name, First Name, Rank, Social Security and all the fields are filled in when the form opens. I have a macro button next to each name so when you click on it , it opens a pop-up form F-Annual Training which is used to enter each persons tour of duty Start - Stop dates. The form opens based on a Query so that it only shows their records. When I created the pop-up form F-Annual Training I didn't want the user to have to fill in any of the fields such as Last Name, First Name just the tour of duty Start -Stop dates, so I bound them to the same fields in F-Annual Training Summary. This is my problem - When I look in the table that stores the data, T-Annual Training, the only data in the table is the Start - Stop dates, The info from the bound fields, Last Name, First Name does not flow over into the table. Is there and easy fix for this - my programming skills are limited... George |
#5
|
|||
|
|||
Bound Fields
Thanks - Will get back to you ...
George - USAF/ANG 1974 to Present "Klatuu" wrote: Post back if you have any more questions, George -- Dave Hargis, Microsoft Access MVP USN '62 - '66 "George" wrote: Dave - Wow what a response, this will keep me busy on the flight back..Thanks It's our pleasure to serve - glad it doesn't go unnoticed.... George "Klatuu" wrote: The root of the problem is your database design. You should not be carrying duplicate data in your tables. For example, you have name, rank, and SSN i the Personnel table in the Annual Training Summary table. You are experiencing the pain of that mistake. Also, a form can have only one record source. That is one table or one query. The query can be based on multiple tables or other queries, but then you run the risk of creating an unupdatable recordset. That means you can display it, but you can't change it. Here is how you should do it. Your Personnel table should have a primary key field. In this case, I would suggest an Autonumber field. Each time you add a record to a table, the Autonumber field will generate a number for the field. This technique is known as an Artificial Primary key. That is because it is not based on any actual data in the table. Note that autonumber fields should never be exposed to the user or used for anything other than relating tables. Now, you Training Summary table should not have any personnel demographic data in it at all. It should only have data pertinent to the training. Instead, you add a field of Numeric Long data type. It should have the value of the Autonumber primary key field in the Personnel table that relates to the training record. This type of field in known as a Foreighn Key. That is, it identifies to which Personnel record the Training record belongs. Now, to get this to work efficiently, I suggest a form/subform construct. First, you use the Personnel table as the record source for the main form and the Training table for the subform. In the subform control on your main form, you identify the form name you want to use for the Training records in the Source Object property. Now, the really important part. There are two other properties of a subform control. They are the Link Master Field(s) and Link Child Field(s) properties. This is how you make the record or records displayed in the suborm stay in sync with the record displayed in the main form. The Link Master Field(s) property should have the name of the Primary key field in the recordset of the main form, which would be your new autonumber field in the Personnel table. The Link Child Field(s) property should have the name of the field in the subform's recordset that relates it to the personnel record. That would be the field that holds the foreign key I described earlier. You don't have to worry about managing the foriegn key field in th training table if you set it up like this. When you create a new training record, it will autmaticcally populate the foreign key field with the primary key of the parent record. Please post back if you need more help. You guys are taking care of us and I am happy to give back a little. -- Dave Hargis, Microsoft Access MVP "George" wrote: In my database I have two tables: T-Personnel Data and T-Annual Training used to track how many days my guardsmen deploy each year. I created a form F-Annual Training Summary that has the following fields: Last Name, First Name, Rank, Social Security and all the fields are filled in when the form opens. I have a macro button next to each name so when you click on it , it opens a pop-up form F-Annual Training which is used to enter each persons tour of duty Start - Stop dates. The form opens based on a Query so that it only shows their records. When I created the pop-up form F-Annual Training I didn't want the user to have to fill in any of the fields such as Last Name, First Name just the tour of duty Start -Stop dates, so I bound them to the same fields in F-Annual Training Summary. This is my problem - When I look in the table that stores the data, T-Annual Training, the only data in the table is the Start - Stop dates, The info from the bound fields, Last Name, First Name does not flow over into the table. Is there and easy fix for this - my programming skills are limited... George |
Thread Tools | |
Display Modes | |
|
|