Dlookup

[cbrsix]

I have 2 tables (client list & change of address list). The client list contains the clients newest address and the change of addess list has both the old and new addresses in it. I was thinking of setting up the Dlookup function to draw the address from the client list but realized that when I put the new address in it will update in the change of address list there too. I don't want this to happen. Is there a way to have the change of address list look up the old address from the client list and have it not update once entered and if the client list address is updated again, it creates a new record in the other table? Is this possible? I know I can always copy and paste, but I want to avoid that.

[NTC]

Your post to me is not fully understandable. Its not clear ifyour question is around an existing design, or if it involves making the design.

If one is to keep the history of addresses - then there should be a stand alone address table. There should be a field which designates which address is current.

Not sure this helps.

[cbrsix]

I have 2 tables: 1. Client List 2. Change of Address List

- The Client list contains all of the client information including their most current address.
- I have to keep records of the clients addresses when they are updated. I do this through the Change of Address list. This list contains the client's ID and their old address and new address. What I want to happen is to have the Change of address list (when the client ID is inputted) to grab the address from the client list and put it in the old address fields. Then I want to enter the new address for the client in the new address fields in the change of address list. Then I want that address to be automatically updated into the client list address.

- My question then becomes, is it possible to do this and have the old address (that was pulled from the client list into the change of address old address fields) not update when the new address is entered into the new address fields and then pulled into the client list? This creates a circular reference type of thing but I didn't know if there is a keep/dont update function.

I hope this helps clarify what I am trying to accomplish.

[NTC]

Yes. You can trigger an append query that would write the address info to the table.

Another approach is simply to rely on the address table as a subform inside your client form.

[cbrsix]

Which one would you consider to be the better method? Simplest?

[NTC]

the subform is simplist. just include a field checkbox for 'current' so when someone has multiple past addresses you always know which is current...or if you want to know when they changed addresses you could include a ValidDate if that matters.

Hope it helps.

[cbrsix]

That's what I was thinking. Thanks.