473,326 Members | 2,815 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,326 software developers and data experts.

copy and paste multiple records

Hi, I have been successful copying a vba code from one of your posts on
how to copy and paste a record by declaring the desired fields that
needs to be copied in form's declaration and creating two button "copy"
and "paste". Works like magic.
My problem is how can I copy multiple records and paste them at the
same time. My data entry form has main form that has a Questionlist box
of Questions. The second list box on the same form displays
subquestions based on the selection made in Question list box. The
subform allows to add response for a selected subquestion. Majority of
the time response choices are the same for all the subquestion.
Based on the scenario below, if I do data entry for SubquestionA, I
want to copy/paste all three records to Subquestion B and Subquestion
C.
Question A has
Sub questions A, B, C.
SubQuestion A: Response Choice: 1, 2, 3
SubQuestion B: Response Choice: 1, 2, 3
SubQuestion C: Response Choice: 1, 2, 3

Please let me know how can I achieve that. It will save a lot of time
entering the same records.
Thanks for all your help.

Manjeet

Jun 22 '06 #1
5 13928
Kaur wrote:
Hi, I have been successful copying a vba code from one of your posts on
how to copy and paste a record by declaring the desired fields that
needs to be copied in form's declaration and creating two button "copy"
and "paste". Works like magic.
My problem is how can I copy multiple records and paste them at the
same time. My data entry form has main form that has a Questionlist box
of Questions. The second list box on the same form displays
subquestions based on the selection made in Question list box. The
subform allows to add response for a selected subquestion. Majority of
the time response choices are the same for all the subquestion.
Based on the scenario below, if I do data entry for SubquestionA, I
want to copy/paste all three records to Subquestion B and Subquestion
C.
Question A has
Sub questions A, B, C.
SubQuestion A: Response Choice: 1, 2, 3
SubQuestion B: Response Choice: 1, 2, 3
SubQuestion C: Response Choice: 1, 2, 3

Please let me know how can I achieve that. It will save a lot of time
entering the same records.
Thanks for all your help.

Manjeet

I don't know what your solution is for one record but I should think
that the help file/code examples on ItemsSelected, Selected, ItemCount,
ListIndex...etc will get you what you need to loop through a list.
Jun 22 '06 #2
Thanks for replying.
This is the code I have used.

Option Compare Database
Dim Respondent As String
Dim ResponseNotes As String

Private Sub cmdCopy_Click()
Respondent = Me.RespondentID
ResponseNotes = Me.RespondentGroupNotes
End Sub

Private Sub cmdPaste_Click()
Me.RespondentID = Respondent
Me.RespondentGroupNotes = ResponseNotes

This allows me to copy just the one record in the datasheet subform for
a given subquestion and paste to next subquestion.

I am new to Access so seekin help to resolve this problem.

Thanks
salad wrote:
Kaur wrote:
Hi, I have been successful copying a vba code from one of your posts on
how to copy and paste a record by declaring the desired fields that
needs to be copied in form's declaration and creating two button "copy"
and "paste". Works like magic.
My problem is how can I copy multiple records and paste them at the
same time. My data entry form has main form that has a Questionlist box
of Questions. The second list box on the same form displays
subquestions based on the selection made in Question list box. The
subform allows to add response for a selected subquestion. Majority of
the time response choices are the same for all the subquestion.
Based on the scenario below, if I do data entry for SubquestionA, I
want to copy/paste all three records to Subquestion B and Subquestion
C.
Question A has
Sub questions A, B, C.
SubQuestion A: Response Choice: 1, 2, 3
SubQuestion B: Response Choice: 1, 2, 3
SubQuestion C: Response Choice: 1, 2, 3

Please let me know how can I achieve that. It will save a lot of time
entering the same records.
Thanks for all your help.

Manjeet

I don't know what your solution is for one record but I should think
that the help file/code examples on ItemsSelected, Selected, ItemCount,
ListIndex...etc will get you what you need to loop through a list.


Jun 22 '06 #3
Kaur wrote:
Thanks for replying.
This is the code I have used.

Option Compare Database
Dim Respondent As String
Dim ResponseNotes As String

Private Sub cmdCopy_Click()
Respondent = Me.RespondentID
ResponseNotes = Me.RespondentGroupNotes
End Sub

Private Sub cmdPaste_Click()
Me.RespondentID = Respondent
Me.RespondentGroupNotes = ResponseNotes

This allows me to copy just the one record in the datasheet subform for
a given subquestion and paste to next subquestion.

I am new to Access so seekin help to resolve this problem. 'this enumerates through the listbox. 1st col = 0.
'last col = num of columns-1

