In Access VB Find a record based on another record.

Posted on 2013-06-01
Last Modified: 2013-06-05
Hi Expert,
In a MS Access Event Procedure I am reading the contents of a table and need to update the corresponding records in another table.  I can update the first record but the remaining don't update I believe because I am not finding the corresponding record to update and the one that does update is just the first record in the table.

The current code with an explanation at the top is attached.  I do not want to use SQL for the update.

I have spent a few hours trying to solve this on my own.

Bob Collison.
Question by:Bob_Collison
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 50 total points
ID: 39213409
A sample db would make it easier to answer your question.  They way I generally approach your task is with 2 recordsets.  I first set a unique recordset from table 1 containing all keys that you want to update in table 2.  I then set a second recordset on table 2 based on the current key in table 1.  You will Loop through recordset 1 setting recordset 2 then loop through recordset 2 to update all key values that match recordset 1.

LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 300 total points
ID: 39213418
Here is what I would do:

1.  Use shorter aliases for your recordsets and variables.  It is really nice to be able to see all of the for a single action on a single line
2.  Indent everything inside loops.  Makes it easier to read.
3.  You never use the Recordset.Find method to find the record you are looking for.  That is why you keep updating the first record in RecordSet_40_EVENT_GROUP_MSTR_354
4.  Declare (Dim) your variables and objects at the beginning of your subroutines and functions, not in line
5.  You don't need to use the Recordsets "Fields" property to refer to a field, use the ! instead (see below)
6.  If a recordset is going to refer to the same table or query, opening and closing the recordset inside a loop creates a lot of overhead.  Put it outside the loop if you can.

'Box "Step 354 - Update  Event Group Master [40_EVENT_GROUP_MSTR] Table Data."

Dim DB_354 As DataBase
Dim RS_40_EVNT_GRP_MSTR_354 As Recordset

Set DB_354 = CurrentDb
Set RS_40_EVNT_GRP_MSTR_354 = DB_354.OpenRecordset("40_EVENT_GROUP_MSTR_IMP")

'---> Find the record to update here

'---> Check to make sure the record was found.  Only update if the record was found.
'---> If not found, display a message
     IF RS_40_EVNT_GRP_MSTR_354.NoMatch then
         msgbox "record not found for [Period_Event_Grp_Reg_Key] : " & PARM_PERIOD_EVENT_GRP_REG_KEY_354
          On Error Resume Next 'Mandatory.
     End IF

Open in new window

LVL 29

Assisted Solution

IrogSinta earned 20 total points
ID: 39213456
You have a very unconventional way of naming your recordsets.  It makes more sense to have shorter names for clarity.  As a programmer, you would know at the start of your code what object the recordset name refers to; seeing these long names over and over make it more confusing, not to mention, more typing, and more memory used for coding.  Your code would be more readable this way:
Dim db As Database
Dim rs353 As Recordset
Dim rs354 As Recordset

Set db = CurrentDb
Set rs353 = db.OpenRecordset("NameOfTableFor353")
Set rs354 = db.OpenRecordset("40_EVENT_GROUP_MSTR_IMP")

Do Until rs353.EOF

Set rs353 = Nothing
Set rs354 = Nothing

Open in new window

Note: the code above is NOT the answer to your question.  It is just to give you an example of how much better your code could be read.  The reason I didn't answer your question is I'm not sure what you're trying to do here.  I'm assuming you're trying to update the data in one table with the corresponding data in the other table.  But if so, why don't you want to use SQL to do this?  It would be simpler and run faster.  Can you give a clearer explanation including what tables are involved and if these tables are in the same database?
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.


Author Comment

ID: 39214232
Hi Experts,
First, thanks for the formatting / convention suggestions.  I have incorporated them.
Secondly thanks for the code suggestions.  I have also incorporated them.
However now when I run the code  only the First Record is updated and it is updated with the data from the Last Record.  Records 2-4 are not updated.

I am attaching a copy of the current code and have included the code for both the Source and Destination Tables. to provide better picture.
I can provide the actual .msd but would have to remove some other stuff from it.
Thanks.  Bob C.
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 300 total points
ID: 39214387
If you have more than one record in the 354 table that relates to a single record in the the outer table, the I would recommend a SQL Update query to update the 345 records   Am working from my iPad but will try to work on that when I get home.
LVL 38

Accepted Solution

Jim P. earned 130 total points
ID: 39214416

It was very hard to read you original code. I think I have the queries right.

My suggestion is that you don't try to update Table2 from Table1 using record sets for both. Even if you switch back to ADO from DAO the code will still work. Use this as a framework. All you have to do is change opening query and the update query. Then you can comment on what you're doing in that portion.

Public Function Q_28144978()

'- Field PERIOD_EVENT_GRP_REG_KEY is the unique key for both Tables.
'- There are four records each with the same key in each table in the same order in the Tables.
'- The problem is that the first record is being updated with the data from the last record.
'- Records 2 to 4 don't get updated.

Dim DB As Dao.Database
Dim RS As Dao.Recordset

Dim SQL As String

'MsgBox "Step 353 - Read Event Group Master Transfer [40_EVENT_GROUP_MSTR_XFER] Table."


Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL, adOpenKeyset, adLockOptimistic)

'MsgBox "Step 354 - Update  Event Group Master [40_EVENT_GROUP_MSTR_IMP] Table Data."

Do Until RS.EOF = True

        "SET LKUP_LONG_NAME = " & char(34) & RS!LKUP_LONG_NAME & char(34) & " " & _
        "WHERE  [PERIOD_EVENT_GRP_REG_KEY] = " & char(34) & RS!PERIOD_EVENT_GRP_REG_KEY & char(34) & ";"
   DoCmd.SetWarnings False
   DoCmd.RunSQL SQL, False
   DoCmd.SetWarnings True

Set RS = Nothing
Set DB = Nothing

End Function

Open in new window

Note that the Set RS = Nothing destroys the RS object. If you are going to open another RS in the same function just do an RS.Close instead. You don't have to close and re-open the DB object.

Author Comment

ID: 39214522
Hi Experts,
I'll take a look at it in a while.  I have to go out.
Please note that there will always be only one instance of a record for the Unique Key [PERIOD_EVENT_GRP_REG_KEY] in both the tables.  However there may be more records in the  the [40_EVENT_GROUP_MSTR_IMP] Table.
Thanks.  Bob C.

Author Comment

ID: 39215179
Hi Experts,
I have been trying various things and I have narrowed down the problem with the code I sent to the 'FindFirst' Code.  It doesn't seem to be working.  i.e. It is doing nothing.  Therefore the existing record that I am trying to 'Find' isn't being found.
Is there a problem with it's syntax and / or is there different code to 'Find' the record?
Thanks.  Bob C.
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 130 total points
ID: 39215213
Using the search/seek functionality in the second recordset will always have issues. In addition you are loading a query of two fields from one table and the full second table into memory when there is no need to.

Running sequential update statements takes very little memory and is generally faster.

Author Closing Comment

ID: 39224271
Hi Experts,
I am going to go with the SQL Solution but I feel that fyed appeared to put the most work into addressing my issue.

I appreciate the work / suggestions from all of you including the 'Standards' suggestions!

Thanks again.
Bob Collison

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question