Update CURRENT ( most recent ) record (1 Viewer)

liamfitz

Registered User.
Local time
Today, 15:19
Joined
May 17, 2012
Messages
240
The following code SHOULD update, the most recently created record in a recordset ( of a subfrom ) It doesn't, it 'skips' to the wrong record and changes that ..
Code:
Set frm = Forms!FrmNavigationLeft!NavigationSubform.Form!NavigationSubform!sfrmReferrals.Form
        frm.Requery
        With frm.RecordsetClone
            If .RecordCount > 0 Then
                .MoveLast
                .Edit
                !Client_ID = cid
                !Referral_ID = refid
                !CalcClient_Name = n
                .Update                
            End If
Is there a way using VBA to select the MOST recent record added ( which will always be the right one in this instance ) I thought Recordset.MoveLast, would take care of this by definition. Thanks in advance.:confused:
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:19
Joined
Feb 19, 2013
Messages
16,553
Updates work automatically when you move off the form so not sure why you need this code.

If it is to update some fields which need to be updated 'automatically', I would put this in the form beforeupdate event

Client_ID = cid
Referral_ID = refid
CalcClient_Name = n

ensuring you are using the name of the table field rather than the name of the control (which may be different)
 

liamfitz

Registered User.
Local time
Today, 15:19
Joined
May 17, 2012
Messages
240
Error 'This action was cancelled by an associated object' ( putting the code in the BeforeUpdate event )
 

DavidAtWork

Registered User.
Local time
Today, 15:19
Joined
Oct 25, 2011
Messages
699
Assuming you're want to UPDATE the Referrals table, do you have a field that would identify the most recent record added, either an auto id or date created, if you did then I would just use some sql which would basically say
UPDATE Referrals
SET Client_ID = cid, Referral_ID = refid, CalcClient_Name = n
WHERE Referrals.autoID = DMax(autoID, Referrals)
or
WHERE Referrals.createdDate = DMax(createdDate, Referrals)

This in very simple terms and would require proper object names for cid, refid, n and the correct syntax according to their data types
David
 

pr2-eugin

Super Moderator
Local time
Today, 15:19
Joined
Nov 30, 2011
Messages
8,494
No no.. Liam what CJ_London meant was to put the code he/she provided into the BeforeUpdate() not the code you have..
 

liamfitz

Registered User.
Local time
Today, 15:19
Joined
May 17, 2012
Messages
240
Thanks to all contributors. Using the
If Me.Dirty = True Then Me.Dirty = False, in the relevant Control's ( Referral_Date ) Dirty event, it works a treat. Thanks.
 

mdlueck

Sr. Application Developer
Local time
Today, 11:19
Joined
Jun 23, 2011
Messages
2,631
Is there a way using VBA to select the MOST recent record added ( which will always be the right one in this instance ) I thought Recordset.MoveLast, would take care of this by definition.

Rather than use a cursor and GUI controls / or a RecordSet object, I would attempt to perform the "Update CURRENT ( most recent ) record" in pure SQL.

Air code:
Code:
SELECT TOP 1 [id]
FROM [table]
WHERE (criteria)
ORDER BY (column to determine order) DESC;
Then with the [id] of the record you need to update, Air code:

Code:
UPDATE [table]
SET [Client_ID] = ?,
[Referral_ID] = ?,
[CalcClient_Name] = ?
WHERE [id] = ?;
You can learn how to execute SQL with VBA code in the following threads:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Example of SQL SELECT using ADODB.Recordset object to Access FE temp table to scan the FE temp table and perform operations
http://www.access-programmers.co.uk/forums/showthread.php?p=1214730 #post1214730
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:19
Joined
Feb 19, 2013
Messages
16,553
Assuming you want the record just inserted then:

Dim rst as recordset

Codedb.Execute("INSERT ......

'Then to get the record just inserted

Set rst = CodeDb.OpenRecordset("SELECT @@Identity as lastID")

Form.Filter="[AutonumberField]=" & rst.Fields(0)

If you wanted the last record inserted whenever, then you need a timestamp field with the default set to Now()

in which case your sql is:

SELECT * FROM Table1 WHERE TimeStamp=(SELECT Max(TimeStamp) From Table1 as Tmp)
 

Users who are viewing this thread

Top Bottom