Hi Tank:

Thanks so much for your detailed explanation. Let me just give you an
update. Ignore what I said about the data having to be "stored" in the
assignments table. I was told that's the way our database is set up. Wrong! I
went into the clients table and changed the last name of one of our clients
to test it. Voila! All the data in the assignments table changed as well. (Of
course, I've been doing my experiments with a copy of our database.)

Our company wanted everything to remain constant as described previously,
but that's not the way it was designed. Accordingly, I'm not changing it. It
stays the way it is. I was just asked to modify the database so when the
cust_id is selected, all the other data is autopopulated.

Now that you have the latest info, here are my comments. Your assumptions in
paragraphs 1 and 2 are correct. We have a clients table and an assignments
table and some of the fields are pretty much identical. In the next series of
paragraphs you describe using an event procedure in the After Update box. I
assume that procedure is valid in light of the new information I provided
above since later on you describe a procedure for "locking in values" and the
code to do that (which I am not going to do).

I plan to use your "suggested event procedure" as a model. First, however, I
want to point out that my CUST ID control in the assignment input form is not
a text box but a combo box. Does that make any difference with using the
event procedure in the After Update box?

Second, right now all the other controls for the company, address, phone,
etc., in the assignment input form are combo boxes with drop down menus.
Should I still use the procedure I referenced in my prior posts by setting
the row source query in the CUST ID control and the control source of the
textbox so the info I need appears in the form automatically?

Thanks, again, for taking time to provide such a detailed response. I really
appreciate your help, Tank.
--
rszebras


"Tank" wrote:

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