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

Dale-

You have to tell the INSERT statement what values you want to put into the new row. Everywhere I have Me.something you need to replace with the actual name of the field you want to copy from the current row on your form. If the names match the field names you have your list, then Me.[Number] gets replaced with Me.ID - just as an example.

What you're doing with all the & concatenations is build an SQL string to pass to the database engine. The end result within the string should end up looking like:

INSERT INTO DeleteMainArchive

([ID],[Timestamp],[FirstName],[LastName],[WL or HV],[Score],[Month Due], [Nutrition Due],[Field7],[Completed],[DOB],[Phone],[Comments],[Phone2])

VALUES (1, #03/07/2014#, 'John', 'Viescas', …..)

Capiche?

Sometimes it's useful to first assign the string to a string variable, use Debug to dump it out, then pass it to db.Execute. If it crashes, you can at least go to the Immediate Window to see what's wrong with the SQL.

Dim strSQL As String

strSQL = "INSERT INTO …. blah blah " & " blah blah "

Debug.Print strSQL

db.Execute strSQL, dbFailOnError

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 Mar 7, 2014, at 4:41 PM, Dale Condon <tenn_tazz26@yahoo.com> wrote:

I am still getting the compile error expected expression. I copied the text exactly as you have below. I noticed you stated it doesn't look like I used real field names from the form. I guess this is where I misunderstand. I am using the table field name for the first part and for the value I assumed that is how the particular format of the field should be such as if it is a date field, it is a date/time value.  Do I use the table field names for the first part and the second Value section use the name that is listed in the form (which is for the most part the same name)?

From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, March 7, 2014 9:46 AM
Subject: Re: [MS_AccessPros] Create Delete button that archives the record you have open in the form

Dale-

Close, but no cigar. Your problem is the VALUES clause - you have to concatenate in the values from the form, like this:

db.Execute "INSERT INTO DeleteMainArchive " & _
"([ID],[Timestamp],[FirstName],[LastName],[WL or HV],[Score],[Month Due], " & _
"[Nutrition Due],[Field7],[Completed],[DOB],[Phone],[Comments],[Phone2]) VALUES(" & _
Me.[Number] & ", #" & Me.[Date/Time] & "#, '" & Me.[Text1] & "', '" & Me.[Text2] & _
"', '" & Me.[Text3] & "', " & Me.[Number2] & ", '" & Me.[Text4] & "', '" & Me.[Text5] & _
"', '" & Me.[Text6] & "', #" & Me.[Date/Time2] & "#, #" & Me.[Date/Time3] & _
"#, " & Me.[Number3] & ", '" & Me.[Memo] & "', " & Me.[Number4] & _
& ")", dbFailOnError

It doesn't look like you used the real field names from your form, so I just inserted placeholders. Note that I've surrounded every "text" or "memo" field with single quotes and every date/time field with #.

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 Mar 7, 2014, at 3:32 PM, Dale Condon <tenn_tazz26@yahoo.com> wrote:

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 " & _
            "([ID],[Timestamp],[FirstName],[LastName],[WL or HV],[Score],[Month Due],[Nutrition Due],[Field7],[Completed],[DOB],[Phone],[Comments],[Phone2]) VALUES(" & _
            [Number],[Date/Time],[Text],[Text],[Text],[Number],[Text],[Text],[Text],[Date/Time],[Date/Time],[Number],[Memo],[Number] & _
            & ")"" & _
            , 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

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

Dale-

What does your code look like?

If you're not providing a field list, then you must also remove the parens. And if you do not provide a field name list, you must provide values for ALL the fields in the table in the sequence in which they're defined in the table.

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 Mar 6, 2014, at 9:13 PM, Dale Condon <tenn_tazz26@yahoo.com> wrote:

I think I am still doing something wrong on my formatting. I keep getting compile errors. Based on what is stated below I remove the <list all field names here > statement since I wanted All the fields brought over. I end up with this:
db.Execute "INSERT INTO DeleteMainArchive " & _
"( ) 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

As soon as I remove that statement I get the error. I would like to not go through and place every field in this code in case down the road I add additional fields to the master table. I would then just have to add those same fields to the DeleteMainArchive table and hopefully that would work. Also I am not exactly sure I understand the Values line either. I would think that if the field names match to the table and the table has already defined the format types it would just send the data as long as I tell it which field in the table to send to.

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

Dale-

Yes, the field names need to be separated by commas.

Note that if you're providing ALL the fields required by DeleteMainArchive in the same sequence as they are defined, you don't need the field name list.

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 27, 2014, at 6:01 PM, Dale Condon <tenn_tazz26@yahoo.com> wrote:

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