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
150 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
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…

746 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