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

[henry1988]

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.

[Seth Schrock]

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.

[henry1988]

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?

[Seth Schrock]

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.

[henry1988]

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.

[twinnyfo]

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.

[Seth Schrock]

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.

INSERT INTO TblBackupDate (StockIDID, [Date For Stock A], [Date for Stock B], [Date For Stock C])
SELECT StockID, [Date For Stock A], [Date for Stock B], [Date For Stock C] 
FROM TblDeliverDate
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.

[henry1988]

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?

[twinnyfo]

Nice when the policy-makers tell DB designers the best way to build a database....

[Seth Schrock]

You most certainly can. In your button's OnClick event, enter the following:

Dim strInsert As String
Dim db As DAO.Database

strInsert = "INSERT INTO TblBackupDate (StockIDID, [Date For Stock A], [Date for Stock B], [Date For Stock C]) " & _
            "SELECT StockID, [Date For Stock A], [Date for Stock B], [Date For Stock C] " & _
            "FROM TblDeliverDate " & _
            "WHERE [Delivery DateID] = " & Me.First_subform.Form.txtDeliveryDateID

Set db = CurrentDb()

db.Execute strInsert, dbFailOnError
Set db = Nothing

Again you would need to replace the subform control name and the textbox control name.

[henry1988]

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.!

[Seth Schrock]

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.

[henry1988]

Seth Schrock,

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

Million thanks...!

[shehzad1589]

having same issue with me

Seth Schrock,

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

Million thanks...!

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]

[shehzad1589]

please find link of my file
help me please[link removed]