ACCESS FEED: RE: [MS_AccessPros] Create Delete button that archives the record you have open in the form

Kamis, 27 Februari 2014

RE: [MS_AccessPros] Create Delete button that archives the record you have open in the form

I would probably change the SQL to an append query like:

Dim strSQLInsert as String
strSQLInsert = "INSERT INTO DeleteMainArchive " & _

"([Field1],[Field2],[Field3],[Field4], ... ) " & _

"SELECT [Field1],[Field2],[Field3],[Field4], ... FROM tblYourMainTable " & _

"WHERE PrimaryKeyFieldName = " & Me.PrimaryKeyFieldName

db.Execute strSQLInsert, dbFailOnError

Duane Hookom MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: tenn_tazz26@yahoo.com
Date: Thu, 27 Feb 2014 09:01:58 -0800
Subject: Re: [MS_AccessPros] Create Delete button that archives the record you have open in the form

John,

Where you have the code: db.Execute "INSERT INTO DeleteMainArchive " & _

"( <list all field names here > ) VALUES(" & _

<insert values from current record in the same sequence as the field list> & _

< be sure to put quotes ' around text values and # around date/time values > & ")"" & _

, dbFailOnError

How should I separate the field names (using a comma?) or do I use the <field1 ><field2> etc?

From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, February 24, 2014 3:58 PM
Subject: Re: [MS_AccessPros] Create Delete button that archives the record you have open in the form

Dale-

This is much easier to do in VBA.

Private Sub cmdDelete_Click()

Dim db As DAO.Database

' Set an error trap

On Error GoTo Delete_Fail

' Make sure focus is not on the "new" record

If Me.NewRecord Then

MsgBox "You cannot click Delete when you have selected the 'new' record."

Exit Sub

End If

If vbYes = MsgBox("Are you sure you want to delete " & _

Me.FirstName & " " & Me.LastName & "?", _

vbQuestion + vbYesNo + vbDefaultButton2) Then

' First copy the record

Set db = CurrentDb

db.Execute "INSERT INTO DeleteMainArchive " & _

"( <list all field names here > ) VALUES(" & _

<insert values from current record in the same sequence as the field list> & _

< be sure to put quotes ' around text values and # around date/time values > & ")"" & _

, dbFailOnError

' Now delete the record

RunCommand acCmdSelectRecord

RunCommand acCmdDelete

End If

Done:

Set db = Nothing

Exit Sub

Delete_Fail:

MsgBox "Unexpected error: " & Err & ", " & Error

Resume Done

End Sub

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 24, 2014, at 8:52 PM, <tenn_tazz26@yahoo.com> <tenn_tazz26@yahoo.com> wrote:

I am using MS Access 2007 and I am trying to figure out how to make a delete button in a form that when a user clicks on it, it will confirm if they wish to delete the record and in the prompt it will show the records FirstName and LastName field, and when they click to confirm the deletion that behind the scenes it will copy that record to an archive table before deleting it from the main table. The form name I am using is called MainForm and the table that is storing the data is called Main. The archive table I created is a copy of the Main table structure and I called it DeleteMainArchive. I thought of using a Macro in combination with an append query but never could figure out how to make the query capture the current record rather then all records.

Thanks,
Dale