Solved

Trying to update a record in a table based on  the value of a field in a query

Posted on 2014-09-15
6
151 Views
Last Modified: 2014-09-18
I'm trying to update the value of a field in a table (the last record written to the table) based on the value of a field in a query.  Here's my code which is not working:  (It gets stuck on the RS.FindLast line of code)

    Dim RS As DAO.Recordset
    Set RS = CurrentDb.OpenRecordset("tblMemberRunningNotes")
    RS.Edit
    RS.FindLast [MembRunNotesID] WHERE [MemberID] = DLast("updqryUpdateIHMGnotes", "MemberID")
    RS!MemberID = DLast("[MemberID]", "tblIHMGNotes")
    RS!RunningNote = DLast("[Running Note]", "tblIHMGNotes")
    RS.Update

Open in new window


What is wrong with this?


--Steve
0
Comment
Question by:SteveL13
6 Comments
 
LVL 12

Expert Comment

by:Ramkisan Jagtap
ID: 40323619
Try with changing line 2 as below:

Set rstTclient = Db.OpenRecordset("tblMemberRunningNotes", dbOpenDynaset)
0
 
LVL 12

Expert Comment

by:Ramkisan Jagtap
ID: 40323636
Try with changing line 2 as below:

Set RS = CurrentDb.OpenRecordset("tblMemberRunningNotes", dbOpenDynaset)
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 40323637
Well, I've never seen the DLast function before, and I don't think it is what you need in this situation. From Access help:

You can use the DLast function to return a random record from a particular field in a table or query when you simply need any value from that field. .

Are you looking for a random record? It doesn't seem that you are.

First, find the value from the query. DLookup can be used for that purpose.

dim v as variant
v = dlookup("MyFieldName","MyQueryName","Some Criteria")

Open in new window


Next, find the "last" record written to your table. You'll need some way to identify this last record, however. If you have an autonumber primary key, then this will be the record with the highest primary key value.

dim LastRecordIDValue as long
LastRecordIDValue = dmax("MyPrimaryKeyFieldName", "MyTableName")

Open in new window


Now do the update

currentdb().execute "Update MyTable set MyField = " & v & " Where MyPrimaryKeyFieldName = " & LastRecordIDValue & ";", dbseechanges + dbfailonerror

Open in new window


... or of MyField is a text field, then

currentdb().execute "Update MyTable set MyField = " & chr(34) & v & chr(34) &  " Where MyPrimaryKeyFieldName = " & LastRecordIDValue & ";", dbseechanges + dbfailonerror

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 34

Expert Comment

by:PatHartman
ID: 40323675
"Last" assumes some defined sequence but by definition, tables and queries are unordered sets.  So, to ensure that a recordset is in some defined sequence, you MUST specifically sort it and that means you can only open it with a query.  You can never rely on the row order of a table based recordset although, if you just compacted the database and didn't do ANY updates after the fact, the table should be in primary key sequence.  Once you have updated the table, all bets are off which isn't to say that Access randomizes the records, only that if an updated record cannot be written back to its former slot on the disk because it is too large, Access has to put it somewhere else.  So, the more updates you have done since the last compact, the more displaced records you will have.  If you understand what defrag does to your hard drive, compact does something similar to the rows in a table.  It puts them in the specified order, one after the other physically in a predictable sequence.

It also looks like you are using an update query (if I can believe your naming convention) in the DLast() function.  You CANNOT use Action queries as a substitute for Select queries.  When you want to reuse queries like this, the best solution is to separate the action from the select.  Save the select part of the query along with the criteria.  Then use that query as the base for the action query rather than directly referencing the table.

So, rather than DLast(), you probably need to use DMax() to get the ID of the most recently written record and then DLookup() to find the data field associated with the ID you found with DMax().  That said, the concept is flawed in a multi-user environment because multiple people are adding to the table and so if you are expecting to retrieve the record the current user just wrote for the current member, you may be disappointed when you get a record for some random member because someone else was updating at the same time.

Tell us more about the process and why you are copying data from one table to another.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 40323703
You probably want something like this:

    Dim lngMemberID As Long
    Dim RS As DAO.Recordset
   
    Set RS = CurrentDb.OpenRecordset("tblMemberRunningNotes")
    lngMemberID = DLast("updqryUpdateIHMGnotes", "MemberID")
    RS.FindFirst "[MembRunNotesID] = " & lngMemberID & ""
    If Not RS.NoMatch Then
        RS.Edit
            RS!MemberID = DLast("[MemberID]", "tblIHMGNotes")
            RS!RunningNote = DLast("[Running Note]", "tblIHMGNotes")
        RS.Update
    End If

Just guessing.

/gustav
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40323853
I forgot that sorting in a query still does not fix the Dlast/DFirst problem.  The answer is don't use them.  They simply do NOT do what you think they should do.

Here is an archived KB article that actually does a better job than anything newer of explaining the issue.

http://support.microsoft.com/kb/109380
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

932 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

10 Experts available now in Live!

Get 1:1 Help Now