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

Copy all records in Subform(Datasheet view) & paste

P: 1
Hi All,

I am trying to create a form for updating values. I have a Table - ASP with Date, Product, Q1,Q2,Q3,Q4 fields. I have a Form ASP_Main with "Date" field for filtering by the date selected. There is a subform ASP_Subform embedded in the main form.

What I am trying to achieve with a click of a button is - Filtered data shows in ASP_Subform, so Copy this whole data, change the date in ASP_Main to today's date and paste the data copied.

Why? This will make sure that I have the data last entered, and new set of data with changes. We don't want to lose any history, and any change to even a single record should produce all records with current date.

I have not been able to run the
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acSelectAllRecords
  2. DoCmd.RunCommand acCopy
  3. etc...
as I am unable to select records in the subform. The error is that "SelectAllRecords" is not available now.

Please help... My deadline is very close now :(

Thanks & Advance
Jul 5 '08 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 1,923
You need to set focus on the subform control before you can run the acSelectAllRecords command. Replace the illustrative name used below with the actual name of your subform control.
Expand|Select|Wrap|Line Numbers
  1. Me.YourSubformControl.SetFocus
Jul 5 '08 #2

Expert 2.5K+
P: 3,072
You could also trigger an INSERT query to perform your task like:
Expand|Select|Wrap|Line Numbers
  1. currentdb.execute ("INSERT INTO ASP (ASP_Date, Product, Q1,Q2,Q3,Q4) SELECT(Date(), Product, Q1, Q2, Q3, Q4 from ASP WHERE ASP_Date=#" & me.txtdate & "#)")
I've changed the field Date into ASP_Date as Date is also a reserved word (and function as you cansee in the query). Just correct this and make sure the parentform's Date fieldname is correct.

Jul 5 '08 #3

Expert Mod 15k+
P: 31,419
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acSelectAllRecords
  2. DoCmd.RunCommand acCopy
  3. etc...
Although this would probably not have taken too much digging, it is nevertheless exactly what I needed to know for a job that is coming up later today, so thanks :)
Jul 8 '08 #4

Post your reply

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