Update CURRENT ( most recent ) record

[liamfitz]

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 ..

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.

 

[CJ_London]

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]

Error 'This action was cancelled by an associated object' ( putting the code in the BeforeUpdate event )

 

[DavidAtWork]

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]

No no.. Liam what CJ_London meant was to put the code he/she provided into the BeforeUpdate() not the code you have..

 

[CJ_London]

thanks Paul!

 

[liamfitz]

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]

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:

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:

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]

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)