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
154 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
[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
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

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 50

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 37

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

738 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