UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Finding Matching Records In Another Table., Access 2016    
 
   
InfoHound
post Nov 1 2017, 10:59 AM
Post#1



Posts: 1,057
Joined: 1-December 12



I want to update records in one table based on records in another.

I've always use a do loop construct to do this, but, I'm wondering if using a Do ...Loop and docmd or SQL statement is quicker?

this is what I'm trying:
CODE
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

On Error GoTo ErrHan

Set dbs = CurrentDb

Set rs = dbs.OpenRecordset("tblIsleInfo")
Set rs2 = dbs.OpenRecordset("tblAisleWithProductInfo")
rs.MoveFirst
rs2.MoveFirst

Do
    docmd.SearchForRecord,[tblAislewithProductInfo],acFirst,rs![storagelocation]& " =  " & rs2![StorageLocation]



    rs.MoveNext
Loop While Not rs.EOF




ExitErr:
    Set dbs = Nothing
    Exit Sub
ErrHan:
    MsgBox Err.Description, vbInformation, "Error "
    Resume ExitErr
End Sub


Do you thing I'm on the right track or should I
Go to the top of the page
 
Doug Steele
post Nov 1 2017, 02:51 PM
Post#2


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Using a query that joins the two tables will likely be significantly faster.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 03:12 AM