Updating newer records to older ones(This sounds stupid)

[ClearlyAnIdiot]

I'll first apologise for the ridiculous title.
Anyway, my goal is to use the SQL "Update" function to set the address of a company to the previous addresses when I update something. FOr example, the fields show as
Current=A
Previous=B
Previous 2=C
When I input something to "Current", I want it to end up like this
Current=New
Previous=A
Previous 2=B

The code I'm using right now is

Private Sub RegAddress_AfterUpdate()
Dim This As String
This = "UPDATE Companies SET PrevRegAddress1='" & Me.RegAddress & "' WHERE CompanyNumber='" & Me.CompanyNuber & "'"
DoCmd.RunSQL This
End Sub

Private Sub PrevRegAddress1_AfterUpdate()
Dim That As String
That = "UPDATE Companies SET PrevRegAddress2='" & Me.PrevRegAddress1 & "' WHERE CompanyNo='" & Me.CompanyNumber & "'"
DoCmd.RunSQL That
End Sub

Any tips?

 

[cricketbird]

I'd set up the tables differently - one customer can have multiple addresses, each with a start and end date. The one without an end date is current (or you could have a separate column for "Active"). You can use the end dates to order them. This will let you keep a more complete history and be easier to query and update as well.

 

[ClearlyAnIdiot]

Ah, never mind the pre-edit post, I just got what you meant. I don't really need that for what I'm doing. Basically, I want it to copypaste things from RegAddress to PrevRegAddress1, and so on, whenever I update RegAddress. It's really just for convenience. The place I work at doesn't have to know the dates that they change, but we need the previous address to help fill out legal documents in case of a change, and a third one just in case something wrong happens.

 

[JHB]

How many previous fields in the table Companies do you need to update, (2 or more)?
Are you using an unbound form?
Because why are you using an SQL update in the RegAddress_AfterUpdate(), if bound to the table Companies it is updated automatic.

cricketbird idea is good.

 

[ClearlyAnIdiot]

I don't think I'm making myself very clear here. Sorry.
It needs to update 2 others.
It's a bound form, but my problem's not that the data isn't getting across.
The SQL isn't actually quite necessary, but I'm comfortable using it. What I'm trying to do is very petty, so that might be why you guys didn't get it.
Let's say, for example,

Company-- ABC Ltd.
RegAddress(current)-- DEF road, blah.
PrevRegAddress1(previous)-- GHI Street, blah blah.
PrevRegAddress2(2 changes ago)-- JKL Road, Blah Blah.

They then change their address to 991239.
RegAddress-- 991239
PrevRegAddress1-- DEF Road, blah.
PrevRegAddress2-- GHI Street, blah blah.

And all of that will happen upon updating only the RegAddress.

Yes, this sounds like a very dumb objective, but it's what my employer wants me to do, and I think my syntax is wrong, or there might be a better way of doing this.

 

[JHB]

Try if you can use the below:
I'm using old value because if you change an address and maybe made a typo and change it before you go next record you'll get PrevRegAddress1 updated with the address who had the typo in.

Private Sub RegAddress_BeforeUpdate(Cancel As Integer)
  Me.PrevRegAddress2 = Me.PrevRegAddress1.OldValue
  Me.PrevRegAddress1 = Me.RegAddress.OldValue
End Sub

 

[ClearlyAnIdiot]

That worked perfectly, thanks. Heh, I haven't been on Access for waay too long.

 

[JHB]

You're welcome, luck with your project.