Duplicating one Field from One table to Another

[Uni]

Hi -
I have two tables - one position, one personnel - which has a 1-to-many
relationship (1 position record to many personnel records). The department
had a new requirement which made it necessary to change some coding (I
inherited this). I'm using tab forms so that when a position is pulled up,
you can click on the tab that has the personnel information (if there is
any). There is a button on the Personnel form that allows the user to add a
new Personnel record. Since I am using an Auto-number field in the Position
table (which doubles as the PK) the functionality is fine. What I need is to
capture only one data field from the Position table and auto-populate the
corresponding Personnel field (Position.NewLineNo -> Personnel.NewLineNo).
The current code for the OnClick() event to Add Personnel to the Personnel
table is:

Private Sub cmdAddPerson_Click()
On Error GoTo Err_cmdAddPerson_Click


DoCmd.GoToRecord , , acNewRec

Exit_cmdAddPerson_Click:
Exit Sub

Err_cmdAddPerson_Click:
MsgBox Err.Description
Resume Exit_cmdAddPerson_Click

End Sub

Any help or advice would be greatly appreciated.

Thanks in advance.

Uni

 

[Al Campagna]

Uni,
An example of values from your Main form, and a couple of subform data
examples would be helpful.
What is the key field that relates the Main form to the Subform? (the
Parent to Child field?)

If it's NewLineNo, then you don't have to add that value to the personnel
subform. The realtionship you've established between the Position table and
the Personnel table, forces every record added to Personnel to have the
NewLineNo of it's parent Position.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[BruceM]

One person cannot be in several positions, either concurrently or over the
course of time?

What exactly are the table relationships? On what tables (or recordsets)
are the forms based? The tab may let you view personnel information, but
how is this accomplished? A subform? If so, assure that the subform
control has the Link Child and Link Master properties set to the linking
fields. If this doesn't address the problem you will probably need to
provide some more information about the database structure (rather than the
user interface).

 

[Uni]

Gentlemen;

The relationship is Position.PositionID(Primary Key - Auto-Number - 1) and
Personnel.PositionID (Foreign Key - Many) This is a 1-to-Many relationship.
I suppose, now that I'm thinking about it, I could actually use the NewLineNo
from the Position table and do away with the NewLineNo from the Personnel
considering that it's a duplicate value and really unnecessary.

Thanks for making my think

Uni

 

[Uni]

Bruce;

I have two tables - one position, one personnel - which has a 1-to-many
relationship (1 position record to many personnel records). The
Position.PositionID is an Auto-number and is the Primary Key (this is the one
side); the Personnel.PositionID is the foreign key (the many side) of this
relationship. The form that has a Add New Personnel button is a sub-form to
the Position Form using a tab control.

Hope this clarifies what I am doing.

Thanks,
Uni

 

[BruceM]

You seem to be describing a situation in which the personnel record exists
only as a subset of Position: each position can involve many employees, but
an employee record is created *only* in the context of a Position. Until a
person is assigned to a postion, there is no record for that person in your
database. Further, a person can be assigned to one position only, or else a
duplicate personnel record will be created if that person is ever assigned
to another postion.
If that is how your database is arranged, check the Link Child and Link
Parent properties of the subform control.
A more typical arrangement is a many-to-many relationship between Position
and Personnel, which involves a junction table to resolve the relationship.

tblPersonnel
PersonID (PK)
LastName
FirstName
etc.

tblPosition
PositionID (PK)
PositionDescription
etc.

tblPersonnelPosition
PersonnelPositionID (PK)
PersonID (foreign key, or FK)
PositionID (FK)
DateAssigned
etc.

There is a one-to-many relationship between both tblPersonnel and
tblPosition, and tblPersonnelPosition. There would be a Personnel table for
entering a record when a new person joins the company. For Position
information there is a Position form for creating and editing the position
information, with a subform based on tblPersonnelPosition. The subform
would have a combo box based on tblPersonnel and bound to PersonID in
tblPersonnelPosition. When creating the combo box row source, PersonID is
the first column. The second column (as seen in query design view) could
be:
FullName: [LastName] & ", " & [FirstName]
The combo box Bound Column is 1; the Column Count is 2; the Column Widths
are 0";1.5" (or whatever). The user will see the name, but the PersonID
(the bound column) is stored.