Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

In Access VB Find a record based on another record.

Posted on 2013-06-01
10
Medium Priority
?
413 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
[X]
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
10 Comments
 
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.


ET
0
 
LVL 48

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 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 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
    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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 48

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

Accepted Solution

by:
Jim P. earned 390 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 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.
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
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 …
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…

636 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