changing message box?

[PARSO]

i have a code and so on for archiving old records which somebody provided me with.
but the code (shown below) only provides users with the name of the record being deleted and no options to cancel the proceedure. i would also like to add a message such as are you sure you wish to archive 'XXX' record.

can any1 help me change the code?

Private Sub Command0_Click()
'MsgBox Me!Subform1.Form!ControlName
MsgBox Me!planssubform.Form!PortName


Dim myID As Integer, SQL1 As String, Sql2 As String

myID = Me!planssubform.Form!ID


SQL1 = " INSERT INTO history ( portname, planexpiry, comments ) SELECT plans.PortName, plans.PlanReapprovalDate, plans.Coments FROM plans WHERE plans.ID= " & myID

Sql2 = " DELETE plans.ID FROM plans WHERE plans.ID=" & myID


DoCmd.SetWarnings False
DoCmd.RunSQL (SQL1)
DoCmd.RunSQL (Sql2)
DoCmd.SetWarnings True

Me!planssubform.Form.Requery
Me!historysubform.Form.Requery

Me.Form.Refresh
End Sub

Thanks in advance

Parso

 

[Simplycongy]

Hi Parso

What you need to do is make your Msgbox an OkCancel box, and use an if statement to jump to the end of the code if they hit cancel...

something like this

if MsgBox ("Are you sure you wih to delete " & Me!planssubform.Form!PortName,"Do you wish to proceed with delete", vbokcancel) = vbcancel then Goto ExitSub

and put a label in your code before your End Sub like this

ExitSub:
Exit Sub

HTH

 

[ghudson]

Try this...

    If MsgBox("Are you sure you want to delete the '" & Me!planssubform.Form!Id & "' record?", vbQuestion + vbOKCancel, "Delete Current Record") = vbCancel Then
        'user clicked Cancel
        MsgBox "Delete action aborted!"
        Exit Sub
    Else 'user clicked OK
        Dim myID As Integer, SQL1 As String, SQL2 As String
        
        myID = Me!planssubform.Form!Id
        
        SQL1 = " INSERT INTO history ( portname, planexpiry, comments ) SELECT plans.PortName, plans.PlanReapprovalDate, plans.Coments FROM plans WHERE plans.ID= " & myID
        SQL2 = " DELETE plans.ID FROM plans WHERE plans.ID=" & myID
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL (SQL1)
        DoCmd.RunSQL (SQL2)
        DoCmd.SetWarnings True
        
        Me!planssubform.Form.Requery
        Me!historysubform.Form.Requery
        
        Me.Form.Refresh
    End If

 

[PARSO]

THANKS
Works a treat guys thank you very much