How to append data from one table to a another table upon the deletion of the first t

[inte]

i have two tables i.e tblSalesMAIN and tblSalesMAIN1. the later is a duplicate of the formar. i have a data entry form bounded to tblSalesMAIN, i have also created a continuose form from tblSalesMAINtblSales and droped it on the data entry form i have created. My goal here is to have all the data i enter into tblSalesMAIN from the bounded form show up in the continuose form but, have it all cleared from tblSalesMAIN and the continuouse form to make room for new entry.
Now, i would like to have all the information i clear from tblSales through a button click event be appended to the duplicated table tblSalesMAIN1.

I have tried all that i know how to do, but to No avail.Below is my the code i am working on. i really your help. Thank you in advance.

Dim dbs As DAO.Database
set dbs = Current.OpenRecordset("SELECT * FROM [tblSalesMAIN]")
dbs.Execute " INSERT INTO tblSalesMAIN1 " _
& "SELECT * "
& "FROM [tblSalesMAIN];"
dbs.Close



If MsgBox("You are about to delete all records. Are you sure?", _
vbQuestion + vbYesNo) = vbNO Then
Exit Sub
End If
strSQL = "DELETE*FROM tblSalesMAIN subform2"
DoCmd.SetWarnings False
DoCmd.RunSQL
DoCmd.SetWarnings True
Me.Requery
Me.Refresh

End Sub


Private Sub cmdd_Click()
DoCmd.GoToRecord , "", acNewRec
Me.Refresh

End Sub

[PhilOfWalton]

Welcome to Bytes

Are you saying that you have a Sales entry form, and a continuous form showing a resume of the past sales entries?

When you press a Command Button on your main form after entering a new sale, you want that entry to show up on the continuous form, and have a blank Sales Entry form, ready for the next entry?

Phil

[inte]

Thanks a lot Phill. I have commented some adjustment I have made to the code, but I am still not getting it right. I hope code I have added and the zipped copy of the portion of my project will give you guys some clarification to be able to assist me.


Private Sub cmdClear_Click()
'Dim dbs As DAO.Database
'set dbs = Current.OpenRecordset("SELECT * FROM [tblSalesMAIN]")
'dbs.Execute " INSERT INTO tblSalesMAIN1 " _
'& "SELECT * "
'& "FROM [tblSalesMAIN];"
'dbs.Close


'INSERT INTO tblSalesMAIN1 [(salesID[, salesTime[, salesDate[, unitPrice[, quantity[, cashTendered[, change[, price]]]]]]])] SELECT ([tblSalesMAIN.SalesID[, tblSalesMAIN.salesTime[, tblSalesMAIN.salesDate[, tblSalesMAIN.unitPrice[, tblSalesMAIN.quantity[, tblSalesMAIN.cashTendered[, tblSalesMAIN.change[, tblSalesMAIN.price]]]]]]])

'DoCmd.RunQuery


If MsgBox("You are about to delete all records. Are you sure?", _
vbQuestion + vbYesNo) = vbNO Then
Exit Sub
End If
strSQL = "DELETE*FROM tblSalesMAIN subform2"
DoCmd.SetWarnings False
DoCmd.RunSQL
DoCmd.SetWarnings True
Me.Requery
Me.Refresh

End Sub


Private Sub cmdd_Click()
DoCmd.GoToRecord , "", acNewRec
Me.Refresh

End Sub

[PhilOfWalton]

I can't help unless you answer the questions.

Phil

[inte]

Are you saying that you have a Sales entry form? Yes

a resume of the past sales entries? No

When you press a Command Button on your main form after entering a new sale, you want that entry to show up on the continuous form, and have a blank Sales Entry form, ready for the next entry? Yes but i have done that already.

what i want to have all of the individual sales have added and are already showing in the table named tblSaleMAIN to be appended to another table named tblSalesMAIN1.

[twinnyfo]

A more important question is why would you want to do this. If the tables are identical, then don’t waste your time copying and deleting—this can cause problems if there are any errors. Instead, have a Yes/No field “Archived” that is set to True when you want to archive the records. Then, filter the second form by only those records that have been archived.

Trust me on this one: I had a design similar to yours in the past and it was nothing bu headaches. The recommended solution will help you in the long run.

Hope this hepps!

[PhilOfWalton]

So what are you going to use tblSalesMAIN1 for? Is this supposed to be a record of ALL your sales?

Phil

[inte]

Yes tblSalesMAIN1 is suppose to be a record of all sales

[inte]

I honestly would love to give your suggestion a try. But I do not understand you well enough. Also I am very limited with regards to vba

[PhilOfWalton]

Twinnyfo is spot on. My thoughts exactly that the second table is not required.

However, the way you have your table set up, there is nothing to link the individual sales together such as an OrderID. Your way, each sale is for a single item (unspecified), and one sale has absolutely no relationship to the next sale. So, the subform seems a bit superfluous.

Phil

[inte]

I agree. Ok let me try to relate the two table with an OrderID. I will let you guys know of the outcome.

Thanks a lot to you all

[inte]

Guys, I would very much love to have fresh ideas from any one. I have tried the about suggestion but it isn't good enough for my purpose, since the portion of my project in discussion here belongs a very big project, I am finding it difficult integrating it with the other parts of the project

if anyone could at least show me how to get my model to work I would be very grateful.
Thank you all in advance

[PhilOfWalton]

OK. Add a Yes/No field called "Archives" to your TblSalesMain (Default Value = 'No')

On the main form, set Allow Deletions & Allow Edits to false

Change the RecordSource of both forms to

SELECT tblSalesMAIN.*
FROM tblSalesMAIN
WHERE (((tblSalesMAIN.Archived)=False));
 

So we can only see non-archived records.

Change the code on the "cmdClear" to

Option Compare Database
Option Explicit
 
Private Sub cmdClear_Click()
 
    Dim MyDb As DAO.Database
    Dim RstSales As Recordset
    Dim StrSQL As String
 
    StrSQL = "SELECT TblSalesMain.* FROM TblSalesmain"
 
    Set MyDb = CurrentDb
    Set RstSales = MyDb.OpenRecordset(StrSQL)
 
    With RstSales
        Do Until .EOF
            .Edit
            !Archived = True
            .Update
            .MoveNext
        Loop
        .Close
        Set RstSales = Nothing
    End With
 
End Sub
 

This sets the Archived field to 'yes' for all records.

Note that at the top of every module you ever write, you should have

Option Compare Database
Option Explicit
 

If, when you compile the code, the Option Explicit is missing, the compiler will not warn of errors (Missing definitions particularly)

Phil

[inte]

I appreciate all of the help you guys have offered me. I am proud to inform you that, after a bit of tweaking here and there, I have had take worry taken away, thanks you guys.