?
Solved

Append to same table

Posted on 2012-09-20
22
Medium Priority
?
367 Views
Last Modified: 2012-09-24
I have a Project form with a subform that store items for each project. For various reasons the query for the subform is made from two tables with a one to one relationship linked on ItemNumber.

I want to be able to copy and append all the current project's subform data into the same tables. I have all the code for appending the project but I need code/query to say copy and append all of the current data in (subform) table 1 and table 2 maintaining a link between the two tables. The link fields are Table 1 ItemNumber (Incremental Number) and ItemNumber (Long Integer) in table 2.

How on earth do you do that?
0
Comment
Question by:DatabaseDek
  • 14
  • 8
22 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38419357
You can use code like this:
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Table1")
Set rst1 = CurrentDb.OpenRecordset("Table2")
rst.AddNew
rst1.AddNew
rst1!ItemNumber = rst!ItemNumber
' Fill other fields
rst.Update
rst1.Update
rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing

Open in new window

0
 

Author Comment

by:DatabaseDek
ID: 38419608
Thank goodness. I thought it was impossible. I will try that tomorrow and reply.

thank you.
0
 

Author Comment

by:DatabaseDek
ID: 38424443
When you say fill other fields, how do you do that Is It

Field 1
Field 2 etc or

Field 1 = something
or do I enter the sql from an append query or do I somehow run a query?

Can I run a query to do this or can it only be done with code? I have 200 columns in the table?. The table hold details of a very detailed specification for a single product.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DatabaseDek
ID: 38424448
This is the code to run a query for appending to one of the tables. I have taken out most of the other fields for clarity


