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
152 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 35

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 35

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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 …

776 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