?
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
Medium Priority
?
156 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 38

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 51

Accepted Solution

by:
Gustav Brock earned 1000 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 38

Assisted Solution

by:PatHartman
PatHartman earned 1000 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

762 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