Updating table with data from another

[mattpsa]

Overview:
I have a table called Locations which contains a bunch of information, including the current address of each location. I have another table called Location_Address which contains the past, current, and future addresses of each location. Users do not interact with either of these tables directly, there is a form they use which allows them to view the information graphically. On this form there is only the current address listed, but there is a button which pops up another form containing all of the other addresses for a location.

Question: I want to add a button to the Other_Addresses form which allows the user to set any of the addresses listed as the current address displayed in the main form. (When the future, pending address becomes the current address, I want the users to be able to change it in the main form). I do not want to let the users manually change this information because of the risk of errors. Any ideas on the easiest way to do this? Thanks in advance

 

[JeremyNYC]

I'm not 100% clear on when you want users to be able to change things and when not, but what I would suggest is that you get rid of the addy fields in the main table and keep them only in the second table. Then you can have a "Current" field, or something like that--boolean--to indicate which is the current field.

On your form, have a subform to display the addresses. Normally it will only display one, but when you click that button, it will list them all.

Storing the addy field in the main form makes a lot of work for you, because it's not normalized (duplicate info).

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.