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

VBA copy and paste?

100+
P: 164
I was wondering if it were possible to do this.

I have a form that is used to input a ton of information and then I have a button on it it to export it into a word template to print out. Now I have another form that I want to start populating after the word export is done. So I was trying to think of a way to get the SOME of the same information I would have just input into that form copied over to another form which has a sub form as well.

I dont know if it is entirely possible to sort of a COPY PASTE function in the way of when I hit the copy button on the first form mentioned it copies a predefined set of fields (ie: name, fee, miles, milescharge, tax) and then can navigate to the record in the sub form and but my cursor in and hit a button labeled COPY on that form, thus populating that field in that sub form???

I hope explained this clearly. I have been searching on how to do this and also posted a similar question on here about a week ago. http://www.thescripts.com/forum/thread762003.html

That worked for me but I couldnt control where the information was pushed and it just started a new record, but there will be times where it needs to go under an existing customers record, so it making a NEW record wouldn't work.

ANY help would be MORE than appreciated. Thanks a million!
Jan 28 '08 #1
Share this Question
Share on Google+
5 Replies


Jim Doherty
Expert 100+
P: 897
I was wondering if it were possible to do this.

I have a form that is used to input a ton of information and then I have a button on it it to export it into a word template to print out. Now I have another form that I want to start populating after the word export is done. So I was trying to think of a way to get the SOME of the same information I would have just input into that form copied over to another form which has a sub form as well.

I dont know if it is entirely possible to sort of a COPY PASTE function in the way of when I hit the copy button on the first form mentioned it copies a predefined set of fields (ie: name, fee, miles, milescharge, tax) and then can navigate to the record in the sub form and but my cursor in and hit a button labeled COPY on that form, thus populating that field in that sub form???

I hope explained this clearly. I have been searching on how to do this and also posted a similar question on here about a week ago. http://www.thescripts.com/forum/thread762003.html

That worked for me but I couldnt control where the information was pushed and it just started a new record, but there will be times where it needs to go under an existing customers record, so it making a NEW record wouldn't work.

ANY help would be MORE than appreciated. Thanks a million!
To be really elegant at this you are really talking about manipulating and controlling recordset behaviour. Sure you can automate a lot of things by direct commands such as DoCmd.GotoControl and then activating menubar commands such as the finding of a value based on the previous screen controls value and then pasting and so on but you do not have an extensive control 'with the data' so to speak.

Here you are talking about identifying specific set columns and rows within an existing recordset that possibly 'may' be filtered or restricted in some way at any one time, only grabbing certain specific values, navigating to a set row on another screen and moving cursors to set points referenced by controls on other forms in a controlled and meaningful way and then updating the underlying recordset and requerying the form that has the amended/updated data provided to it.

I'm not sure if I am explaining this sufficiently but it would be worth your time looking at the concepts of traversing recordset objects and related manipulation of those recordsets. Heres a couple of HOWTO links from this site to get you started.

Access VBA DAO recordset loop using two recordsets
Finding a Specific Record Programmatically

Regards
Jim :)
Jan 28 '08 #2

100+
P: 164
Okay, I read through your post and absorbed as much as I could. Read the links you gave me and I believe got lost even more! sorry!

I posted some pictures up on one of my domains to maybe illustrate things a bit better, as I was probably a bit confusing in my description.

The first one:
http://www.mykesdesigns.com/access/contactsform.jpg

Is the first form used in the database to export data into word. Then I was wanting to have a copy customer button at the bottom to take the fields
(Plaintiff, Date of creation, Service Charge, Mileage Charge, Sales Tax Charge, and Check Amount)

and then pump them into this form:
http://www.mykesdesigns.com/access/maincustomer.jpg

but the only problem is, is if this is "walter's" second record I cant figure out how to add a record to an existing customer (Plaintiff)


So I don't know if there is an alternative method to all of this, doesn't have to be a copy, paste method. At this point I am willing to try anything.

I hope the pictures helped explain what I am trying to do, and sorry if it was an exact repeat of the first post was, I just thought it may help.

And thank you so much for even considering helping me out with this, it really does mean a lot!
Jan 28 '08 #3

Jim Doherty
Expert 100+
P: 897
Okay, I read through your post and absorbed as much as I could. Read the links you gave me and I believe got lost even more! sorry!

