473,328 Members | 1,419 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,328 software developers and data experts.

How to copy records from continuous subform to another continuous subform?

To person who may help,

I have a form with 2 continuous subform.

-Mainform is used to record the product details.

-first subform is use to record the product delivery dates.

-second subform is use to keep the previous delivery dates from first subform if we revised the new dates in first subform.

However, how to copy & paste the delivery dates from the first subform to second subform?

I had spend about 2 weeks time to find out the solution but still failed. Much appreciated if anyone can help on this.
Nov 23 '14 #1

✓ answered by Seth Schrock

You most certainly can. In your button's OnClick event, enter the following:
Expand|Select|Wrap|Line Numbers
  1. Dim strInsert As String
  2. Dim db As DAO.Database
  3.  
  4. strInsert = "INSERT INTO TblBackupDate (StockIDID, [Date For Stock A], [Date for Stock B], [Date For Stock C]) " & _
  5.             "SELECT StockID, [Date For Stock A], [Date for Stock B], [Date For Stock C] " & _
  6.             "FROM TblDeliverDate " & _
  7.             "WHERE [Delivery DateID] = " & Me.First_subform.Form.txtDeliveryDateID
  8.  
  9. Set db = CurrentDb()
  10.  
  11. db.Execute strInsert, dbFailOnError
  12. Set db = Nothing
Again you would need to replace the subform control name and the textbox control name.

15 6260
Seth Schrock
2,965 Expert 2GB
If the two subforms are based on separate tables, then I would use an INSERT query that would get its values from the currently selected record in the first subform.
Nov 24 '14 #2
Thank you for your reply;)

Yes, it is a separate form. May I know how to use the Insert Query? Also, Is it possible to copy the data by pressing a button in mainform?
Nov 24 '14 #3
Seth Schrock
2,965 Expert 2GB
I know that the forms are separate, but I need to know if the forms are based on different tables. Can you tell me what the Record Source property is for each subform?

And yes, you can do this all from a button on the main form.
Nov 24 '14 #4
Hi Seth Schrock,

The details are as below:

MAINFORM (TblStock)
Primary Key: STOCKID
STOCK DETAILS
STOCK INFO
* STOCKID from TblStock LINK TO STOCKID from TblDeliverDate & TblBackupDate (One to many relationship)

SUBFORM 1 (TblDeliverDate) :
Primary Key: Delivery DateID
STOCKID
Date For STOCK A
Date For STOCK B
Date For STOCK C

SUBFORM 2(TblBackupDate):
Primary Key: BackupID
STOCKIDID
Date For STOCK A
Date For STOCK B
Date For STOCK C

Many many thanks.
Nov 25 '14 #5
twinnyfo
3,653 Expert Mod 2GB
I would recommend using a pop-up form, such that when you click on one of the records in the first subform, it pops up, populated with the data in the selected record--but it is an unbound form, so that you can easily cancel without making any permanent changes.

You could assign values to variables for all of the existing data in that record. Then, after the user makes changes, if they want to cancel, no changes are made. But, if they want to save, then the pop up appends data to the second table (and thus the second subform), and updates the selected record on the first subform's table. Refresh both the subforms and close the pop up.

This should work.
Nov 25 '14 #6
Seth Schrock
2,965 Expert 2GB
I think that it would be better if instead of having a backup date table, add another field to tblDeliveryDate that marks the date as old. Also, it isn't a good idea to have separate fields for StockA, StockB, and StockC because you might need to add StockD in the future. If you have a normalized database, no redesigning would be necessary to add StockD to the list. See Database Normalization and Table Structures for more information about proper table design.

Back to copying the record... So basically, you would need an INSERT query to copy the record from the first table to the second.
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TblBackupDate (StockIDID, [Date For Stock A], [Date for Stock B], [Date For Stock C])
  2. SELECT StockID, [Date For Stock A], [Date for Stock B], [Date For Stock C] 
  3. FROM TblDeliverDate
  4. WHERE [Delivery DateID] = " & Me.First_subform.Form.txtDeliveryDateID
You would need to insert your own subform control name and textbox control name, but that would copy the record to the new table.
Nov 25 '14 #7
Hi Seth Schrock,

