Lookup Values

A

Arfaja

I have a data access page with name, address, city, state and zip that allows
the user to update a Orders Table. What I would like to do is have the name
field be a dropdown list and when the user selects a name from the list (this
list is from another table named "Users"), i would like the address, city,
state, and zip to populate from the "Users" table onto the data access page
so i can update the Orders Table. Can anybody help me with this?
 
J

John Vinson

I have a data access page with name, address, city, state and zip that allows
the user to update a Orders Table. What I would like to do is have the name
field be a dropdown list and when the user selects a name from the list (this
list is from another table named "Users"), i would like the address, city,
state, and zip to populate from the "Users" table onto the data access page
so i can update the Orders Table. Can anybody help me with this?
Click to expand...

That's not how relational databases are designed to work. Storing each
user's address information redundantly in the Orders table is neither
necessary nor good design - if someone's address changes, it gives you
an arbitrary number of redundant address fields to track down and
edit.

Unless you need to store the address *IN THE ORDERS TABLE*, so that
each order records the address of the user as of the time of the
order, you're much better off just storing the UserID and using a
Query joining the Orders table to the User table to pick up the user's
current address.

John W. Vinson[MVP]
 
A

Arfaja

Thanks John, but i need to store the address at the time of the order. I
need to reference where the order was sent when it was purchased, so joining
the tables will not work becasue address do change. Is this possible?
 
T

TC

Conceptually, you might have two addresses: the customer's current
addres, ie. where they actually are right now, and the address to which
each order was (or should be) shipped. The first address could go in
the customer's table, the second could go in the orders table. The
first address is an attribute of the customer reghardless of order The
second address is an attribute of the order regardless of customer.

HTH,
TC
 
J

John Vinson

Thanks John, but i need to store the address at the time of the order. I
need to reference where the order was sent when it was purchased, so joining
the tables will not work becasue address do change. Is this possible?
Click to expand...

Well, I know how to do it on an Access Form (use the Form's
BeforeUpdate event to copy the data from the controls bound to the
User table to the Orders table) - I'm sure there's something similar
in DAPs but I'm not experienced enough with them to say. Perhaps some
script in the appropriate page event would work.

John W. Vinson[MVP]
 
Top