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

Copy text field on MSaccess form to another field within the same form

P: 38
Can anyone help me. I have a database that has a form. On the form I have a subform that brings in some data that is relevant to the data on the main form but from a week before (status of projects it was the data is about).
If things haven't changed I want someone to click on a button so that the data from the week before goes into the spot for this weeks data. I can't get it to work. I have tried using the expression builder on the on click property of the button and telling it that
[Status_for_Week_Ending]=Forms![qryForReview subform1]!Status_for_Week_Ending
However this does not work.
Any suggestions?
Mar 20 '07 #1
Share this Question
Share on Google+
17 Replies


Denburt
Expert 100+
P: 1,356
Not sure if I understand and I am missing some details.

Status_for_Week_Ending

Are you saying that you want to update this field with a new date and would that be todays date or the end if this week. Is this field on the main form or the subform?

Main Form
Me!Status_for_Week_Ending = DateAdd("d",7,Me!Status_for_Week_Ending)

SubForm
Me!MySubForm.Form!Status_for_Week_Ending = DateAdd("d",7,Me!MySubForm.Form!Status_for_Week_En ding)
Mar 20 '07 #2

P: 38
I'm sorry. I already have the date figured out. I just want it to copy data from one memo field and put it into another memo field with the click of a button.
Thanks

Not sure if I understand and I am missing some details.

Status_for_Week_Ending

Are you saying that you want to update this field with a new date and would that be todays date or the end if this week. Is this field on the main form or the subform?

Main Form
Me!Status_for_Week_Ending = DateAdd("d",7,Me!Status_for_Week_Ending)

SubForm
Me!MySubForm.Form!Status_for_Week_Ending = DateAdd("d",7,Me!MySubForm.Form!Status_for_Week_En ding)
Mar 20 '07 #3

Denburt
Expert 100+
P: 1,356
I am still not clear about where the controls reside from looking at what you have up top I think you want the date in the subform to be transfered to a control on the main form?

Me![Status_for_Week_Ending]=Me![qryForReview subform1].Form!Status_for_Week_Ending
Mar 20 '07 #4

P: 38
I am not sure how else to explain it. I just have a memo field that will auto fill based on criteria when the form opens (this field is in a subform on the main form). I want a button on the form to take that text from that memo field and put it in a new memo field that is already on the form (this field is on the main form) if the button is clicked.

I am still not clear about where the controls reside from looking at what you have up top I think you want the date in the subform to be transfered to a control on the main form?

Me![Status_for_Week_Ending]=Me![qryForReview subform1].Form!Status_for_Week_Ending
Mar 21 '07 #5

