Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

In Access VB Find a record based on another record.

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.
Bob Collison
Bob Collison
  • 4
  • 2
  • 2
  • +2
6 Solutions
Eric ShermanAccountant/DeveloperCommented:
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.

Dale FyeCommented:
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

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?
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Bob CollisonSystem ArchitectAuthor Commented:
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.
Dale FyeCommented:
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.
Jim P.Commented:

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.
Bob CollisonSystem ArchitectAuthor Commented:
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.
Bob CollisonSystem ArchitectAuthor Commented:
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.
Jim P.Commented:
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.
Bob CollisonSystem ArchitectAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now