By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,553 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

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

P: 9
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
Jul 4 '18 #1
Share this Question
Share on Google+
13 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Jul 4 '18 #2

P: 9
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
Attached Files
File Type: zip TAG Manager Assistant.zip (40.8 KB, 15 views)
Jul 4 '18 #3

PhilOfWalton
Expert 100+
P: 1,430
I can't help unless you answer the questions.

Phil
Jul 4 '18 #4

P: 9
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.
Jul 4 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 3,045
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!
Jul 4 '18 #6

PhilOfWalton
Expert 100+
P: 1,430
So what are you going to use tblSalesMAIN1 for? Is this supposed to be a record of ALL your sales?

Phil
Jul 4 '18 #7

P: 9
Yes tblSalesMAIN1 is suppose to be a record of all sales
Jul 4 '18 #8

P: 9
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
Jul 4 '18 #9

PhilOfWalton
Expert 100+
P: 1,430
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
Jul 4 '18 #10

P: 9
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
Jul 4 '18 #11

P: 9
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
Jul 4 '18 #12

PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1. SELECT tblSalesMAIN.*
  2. FROM tblSalesMAIN
  3. WHERE (((tblSalesMAIN.Archived)=False));
  4.  
So we can only see non-archived records.

Change the code on the "cmdClear" to

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdClear_Click()
  5.  
  6.     Dim MyDb As DAO.Database
  7.     Dim RstSales As Recordset
  8.     Dim StrSQL As String
  9.  
  10.     StrSQL = "SELECT TblSalesMain.* FROM TblSalesmain"
  11.  
  12.     Set MyDb = CurrentDb
  13.     Set RstSales = MyDb.OpenRecordset(StrSQL)
  14.  
  15.     With RstSales
  16.         Do Until .EOF
  17.             .Edit
  18.             !Archived = True
  19.             .Update
  20.             .MoveNext
  21.         Loop
  22.         .Close
  23.         Set RstSales = Nothing
  24.     End With
  25.  
  26. End Sub
  27.  
This sets the Archived field to 'yes' for all records.

Note that at the top of every module you ever write, you should have
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
If, when you compile the code, the Option Explicit is missing, the compiler will not warn of errors (Missing definitions particularly)

Phil
Jul 4 '18 #13

P: 9
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.
Jul 6 '18 #14

Post your reply

Sign in to post your reply or Sign up for a free account.