Denburt
Expert 100+
P: 1,356
If the button is on the main form and both memo fields are named the following code should work. A word of advice also I am using MS Access 2003 and recently I had two different databases kick back and scream at me for using underscores (don't know why) it took a lot of effort to change my tables and queries.

Expand|Select|Wrap|Line Numbers
  1. Me![Status_for_Week_Ending]=Me![qryForReview subform1].Form![Status_for_Week_Ending]
  2.  
If your button is on the subform then you should use the following:
Expand|Select|Wrap|Line Numbers
  1. Me.parent![Status_for_Week_Ending]=Me![Status_for_Week_Ending]
  2.  
Try it out and let me know.
Mar 21 '07 #6

P: 38
That is the code I am using minus the Me!, it gives me this error when I use it:
The expression On Click you entered as the event property setting produced the following error: The object doesn't contain the Automation object 'qryForReview subform1.'.
Any idea what this means or what to do?

If the button is on the main form and both memo fields are named the following code should work. A word of advice also I am using MS Access 2003 and recently I had two different databases kick back and scream at me for using underscores (don't know why) it took a lot of effort to change my tables and queries.

Expand|Select|Wrap|Line Numbers
  1. Me![Status_for_Week_Ending]=Me![qryForReview subform1].Form![Status_for_Week_Ending]
  2.  
If your button is on the subform then you should use the following:
Expand|Select|Wrap|Line Numbers
  1. Me.parent![Status_for_Week_Ending]=Me![Status_for_Week_Ending]
  2.  
Try it out and let me know.
Mar 21 '07 #7

Denburt
Expert 100+
P: 1,356
First you should always use Me! or something to that effect otherwise the program will have to try and figure out if it is a variable or an object on the form.

Check to make sure your subform control on the main form contains the name [qryForReview subform1] and they are spelled exactly the same. Then use the code I provided as is (unless the subform control name needs to be changed).
Mar 21 '07 #8

P: 38
What if it then says: The expression On Click you entered as the event property setting produced the following error: The object doesn't contain the Automation object 'Me.'.

First you should always use Me! or something to that effect otherwise the program will have to try and figure out if it is a variable or an object on the form.

Check to make sure your subform control on the main form contains the name [qryForReview subform1] and they are spelled exactly the same. Then use the code I provided as is (unless the subform control name needs to be changed).
Mar 21 '07 #9

Denburt
Expert 100+
P: 1,356
I think the pertinent question now is where are you using this snipet of code?

If you look at the form in design view check to see if the properties for the form are shown if not then on the menu bar click view and properties. On the properties sheet look for the "On Click" event set this to [Event Procedure] Then look to the right (While your cursor is in this field) and on the far right you can click a button that takes you to the VB window add the code right there and it should work.
Mar 21 '07 #10

P: 38
I did that. It is still not working. It gives me an error saying it can't be found. Then I have the option to end or debug.

I think the pertinent question now is where are you using this snipet of code?

If you look at the form in design view check to see if the properties for the form are shown if not then on the menu bar click view and properties. On the properties sheet look for the "On Click" event set this to [Event Procedure] Then look to the right (While your cursor is in this field) and on the far right you can click a button that takes you to the VB window add the code right there and it should work.
Mar 21 '07 #11

Denburt
Expert 100+
P: 1,356
OK sounds like were getting there.

1. Is this button on the main form or subform?
2. Are you sure both fields are named Status_for_Week_Ending
3. Are these fields disabled or locked for any reason?
3 If the button is in the subform are you sure the subform control is named qryForReview subform1
Mar 21 '07 #12

P: 38
1. The button is on the main form.
2. Yes both fields are named Status_for_Week_Ending.
3. No the are not disabled or locked for any reason.
4. NA

Let me know what else I can do? Thanks for all your help.


OK sounds like were getting there.

1. Is this button on the main form or subform?
2. Are you sure both fields are named Status_for_Week_Ending
3. Are these fields disabled or locked for any reason?
3 If the button is in the subform are you sure the subform control is named qryForReview subform1
Mar 22 '07 #13

Denburt
Expert 100+
P: 1,356
Actually in my last post my third question was mistated, however you did clarify a few things for me such as the button is on the main form. Now if you just make sure the subform control is named [qryForReview subform1] then there is no reason I can think of that would prevent this from working.

Expand|Select|Wrap|Line Numbers
  1. Me![Status_for_Week_Ending]=Me![qryForReview subform1].Form![Status_for_Week_Ending]
  2.  
You could try the following it may help you resolve this issue. Change the lin in question with the two following examples first try one then change it and try the next:

Expand|Select|Wrap|Line Numbers
  1. Me![Status_for_Week_Ending]="This should work"
  2.  
If that works great, if not then the field name needs to be checked. Now change it to read:

Expand|Select|Wrap|Line Numbers
  1. Me![qryForReview subform1].Form![Status_for_Week_Ending] = "If this doesn't work check the subform control name and the field name"
  2.  
I so hope this helps. :) Good Luck and you are welcome anytime.
Mar 22 '07 #14

Denburt
Expert 100+
P: 1,356
I just spotted this in the Articles section and it may help you understand how to reference subforms.

Referring to Items on a Sub-Form
Mar 22 '07 #15

P: 38
Ok the first one worked. The second one said
Run-time error '2448':
You can't assign a value to this object.

Do you know what this means?

Actually in my last post my third question was mistated, however you did clarify a few things for me such as the button is on the main form. Now if you just make sure the subform control is named [qryForReview subform1] then there is no reason I can think of that would prevent this from working.

Expand|Select|Wrap|Line Numbers
  1. Me![Status_for_Week_Ending]=Me![qryForReview subform1].Form![Status_for_Week_Ending]
  2.  
You could try the following it may help you resolve this issue. Change the lin in question with the two following examples first try one then change it and try the next:

Expand|Select|Wrap|Line Numbers
  1. Me![Status_for_Week_Ending]="This should work"
  2.  
If that works great, if not then the field name needs to be checked. Now change it to read:

Expand|Select|Wrap|Line Numbers
  1. Me![qryForReview subform1].Form![Status_for_Week_Ending] = "If this doesn't work check the subform control name and the field name"
  2.  
I so hope this helps. :) Good Luck and you are welcome anytime.
Mar 22 '07 #16

P: 38
Nevermind that was my error! IT WORKS!!!
You are awesome!
Thanks so much!

Ok the first one worked. The second one said
Run-time error '2448':
You can't assign a value to this object.

Do you know what this means?
Mar 22 '07 #17

Denburt
Expert 100+
P: 1,356
Tickles my insides, glad it works happy to help. :)
Mar 22 '07 #18

Post your reply

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