Thank you for your advised. I am agreed with you but i have no right to restructure it due to my company policies..=(

Back to the insert query, is it possible to execute it by pressing a button in the mainform. if the answer is yes, then how to do it?
Nov 25 '14 #8
twinnyfo
3,653 Expert Mod 2GB
Nice when the policy-makers tell DB designers the best way to build a database....
Nov 25 '14 #9
Seth Schrock
2,965 Expert 2GB
You most certainly can. In your button's OnClick event, enter the following:
Expand|Select|Wrap|Line Numbers
  1. Dim strInsert As String
  2. Dim db As DAO.Database
  3.  
  4. strInsert = "INSERT INTO TblBackupDate (StockIDID, [Date For Stock A], [Date for Stock B], [Date For Stock C]) " & _
  5.             "SELECT StockID, [Date For Stock A], [Date for Stock B], [Date For Stock C] " & _
  6.             "FROM TblDeliverDate " & _
  7.             "WHERE [Delivery DateID] = " & Me.First_subform.Form.txtDeliveryDateID
  8.  
  9. Set db = CurrentDb()
  10.  
  11. db.Execute strInsert, dbFailOnError
  12. Set db = Nothing
Again you would need to replace the subform control name and the textbox control name.
Nov 25 '14 #10
Hi Seth Scrock,

Awesome, that is the code what I need! Thank you very much!

However, I notice that if I click the button twice, the data will insert twice. Is there any way can prevent this?

Again, thank you very very much.!
Nov 26 '14 #11
Seth Schrock
2,965 Expert 2GB
You could use DCount function to count the number of records that match and if the number is greater than 0, don't run the insert query.
Nov 26 '14 #12
Seth Schrock,

Thank you for your prompt reply. But may i know how to add into the code you gave?

Million thanks...!
Nov 29 '14 #13
@henry1988
I am having the same issue
i am unable to handle this :(

i am new in access .. How could i share you my file .. so i cab be able to share my problem ....
could you please share your email ID, ill email then or
please find my email address [email address removed]
Aug 12 '20 #14
please find link of my file
help me please[link removed]
Aug 12 '20 #15
twinnyfo
3,653 Expert Mod 2GB
shehzad1589,

I know you are new to this forum (and new to Access) can understand that you are looking for solutions. All of these things are fine, but you are going about it in the wrong way.

If you have a specific question about this specific thread, feel free to ask it here.

If you have a different question, or a question that may be similar or related to this question, please start a new thread (you can always refer to this thread via a link).

I am not sure, but I think your specific question would be more suitably answered in a separate thread (how to prevent duplication of records when inserting a record). Feel free to create a new thread, if so.

Finally, we NEVER allow e-mail addresses on this site and it is very inappropriate to simply post your DB online and expect someone to troubleshoot it for you. That is simply poor form. There are times when we may ask for a DB to be posted, but that will usually come from an established expert or moderator due to specifically challenging situations. Additionally, some experts/moderators will post tiny db's that demonstrate a principle under discussion.

Standing by to hepp!

Hope this hepps.
Aug 12 '20 #16

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

Similar topics

0
by: Krasimir_Slaveykov | last post by:
Hello George, Monday, July 14, 2003, 11:46:06 AM, you wrote: Read this: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html
2
by: Maggieanp22 | last post by:
I'm designing a db which stores client details but each client has multiple 'occasions'. For each occasion there is a section which has items purchased (there are sometimes none, sometimes...
1
by: phaddock4 | last post by:
Being fairly inexperienced at Access 2000, i've been reading many posts here for the last several days, and testing myself to find the best approach to do the following in A2K: SET UP: I have...
6
by: ryan | last post by:
I created in design view a subform within a subform. However the subform within the subform in only visible in design view. In form view the subform within the subform is not visible. I have...
20
by: Robert | last post by:
Need some help to stop me going around in circles on this one.... Have a nested subform (subform2) which simulates a continuous form for the record on the parent subform. Subform2 has rows of...
4
by: Nameless One | last post by:
Okay, it's been a while. I'm sure I used to know how to do this. I have two identical databases in structure and format. I want to copy records from a table in one database and add them to the...
1
by: Eric | last post by:
There are two sub forms in main form. One has values: Outlet Equipment 2 ABCBCBD 3 DKJDKJD 4 KDJKDJK Second has also a variable name outlet. I need help how to i display the...
5
by: bplantes | last post by:
I have a "Dashboard" in a tool I am building which has a list of different buttons. Clicking on each button will display different subforms which show up in a window to the right of the menu. One...
7
by: Laneyshia | last post by:
I'll cut to the chase: I'm writing a program written in Access Module. I never wrote in VBA code before although I do have programming experience. The idea is broken down into 3 steps. Step 1: ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.