INSERT INTO Item ( ItemNumber, ItemQuantity, Discription, Each, FireRating, DH, LDW, Thickness, RDW, DC, FLC, FTC, OPH, Veneer, [Lipping Possition], Lippings, Finish, Handing, Rebates, DoorNumbers, [Note], Item.OPRV, Item.DRV, Item.DoubleAction, Item.FormLocked
FROM Item
WHERE (((Item.AccountCode)=[Forms]![ProjectForm].[Tag]));
0
 
LVL 40

Expert Comment

by:als315
ID: 38424746
If you have all data from table Item on your form (fields could be hidden), you can set values as follows:
rst!ItemQuantity = Me.ItemQuantity
rst!Discription = Me.Discription
......
You can also try to use update query, but if duplicates are allowed in field AccountCode, it will not be simple.
0
 

Author Comment

by:DatabaseDek
ID: 38424836
If we go back to your original idea how do I "AddNew" all the other fields in the tables
0
 

Author Comment

by:DatabaseDek
ID: 38424886
Let me rephrase that. Will your solution only work with fields on a form. Can they be copied from the tables?
0
 

Author Comment

by:DatabaseDek
ID: 38424914
Would it help if I broke the referential integrity between table1 and Table2 so we could add records and only link them in the forms query?
0
 
LVL 40

Expert Comment

by:als315
ID: 38425026
I should look at your tables. If you can have many records with same AccountCode in table Item, you will not be able to update table with query (may be some intermediate temporary table can solve problem). May be you can upload these 2 tables and form with some dummy data?
0
 

Author Comment

by:DatabaseDek
ID: 38425124
Hi

Thanks for your patience. It took a while. Warning, It's not pretty

Thanks
db1.zip
0
 
LVL 40

Expert Comment

by:als315
ID: 38425816
As I can see, you most values are on a form, but you can use alternate method with queries (look at sample).
You may have problems if your DB is multiuser and users will simultaneously add new records to same AccountCode. I've prepared sample in Access 2010, may be you will need to modify references in VB Editor.
db001.mdb
0
 

Author Comment

by:DatabaseDek
ID: 38426175
Hi

Thank you very much.

But what I am trying to do is copy the entire project. ie. all 17 records to create a new project.
Here is the code for the project stuff but how do we copy All 17 records to the two tables? You can see the query that i am trying to run at the end of the code. This is where I expected to put your original code.

What I need is: Get the first record in the table  where Account code is [Tag]  copy it to the same table, now get the first record from table two and Add it to Table2 and insert the ItemNumber from Table1's new record
Next Record
This is the code for copying Parent record:

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim F As Form

' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set rst = Me.RecordsetClone

On Error GoTo Err_btnDuplicate_Click

' Tag property to be used later by the append query.
Me.Tag = Me![AccountCode]

' Add new record to end of Recordset object.
With rst
   .AddNew
     
      !CustomerName = Me!CustomerName
      !ProjectRef = Me!ProjectRef & " B"
      !Phone = Me!Phone
      !Fax = Me!Fax
      !Email = Me!Email
      !Ref = Me!Ref
      !OrderNumber = Me!OrderNumber
      !CustomerOrderNumber = Me!CustomerOrderNumber
      '!DateCreatedProject = Me!TodaysDate
     
   .Update                     ' Save changes.
   .Move 0, .LastModified
End With
Me.Bookmark = rst.Bookmark

' Run the Duplicate Order Details append query which selects all
' detail records that have the OrderID stored in the form's
' Tag property and appends them back to the Item table with
' the OrderID of the duplicated main form record.


        DoCmd.OpenQuery "DuplicateOrderDetails", acNormal, acEdit
0
 

Author Comment

by:DatabaseDek
ID: 38426177
I don't think I put the Tag field in the sample db. Not even sure if it is required.
0
 
LVL 40

Expert Comment

by:als315
ID: 38426357
You could use same algorithm for adding new project, but you have Random number for project number (why?) and it is not so simple to find recently added project.
Try this sample (it is combination of adding methods)
db001-1.mdb
0
 

Author Comment

by:DatabaseDek
ID: 38426716
Really sorry I do not seem to be able to make myself clear. If a customer wants to copy an entire Project with all of the records in the subform how do we do it.

Look at project AA4098 It has 17 items. We need a copy project ie. AA4098 B and all 17 record in the original subform. This is because sometimes they need to offer the same project but where only one field in each item record has a change. They do not want to re-enter all 200 odd details when 199 of them are the same. The only change between the two projects is that each item may be blue and the other white

Help!
0
 

Author Comment

by:DatabaseDek
ID: 38426721
Sorry forgot Sample
db001-2.mdb
0
 

Author Comment

by:DatabaseDek
ID: 38426746
I used Random for Project AccountCode because I thought that at some time in the future that I may need to manually move projects from one Dbase to another. I thought, wrongly I guess, that it would take the random numbers where as if incremental there may be duplicates in the original.
0
 
LVL 40

Expert Comment

by:als315
ID: 38427782
Is it possible to have many records in table VisioPanels with one ItemNumber? If no - you should set ItemNumber as PK in VisionPanels.
Test sample
db001-3.mdb
0
 

Author Comment

by:DatabaseDek
ID: 38428242
Hi Als315

If only you knew how you have saved my life here!!!
Brilliant!

Not sure how I will get on applying it to the finished product, but please accept my thanks for your help. I wish I understood how you did it but as long as I can get it to work I will be happy. Thanks again for your patients.

Oh and I noticed that you have not changed the VisionPanel ItemCode to PK why do you say it needs it? And is there any system in Experts Exchange for me to pay you for future work?

What is CLng in CLng([Forms]![Form]![AccountCode]) for Is it Long Integer?
Are these Relationships your work or is it something to do with a distant synchronisation i did years ago? If they are not required how do you get rid of them. They are haunting me.
Relationships
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 38428456
I didn't add  system tables to relationships.
I've added 2 fields to form "Form" - txtAccountCode and txtItemNumber - there are saved old AccountCode and old ItemNumber. I think you need only one - txtAccountCode.
I've added also one field to table Item (OldItemNumber). It is used in add query.
0
 

Author Closing Comment

by:DatabaseDek
ID: 38428695
It was obviously something too difficult for most experts.

Top Notch stuff.

Thank you again
0
 
LVL 40

Expert Comment

by:als315
ID: 38428762
You are welcome
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question