Solved

In Access VB Find a record based on another record.

Posted on 2013-06-01
10
357 Views
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.

Thanks.
Bob Collison.
Code-1.txt
0
Comment
Question by:Bob_Collison
  • 4
  • 2
  • 2
  • +2
10 Comments
 
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.


ET
0
 
LVL 47

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 PARM_PERIOD_EVENT_GRP_REG_KEY_354 As String
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")

Do Until RS_40_EVNT_GRP_MSTR_XFER_353.EOF
     PARM_PERIOD_EVENT_GRP_REG_KEY_354 = RS_40_EVNT_GRP_MSTR_XFER_353![PERIOD_EVENT_GRP_REG_KEY]
     
'---> Find the record to update here
     RS_40_EVNT_GRP_MSTR_354.Findfirst "[PERIOD_EVENT_GRP_REG_KEY] = '" & PARM_PERIOD_EVENT_GRP_REG_KEY_354 & "'"

'---> 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
     Else
          RS_40_EVNT_GRP_MSTR_354.Edit
          RS_40_EVNT_GRP_MSTR_354!LKUP_LONG_NAME = RS_40_EVNT_GRP_MSTR_XFER_353!LKUP_LONG_NAME
          RS_40_EVNT_GRP_MSTR_354!LKUP_SHORT_NAME = RS_40_EVNT_GRP_MSTR_XFER_353!LKUP_SHORT_NAME
          On Error Resume Next 'Mandatory.
          RS_40_EVNT_GRP_MSTR_354.Update
     End IF
     RS_40_EVNT_GRP_MSTR_XFER_353.MoveNext
Loop
RS_40_EVNT_GRP_MSTR_354.Close
RS_40_EVNT_GRP_MSTR_XFER_353.Close

Open in new window

0
 
LVL 29

Assisted Solution

by:IrogSinta
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
    rs354.Edit
    rs354!LKUP_LONG_NAME = rs353!LKUP_LONG_NAME
    rs354!LKUP_SHORT_NAME = rs353!LKUP_SHORT_NAME
    rs354.Update
    rs353.MoveNext
Loop

rs353.Close
rs354.Close
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?
0
 

Author Comment

by:Bob_Collison
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.
Code-2.txt
0
 
LVL 47

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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 38

Accepted Solution

by:
Jim P. earned 130 total points
ID: 39214416
Bob,

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()

'Notes:
'- 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."


SQL = "SELECT PERIOD_EVENT_GRP_REG_KEY, LKUP_LONG_NAME " & _
    "FROM 40_EVENT_GROUP_MSTR_XFER " & "';"

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

   SQL = "UPDATE RS_40_EVENT_GROUP_MSTR_354" & _
        "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
   
   RS.MoveNext
Loop

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.
0
 

Author Comment

by:Bob_Collison
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.
0
 

Author Comment

by:Bob_Collison
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.
0
 
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.
0
 

Author Closing Comment

by:Bob_Collison
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
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now