populate one field from another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 tables... tblPersonnel, tblAssets and
tblHistory. I need to populate
[TransferredFrom] and [DateTransferred] in tblHistory
from the values in
[PersonnelID] and [DateAssigned] respectively from
tblAssets. (PersonnelID is a combobox concontinated to
show first and last name)Thereby each new
holder of that particular asset and the date he acquired
it will be transferred
to tblHistory whereby creating a record of the different
holders and dates of the asset.
Also must tblHistory be assigned a primary key?
Really appreciate your help
 
I have 3 tables... tblPersonnel, tblAssets and
tblHistory. I need to populate
[TransferredFrom] and [DateTransferred] in tblHistory
from the values in
[PersonnelID] and [DateAssigned] respectively from
tblAssets. (PersonnelID is a combobox concontinated to
show first and last name)Thereby each new
holder of that particular asset and the date he acquired
it will be transferred
to tblHistory whereby creating a record of the different
holders and dates of the asset.
Also must tblHistory be assigned a primary key?
Really appreciate your help
Click to expand...

A couple of concerns here: Names are NOT good unique keys, since names
are not unique. I once worked with Dr. Lawrence David Wise and his
colleague, Dr. Lawrence David Wise. Larry was tall, blond and
friendly; L. David was stocky, dark and taciturn. Are you actually
using the name? or just displaying it using the combo box? Bear in
mind that data *storage* and data *display* are two different things,
and that you are not storing "a combo box" in the table!

I would suggest opening a Recordset based on tblHistory in the
AfterUpdate event of the form and writing the desired data into it.
tblHistory must indeed have a primary key; otherwise it's a heap, not
a table, and you'll be unable to reliably find anything in it. Note
that a Primary Key need not be an autonumber, nor need it be a single
field; for instance, you might be able to use PersonnelID and
DateAssigned as a two-field dual primary key.

The code would be something like

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblHistory", dbOpenDynaset)
rs.AddNew ' open new record for addition
rs!PersonnelID = Me!PersonnelID
rs!DateAssigned = Me!DateAssigned
<etc. etc.>
rs.Update ' write the new record to disk
rs.Close
Set rs = Nothing

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top