Dim var As Variant
Dim rst As DAO.Recordset
set rst = currentdb.openrecordset("TableName",dbopendynaset_
For Each var In Me.ListBoxName.ItemsSelected
rst.AddNew
rst!ID = ListBoxName.Column(0,var)
rst!RespondentGroupNotes = ListBoxName.Column(1,var)
rst.Update
Next
rst.close
set rst = Nothing

This is an example. You should be able to get the gist of it and modify
to suit.

Thanks
salad wrote:
Kaur wrote:

Hi, I have been successful copying a vba code from one of your posts on
how to copy and paste a record by declaring the desired fields that
needs to be copied in form's declaration and creating two button "copy"
and "paste". Works like magic.
My problem is how can I copy multiple records and paste them at the
same time. My data entry form has main form that has a Questionlist box
of Questions. The second list box on the same form displays
subquestions based on the selection made in Question list box. The
subform allows to add response for a selected subquestion. Majority of
the time response choices are the same for all the subquestion.
Based on the scenario below, if I do data entry for SubquestionA, I
want to copy/paste all three records to Subquestion B and Subquestion
C.
Question A has
Sub questions A, B, C.
SubQuestion A: Response Choice: 1, 2, 3
SubQuestion B: Response Choice: 1, 2, 3
SubQuestion C: Response Choice: 1, 2, 3

Please let me know how can I achieve that. It will save a lot of time
entering the same records.
Thanks for all your help.

Manjeet


I don't know what your solution is for one record but I should think
that the help file/code examples on ItemsSelected, Selected, ItemCount,
ListIndex...etc will get you what you need to loop through a list.


Jun 22 '06 #4
Thanks for the code. It seems like it takes the records and appends to
the same ID. This is the code I have.

Private Sub cmdPaste_Click()

Dim var As Variant
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblRespondent",
dbOpenDynaset)
_
For Each var In Me.LstResp.ItemsSelected
rst.AddNew
rst!RespondentID = LstResp.Column(0, var)
rst!RespondentGroupNotes = LstResp.Column(2, var)
rst!SurveyID = LstResp.Column(3, var)
rst!SurveyEditionID = LstResp.Column(4, var)
rst!QuestionID = LstResp.Column(5, var)
rst!SubQuestionID = LstResp.Column(6, var)
rst.Update
Next
rst.Close
Set rst = Nothing
End Sub

It looks like I am close to the answer to my problem but not there yet.
How do I copy first from first subquestion that has the data populated
with above fields and then go to the next subquestion and append the
records. Right now it will allow me to append these records to the same
subquestion.
Thanks for your time.
salad wrote:
Kaur wrote:
Thanks for replying.
This is the code I have used.

Option Compare Database
Dim Respondent As String
Dim ResponseNotes As String

Private Sub cmdCopy_Click()
Respondent = Me.RespondentID
ResponseNotes = Me.RespondentGroupNotes
End Sub

Private Sub cmdPaste_Click()
Me.RespondentID = Respondent
Me.RespondentGroupNotes = ResponseNotes

This allows me to copy just the one record in the datasheet subform for
a given subquestion and paste to next subquestion.

I am new to Access so seekin help to resolve this problem.

'this enumerates through the listbox. 1st col = 0.
'last col = num of columns-1

Dim var As Variant
Dim rst As DAO.Recordset
set rst = currentdb.openrecordset("TableName",dbopendynaset_
For Each var In Me.ListBoxName.ItemsSelected
rst.AddNew
rst!ID = ListBoxName.Column(0,var)
rst!RespondentGroupNotes = ListBoxName.Column(1,var)
rst.Update
Next
rst.close
set rst = Nothing

This is an example. You should be able to get the gist of it and modify
to suit.

Thanks
salad wrote:
Kaur wrote:
Hi, I have been successful copying a vba code from one of your posts on
how to copy and paste a record by declaring the desired fields that
needs to be copied in form's declaration and creating two button "copy"
and "paste". Works like magic.
My problem is how can I copy multiple records and paste them at the
same time. My data entry form has main form that has a Questionlist box
of Questions. The second list box on the same form displays
subquestions based on the selection made in Question list box. The
subform allows to add response for a selected subquestion. Majority of
the time response choices are the same for all the subquestion.
Based on the scenario below, if I do data entry for SubquestionA, I
want to copy/paste all three records to Subquestion B and Subquestion
C.
Question A has
Sub questions A, B, C.
SubQuestion A: Response Choice: 1, 2, 3
SubQuestion B: Response Choice: 1, 2, 3
SubQuestion C: Response Choice: 1, 2, 3

Please let me know how can I achieve that. It will save a lot of time
entering the same records.
Thanks for all your help.

Manjeet
I don't know what your solution is for one record but I should think
that the help file/code examples on ItemsSelected, Selected, ItemCount,
ListIndex...etc will get you what you need to loop through a list.



Jun 23 '06 #5
Kaur wrote:
Thanks for the code. It seems like it takes the records and appends to
the same ID. This is the code I have.

Private Sub cmdPaste_Click()

Dim var As Variant
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblRespondent",
dbOpenDynaset)
_
For Each var In Me.LstResp.ItemsSelected
rst.AddNew
rst!RespondentID = LstResp.Column(0, var)
rst!RespondentGroupNotes = LstResp.Column(2, var)
rst!SurveyID = LstResp.Column(3, var)
rst!SurveyEditionID = LstResp.Column(4, var)
rst!QuestionID = LstResp.Column(5, var)
rst!SubQuestionID = LstResp.Column(6, var)
rst.Update
Next
rst.Close
Set rst = Nothing
End Sub

