423,680 Members | 2,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,680 IT Pros & Developers. It's quick & easy.

How to update a pointer from one table to another

P: 4
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".
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3. Dim rs As DAO.Recordset, TempID As Long
  4. Set rs = CurrentDb.OpenRecordset("Venues")
  5.  
  6. If Not (rs.EOF And rs.BOF) Then
  7.     rs.MoveFirst
  8.     Do Until rs.EOF = True
  9.         rs.Edit
  10.         TempID = rs!Suburb_ID
  11.         rs!Suburb_ID = DLookup("NSID", "ConvertSuburbID", "OSID=TempID")
  12.         rs.Update
  13.         rs.MoveNext
  14.     Loop
  15. Else
  16.     MsgBox "There are no records in the recordset."
  17. End If
  18.  
  19. rs.Close                      'Close the recordset
  20. Set rs = Nothing              'Clean up
  21. End Sub
Apr 19 '18 #1

✓ answered by 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) :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [Venues]
  2.        INNER JOIN
  3.        [ConvertSuburbID]
  4.     ON [Venues].[Suburb_ID]=[ConvertSuburbID].[OSID]
  5. SET    [Venues].[Suburb_ID]=[ConvertSuburbID].[NSID]

Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,084
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) :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [Venues]
  2.        INNER JOIN
  3.        [ConvertSuburbID]
  4.     ON [Venues].[Suburb_ID]=[ConvertSuburbID].[OSID]
  5. SET    [Venues].[Suburb_ID]=[ConvertSuburbID].[NSID]
Apr 20 '18 #2

P: 4
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.)
Apr 20 '18 #3

NeoPa
Expert Mod 15k+
P: 31,084
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.
Apr 20 '18 #4

NeoPa
Expert Mod 15k+
P: 31,084
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.
Apr 25 '18 #5

P: 4
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.
Apr 25 '18 #6

NeoPa
Expert Mod 15k+
P: 31,084
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.
Apr 25 '18 #7

P: 4
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.
Apr 25 '18 #8

P: 1
Thank for share us this. Nice artilce
Apr 26 '18 #9

Post your reply

Sign in to post your reply or Sign up for a free account.