Hi rszebras,

In studying your comments about autopopulating certain fields in the
Assignments table with values coming from the Clients table, (e.g. name,
address, telephone, etc.), I am assuming you have fields in your Assignments
table that match up with the fields in the Clients table. I’m assuming those
common fields may have the same names or slightly different names but are of
the same type in structure and are reserved for identical values.

For example, in your Clients table, you may have Fname, Mname, Lname,
Street, City, State, Zip, Telephone.
In your Assignments table, you may have Firstname, MiddleName,LastName,
StreetAddress, StateCode, PostalCode, Phone.

Since you’re using a text box named Cust ID (better to name it Cust_ID
without spaces) in your “Assignments data entry form” (whose record source is
the Assignments table), to display name, address, telephone, etc. from the
Clients table, you can use an Event Procedure in the CustID text box to copy
the values from the clients table to the Assignments table, assuming, as John
Vinson recommended and which I believe you are doing, that the two tables are
linked in some fashion that might resemble, [Clients].[CustID] =
[Assignments].[Cust_ID].

The transfer of these values can be accomplished in the following way:

In form design view (e.g. “Assignments Data Entry Form”), click the text
box, “CustID” and open the Property Sheet. Click the Event tab in the
Property Sheet and place your cursor on the line, “After Update”. When your
cursor is on that line, you will see two control buttons on the far right
side of that line. Click the first button and highlight the words, “Event
Procedure” on the drop-down list. Then click the second button (ellipsis…)
and you will be taken to the Event Procedure screen. Between the two
existing lines, “Private Sub Cust_ID_After Update () and “End Sub”, add your
event procedure, as shown below.

Let’s assume the Clients table uses “CustID” and your linked ID in the
Assignments table is named “Cust_ID”, and you have the following sample
fields whose values you want to transfer,

CLIENTS TABLE: FName, MName, LName, Street, City, State, Zip, Telephone.
ASSIGNMENTS TABLE: Firstname, MiddleName, LastName, StreetAddress, Town,
StateCode, PostalCode, Phone.

HERE IS A SUGGESTED EVENT PROCEDU
*******************************
Private Sub Cust_ID_AfterUpdate()

FirstName = FName
MiddleName = MName
LastName = LName
StreetAddress = Street
Town = City
StateCode = State
PostalCode = Zip
Phone = Telephone

End Sub
******************************

Of course, if you are using the same field names in both tables, you need to
use the [tablename].[fieldname] formatting Example: [Clients].[FName] =
[Assignments].[FName]

If you definitely want to lock in your value transfers (in the event someone
comes along later to mistakenly reenter the same Cust_ID number or a
different one, you could set up an expression for each field, such as,

If IsNull (FirstName) Then
Firstname = FName

You may not need to transfer values exactly as they are in the Clients
table. For example, you may need only the full name in your Assignments
table, not broken down by first name, middle name, last name. You could thus
batch some of your fields, if appropriate, for your Assignments usage at this
transfer stage, if practical. For example, you could have customized fields
in the Assignments table, such as “FullName”, FullAddress”, etc.

FullName = [Fname] &” “& [Mname]&” “&[Lname] or
FullAddress = [StreetAddress] & vbNewLine & [Town] &”, “& [StateCode] &” “&
[PostalCode]

The FullAddress example shows how you can make a mailing label-like format
by stringing several fields together --- all from the AfterUpdate () Event
Procedure of the Cust_ID text box in your “Assignments Data Entry Form”.

But of course, practically everything I say above is going contrary to good
database development, as John Vinson, and many, many others emphasize.
However, you’re reasons for duplicating the data is also sound. Building the
database to meet your company’s needs is paramount and on occasion may
supersede normal development protocols. I would only hope that not too much
information needs to be transferred in this fashion, otherwise you could be
faced with a labor-intensive operation, particularly if you have to
continually select certain fields for transfer record by record.
- - - -
Tank