It looks like I am close to the answer to my problem but not there yet.
How do I copy first from first subquestion that has the data populated
with above fields and then go to the next subquestion and append the
records. Right now it will allow me to append these records to the same
subquestion.
Thanks for your time.

I am unsure how to answer your problem/question. It's difficult for me
to visualize your form and what exactly it is you need to do.

You have a form. It has 2 list boxes; a Questionlist box of Questions
and then a listbox of subquestions. Then you have a subform that gets
updated...I think. I am unsure what the flow is...what you are
selecting to be updated and what should be updated. Where is the data
coming from? I think/know I would need more info/clarification in order
to help.


salad wrote:
Kaur wrote:

Thanks for replying.
This is the code I have used.

Option Compare Database
Dim Respondent As String
Dim ResponseNotes As String

Private Sub cmdCopy_Click()
Respondent = Me.RespondentID
ResponseNotes = Me.RespondentGroupNotes
End Sub

Private Sub cmdPaste_Click()
Me.RespondentID = Respondent
Me.RespondentGroupNotes = ResponseNotes

This allows me to copy just the one record in the datasheet subform for
a given subquestion and paste to next subquestion.

I am new to Access so seekin help to resolve this problem.


'this enumerates through the listbox. 1st col = 0.
'last col = num of columns-1

Dim var As Variant
Dim rst As DAO.Recordset
set rst = currentdb.openrecordset("TableName",dbopendynaset_
For Each var In Me.ListBoxName.ItemsSelected
rst.AddNew
rst!ID = ListBoxName.Column(0,var)
rst!RespondentGroupNotes = ListBoxName.Column(1,var)
rst.Update
Next
rst.close
set rst = Nothing

This is an example. You should be able to get the gist of it and modify
to suit.

Thanks
salad wrote:
Kaur wrote:

>Hi, I have been successful copying a vba code from one of your posts on
>how to copy and paste a record by declaring the desired fields that
>needs to be copied in form's declaration and creating two button "copy"
>and "paste". Works like magic.
>My problem is how can I copy multiple records and paste them at the
>same time. My data entry form has main form that has a Questionlist box
>of Questions. The second list box on the same form displays
>subquestions based on the selection made in Question list box. The
>subform allows to add response for a selected subquestion. Majority of
>the time response choices are the same for all the subquestion.
>Based on the scenario below, if I do data entry for SubquestionA, I
>want to copy/paste all three records to Subquestion B and Subquestion
>C.
>Question A has
>Sub questions A, B, C.
>SubQuestion A: Response Choice: 1, 2, 3
>SubQuestion B: Response Choice: 1, 2, 3
>SubQuestion C: Response Choice: 1, 2, 3
>
>Please let me know how can I achieve that. It will save a lot of time
>entering the same records.
>Thanks for all your help.
>
>Manjeet
>

I don't know what your solution is for one record but I should think
that the help file/code examples on ItemsSelected, Selected, ItemCount,
ListIndex...etc will get you what you need to loop through a list.

Jun 23 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: David Londeck | last post by:
I am using Visual Basic 2003 and I have written a notepad like application using the RichTextBox control. I am having trouble trying to emulate Microsoft Words text block copy/paste feature. In...
1
by: andree | last post by:
Hello, I have a form where a user may paste multiple records. The form has an AfterInsert procedure. The procedure copies the newly inserted record into a different table for audit purposes. ...
1
by: Sean Howard | last post by:
Dear All, As is my want I need to do something in Access that seems simple but cannot fathom out. I have main form with two subforms, both datasheets with an almost identical table structure....
2
by: HB2 | last post by:
I use to be able to highlight several records in a table, copy them and paste new records. Now when I right click the records the copy and paste functions are disabled. How do I enable them? ...
1
by: PalJoey | last post by:
I am having a problem copying and pasting records into the same table. When I copy a record that has a zero length string for a field Access seems to convert it to NULL when pasted. One of the...
0
by: igendreau | last post by:
I have a database with a Header table. Each record in tblHeader has two One-to-Many Relationships: with tblLines and tblKeys. The HeaderID field ties tblHeader to the other two tables. The data...
4
4Him
by: 4Him | last post by:
First off, let me say this is a great site! I've just started working with Access and much of my success is from what I've read here! Background: I have a form, driven off a single table. Goal:...
7
kcdoell
by: kcdoell | last post by:
Good morning everyone: I created a form and set the default view as a continuous form. Basically the form is displaying records in which the user can add or edit new ones. The record source for...
5
by: phill86 | last post by:
Hi I have a main form that holds records for scheduled meetings, date time location etc... in that form i have a sub form that has a list of equipment resources that you can assign to the meeting in...
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: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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: 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.