Append and Delete via VBA

[Guest]

I need to have an Event Procedure that will run when button is clicked.

It needs to take the current record displayed on the form from Table1 and
append it Table2 THEN DELETE crrent record from Table1.

I am lost.

Please help.

 

[Guest]

Hi Ed,
Here is something you can try:

Create an append query from Table1 to Table2 and include a column for the
Unique ID Field from column 1 with a parameter, i.e [ID?]

on your on Click event, you can add:

dim qdf as QueryDef

set qdf = currentdb.QueryDefs("Your append query name")
qdf.Parameters("[ID?]") = Me.ID
qdf.Execute

assuming ID is you Unique ID field and it exists in your form.
Does it help?

Take care
Mauricio Silva

 

[Guest]

Here is some untested air code that might help

Dim rst1 as Recordset
Dim rst2 as Recordset

rst1 = Currentdb.OpenRecordset("Table1", dbOpenDynaset)
rst2 = Currentdb.OpenRecordset("Table2", dbOpenDynaset)

With rst1
.FindFirst "MyKeField = '" & Me.MyKeyFieldControl & "'"
If .Nomatch Then
MsgBox Me.MyKeyFieldControl & " Is not in Table 1"
Else
With rst2
.FindFirst "MyKeField = '" & Me.MyKeyFieldControl & "'"
If Not .NoMatch Then
"MsgBox Me.MyKeyFieldControl & " Is Already in Table 2"
Else
.AddNew
.Fields(0) = rst1.Fields(0)
.Fields(1) = rst1.Fields(1)
.Update
End With ' rst2
.Delete
End If
End If
End With ' rst1

rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing

 

[Tim Ferguson]

It needs to take the current record displayed on the form from Table1
and append it Table2 THEN DELETE crrent record from Table1.

' make sure the database is up to date
'
docmd.runcommand acSaveRecord

' copy the record to the new table: this is a
' plain & simple insert query
'
' I'm pretending that field One is the primary key and
' it's a numeric value. YMMV
'
jetSQL = "insert into table2 (field1, field2, field3) " & _
"select one, two, three from table1 " & _
"where one = " & me.txtFieldOne.Value

' carry it out
'
currentDB().Execute jetSQL, dbFailOnError

' now delete the current record
'
jetSQL " delete from table1 " & _
"where one = " & me.txtFieldOne.Value

' and carry it out
'
currentDB().Execute jetSQL, dbFailOnError

' do something sensible with the form
' because you don't want it left on a deleted record
'
DoCmd.RunCommand acMoveNext



Meanwhile, I strongly suspect a design problem: it's not usually a Good
Thing even to have two tables with the same field structure.

Hope that helps


Tim F