Question On How To Copy Data From 2 Records To 1 Easily

[mwroobel]

Hi all. I setup a relatively simple database for my brothers business. It is basically a list of items sold, some of which are bought from one person and sold to another. I set it up so he could search in the received from layout on a serial number, then duplicate the record, and clear out the name, address city state zip etc and leaving the equipment information intact so he can enter the new owner information. A lot of the equipment goes to and from a similar group of people. Would there be a way, once I am in the duplicated field, to display a list of all the users currently in the system (first and last name) and when you click on that name in the dropdown list it will also copy the address, city, state, zip, etc from that record into the new duped record?

Thanks a lot!!

[SlimJim]

I'm not sure if you will want to hear this but your structure is wrong. What you are doing here is duplicating data at each stage and that should not be necessary.

At the simplest you need two tables: Equipment, Users. In the Equipment table you need three ID fields. EquipmentID, SellerID and BuyerID and whatever else you need to describe the equipment. In the User table you need a UserID, and names etc. Make two table occurrences in the relationship graph of the users table. Call one of them Sellers and the other one Buyers. Then relate Equipment to Sellers via SellerID match UserID and relate Equipment to Buyers via BuyerID match UserID.

On your equipment layout set up the SellerID and BuyerID fields as drop-downs from a value list of userID also showing values from Username (calculated field of FirstName & " " & LastName). This will show both the UserID and the name. When you select an item then the UserID will drop into the field and set up the relationship. You can put the contact fields from the users table onto the equipment layout (there is no need to copy them into the table). So if there is enough space you could put both seller and buyer details onto the same layout.