Bound Fields

[George]

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

 

[Klatuu]

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.

 

[George]

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]

Post back if you have any more questions, George

 

[George]

Thanks - Will get back to you ...

George - USAF/ANG 1974 to Present