A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Bound Fields



 
 
Thread Tools Display Modes
  #1  
Old April 18th, 2008, 04:35 PM posted to microsoft.public.access.forms
George
external usenet poster
 
Posts: 883
Default 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  
Old April 18th, 2008, 06:44 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old April 18th, 2008, 11:07 PM posted to microsoft.public.access.forms
George
external usenet poster
 
Posts: 883
Default 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  
Old April 21st, 2008, 02:33 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old April 22nd, 2008, 02:04 AM posted to microsoft.public.access.forms
George
external usenet poster
 
Posts: 883
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.