DoCmd.RunSQL Update Last Record

[GendoPose]

Hi All,

I have a button that runs a line of UPDATE SQL, but I want it to update the last record. How would I go about this?

I assume it's part of the where condition but I'm not sure how I'd achieve it. The code I have so far is this;

DoCmd.RunSQL "UPDATE [MASTER PLANNER] SET [MASTER PLANNER].[USER] = Forms!frmLogin!cboUser.Column(0) WHERE (last record)"

Thanks all!

 

[pr2-eugin]

Define last record. If you go to a Table view navigate to the last record; if this is your idea about last record then you are wrong.

 

[GendoPose]

Define last record. If you go to a Table view navigate to the last record; if this is your idea about last record then you are wrong.

Last record meaning the newest one, the record with the highest Primary Key number.

I've changed my SQL Statement to this;

DoCmd.RunSQL "UPDATE [MASTER PLANNER] SET [MASTER PLANNER].[USER] = Forms!frmLogin!cboUser.Column(0) WHERE [MASTER PLANNER].ID = (SELECT MAX([ID]) FROM [MASTER PLANNER])"

but it seems the VBA stops working at that line, any ideas?

This is the whole sub;

Private Sub Command5390_Click()
    If MsgBox("Approve this job?", vbYesNo, "DST PLANNER") = vbYes Then
        DoCmd.OpenQuery "qryApproveRequest"
        Me.Approved = True
        DoCmd.Save
        Me.Dirty = False
        DoCmd.RunSQL "UPDATE [MASTER PLANNER] SET [MASTER PLANNER].[USER] = Forms!frmLogin!cboUser.Column(0) WHERE [MASTER PLANNER].ID = (SELECT MAX([ID]) FROM [MASTER PLANNER])"
        If MsgBox("View job?", vbYesNo, "DST PLANNER") = vbYes Then
            DoCmd.Close acForm, "PlanningProductionApproval"
            DoCmd.OpenForm "Planner Query"
            DoCmd.GoToRecord acDataForm, "Planner Query", acLast
        Else
            DoCmd.CancelEvent
        End If
    Else
        DoCmd.CancelEvent
    End If
End Sub

 

[pr2-eugin]

Try,

CurrentDB.Execute "UPDATE [MASTER PLANNER] SET [MASTER PLANNER].[USER] =[COLOR=Red][B] '" &[/B][/COLOR] Forms!frmLogin!cboUser.Column(0) [COLOR=Red][B]& _[/B][/COLOR]
                  [COLOR=Red][B]"'[/B][/COLOR] WHERE [MASTER PLANNER].ID = (SELECT MAX([ID]) FROM [MASTER PLANNER])"

 

[GendoPose]

Try,

CurrentDB.Execute "UPDATE [MASTER PLANNER] SET [MASTER PLANNER].[USER] =[COLOR=red][B] '" &[/B][/COLOR] Forms!frmLogin!cboUser.Column(0) [COLOR=red][B]& _[/B][/COLOR]
                  [COLOR=red][B]"'[/B][/COLOR] WHERE [MASTER PLANNER].ID = (SELECT MAX([ID]) FROM [MASTER PLANNER])"

That's got it, thank you!

 

[pr2-eugin]

No problem ! Good Luck !