automatic feild insertion

[Guest]

how do you have a field fill information in form an existing tabel
autmaitcally when a previous field is filled? Example I pick a last name for
a combo box and the first name and phone number will fill automatically. I
can do it in a form but it will not write to the table

 

[Rick B]

It should not write to the table. You only store that data in one table.
When you add related data to another table, you only store the key, not the
data.

In other words, if you have a transaction table and you fill in the "person
ID", that is all you need in that table. You don't want the person's name
and phone number stored in the transaction table. What happens if they
change phone numbers? Then the data is wrong.

For more info, look at the northwind database that ships with Access. Go
try to add an invoice and notice how it handles it when you select a
customer. It fills in the address data on the form, but that is not saved
back to the invoice table.

 

[Arvin Meyer [MVP]]

If you are writing more than the ID value of that person to another table,
you are probably writing too much data. Normalization rules forbid writing
anything more than a key from one table to another.

To answer your question on how, you push the data in the combo's AfterUpdate
event:

Sub MyCombo_AfterUpdate()
Me.txtFirstName = Me.MyCombo.Column(2)
Me.txtPhone = Me.MyCombo.Column(3)
End Sub

Where Column 2 (actually the 3rd column) and Column 3 (actually the 4th
column) of the combo box contain the first name and phone data.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access