How to update a pointer from one table to another

[petrol2]

I have a table containing an Address list, in which the name, street address, phone numbers etc. are spelt out in full but the suburb is recorded simply as a pointer to a Suburbs table containing the suburb name, state and postcode. Because of major changes it has now been necessary to replace the Suburbs table with a new one in which the key fields (Suburb_ID) are different from the previous version, so I need to replace all the pointers in the Address table.

I have made a lookup table containing for each suburb its old Suburb_ID (OSID) and the new one (NSID).

I have created a Sub to loop through the Address table, but I can't get the replacement to work. I tried a query in which the Address table and the Lookup table were linked, but that didn't work. My latest attempt uses a DLookup of the Lookup table, but that doesn't work either. Can anyone suggest a better way?

PS. Here is the code for my latest attempt. The address table is called "Venues" and the lookup table is called "ConvertSuburbID".

Private Sub Command0_Click()
 
Dim rs As DAO.Recordset, TempID As Long
Set rs = CurrentDb.OpenRecordset("Venues")
 
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
        rs.Edit
        TempID = rs!Suburb_ID
        rs!Suburb_ID = DLookup("NSID", "ConvertSuburbID", "OSID=TempID")
        rs.Update
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If
 
rs.Close                      'Close the recordset
Set rs = Nothing              'Clean up
End Sub

[NeoPa]

You should be able to do this in a single UPDATE query.

Here's my guess at what you need. This is only possible in as far as you've included the correct details in your question with which to work (Well done) :

UPDATE [Venues]
       INNER JOIN
       [ConvertSuburbID]
    ON [Venues].[Suburb_ID]=[ConvertSuburbID].[OSID]
SET    [Venues].[Suburb_ID]=[ConvertSuburbID].[NSID]

[petrol2]

Brilliant!
I had tried an update query before I tried the Sub, but had obviously created it wrongly and couldn't get it to work.
Thank you very much for your help.
Peter.
(By the way, I am also the user "petrol" (Peter O'L) from 2017. I changed my email address and forgot the old password, so I had to create a new user. The old one can now be deleted.)

[NeoPa]

You're welcome Peter.

Petrol2:
By the way, I am also the user "petrol" (Peter O'L) from 2017. ... The old one can now be deleted.

I thought you probably were. Not that it's as simple as you seem to imagine. We can't just delete accounts.
I'll see what I can get done for you. It's probably much better to re-use the old account than to work with the new one. I'll get back to you.

[NeoPa]

The owner has said he'll look into this for you Peter. Look out for emails in either account or a PM in here. He may need extra info from you first before he can fix it.

I don't need to warn you about sharing any info in the forum area. His ID's Niheel and you can be confident of sharing any info with him as long as it's in the PM area.

[petrol2]

Many thanks for that, NeoPa. But do tell Niheel not to waste too much of his precious time on it. The new account works just as well, so I guess the only benefit of merging them is to get the statistics right. Is this important?

By the way, I had to close the old email account because of unavailability of the ISP when we moved house. So I'm no longer able to access anything sent to the optusnet address.

[NeoPa]

Yes. Statistics are important. Niheel will set the new address to the old account and allow you, and everyone else, to continue to work with you on your old account.

Sometimes we look up the history of a poster to get an idea of what we're likely to be dealing with. Information can always be helpful.

PS. I expect Niheel, when he's finished, will delete these last three posts (from #5 onwards) as they're just unrelated chatter that is irrelevant to the thread. Only when you're all sorted out though.

[petrol2]

Thanks. I'll be going overseas next Tuesday (actually to England, where you are - I live in Brisbane) so may be very slow to see and respond to any posts after that date.

[midtrams]

Thank for share us this. Nice artilce