In Access VB Find a record based on another record.

Posted on 2013-06-01
Medium Priority
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
  • 4
  • 2
  • 2
  • +2
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 150 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 49

Assisted Solution

by:Dale Fye
Dale Fye earned 900 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 60 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?
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


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 49

Assisted Solution

by:Dale Fye
Dale Fye earned 900 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 390 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 390 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

599 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