I posted some pictures up on one of my domains to maybe illustrate things a bit better, as I was probably a bit confusing in my description.

The first one:
http://www.mykesdesigns.com/access/contactsform.jpg

Is the first form used in the database to export data into word. Then I was wanting to have a copy customer button at the bottom to take the fields
(Plaintiff, Date of creation, Service Charge, Mileage Charge, Sales Tax Charge, and Check Amount)

and then pump them into this form:
http://www.mykesdesigns.com/access/maincustomer.jpg

but the only problem is, is if this is "walter's" second record I cant figure out how to add a record to an existing customer (Plaintiff)


So I don't know if there is an alternative method to all of this, doesn't have to be a copy, paste method. At this point I am willing to try anything.

I hope the pictures helped explain what I am trying to do, and sorry if it was an exact repeat of the first post was, I just thought it may help.

And thank you so much for even considering helping me out with this, it really does mean a lot!

Hi,

Placing those images on your domain is visually very helpful and simplifies matters because it is not always easy guessing what you have based on a post but now I can see the direction you need to take.

On that contactsform.jpg you are simply typing that plaintiff/customers name. I suggest you revisit your design by placing a customerID field in the table and feeding the plaintiff/customer from a plaintiff/customer table. This will eradicate the need to deal with the transfer of any data at all in the manner you are speaking about or go down the route of recordsets that I spoke about earlier.

You could potentially have many records for each plaintiff so linking the plaintiff to the screenform shown by contactsform.jpg (that way round) will put you in a position of being able to track those records per plaintiff

A 'query' can retrieve any data you wish to see in the plaintiff/customer side (Maincustomer.jpg subform) this is called the ' Auto lookup' feature in access. Have a look at this concept in help and if you get stuck with it get back to me.

It may be that I 'could' take a quick look at the db for you if it is not too far down your development/design road as it is not always is it easy to articulate these things on here before the thread gets a horrible mishmash of misunderstandings :)

Regards

Jim :)
Jan 28 '08 #4

100+
P: 164
Hi,

Placing those images on your domain is visually very helpful and simplifies matters because it is not always easy guessing what you have based on a post but now I can see the direction you need to take.

On that contactsform.jpg you are simply typing that plaintiff/customers name. I suggest you revisit your design by placing a customerID field in the table and feeding the plaintiff/customer from a plaintiff/customer table. This will eradicate the need to deal with the transfer of any data at all in the manner you are speaking about or go down the route of recordsets that I spoke about earlier.

You could potentially have many records for each plaintiff so linking the plaintiff to the screenform shown by contactsform.jpg (that way round) will put you in a position of being able to track those records per plaintiff

A 'query' can retrieve any data you wish to see in the plaintiff/customer side (Maincustomer.jpg subform) this is called the ' Auto lookup' feature in access. Have a look at this concept in help and if you get stuck with it get back to me.

It may be that I 'could' take a quick look at the db for you if it is not too far down your development/design road as it is not always is it easy to articulate these things on here before the thread gets a horrible mishmash of misunderstandings :)

Regards

Jim :)
Thank you so much for the quick reply. I am, as we speak looking up the auto lookup query. I hope this will solve my problem. So if I understand correctly, when the first form is brought up to input all the data (contactsform) they will input the custID and the auto lookup query will take care of putting all the required fields in the sub form that I need it to. Granted the sub form is based off of that query?

I think that is how it will work? I will try accomplishing this today. and Jim, if I am totally stumped.. if you 'could' take a look at this I would be more than thankful. I will come back with updates soon!
Jan 28 '08 #5

Jim Doherty
Expert 100+
P: 897
Thank you so much for the quick reply. I am, as we speak looking up the auto lookup query. I hope this will solve my problem. So if I understand correctly, when the first form is brought up to input all the data (contactsform) they will input the custID and the auto lookup query will take care of putting all the required fields in the sub form that I need it to. Granted the sub form is based off of that query?

I think that is how it will work? I will try accomplishing this today. and Jim, if I am totally stumped.. if you 'could' take a look at this I would be more than thankful. I will come back with updates soon!

Hi Mike,

As you know I took a look at the db for you and I'm sure you now understand the concepts of using queries to draw together your table data together to present the information in a meaningful way rather than by typing data in many times over unnecessarily.

Best of luck

Jim :)
Jan 29 '08 #6

Post your reply

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