Help! Suggested Code from Byron didn't work!

[Kristen]

In response to my question below, I tried the following
code based on Byron's answer and it isn't working?
Byron...are you out there to answer. Or could anyone else
please help.

I need a button to copy Address #1 from the Form Company
Info to the field C Address #1 on its subform Contact
Subform. what I wrote below didn't work.

Me![Contact Subform].Form![C Address #1] = Me.Address_#1

P.S. I don't understand VB at all. When do you use an
exclamation point? When do you use a period? When do you
use square paranthesis? When do you call out that it's a
form?
If you don't have time to answer these questions. Please
just answer my first one regarding the nonworking code.
Thank you!

I have a Company form with the company address. Then it
has a subform, Contacts, with the contact's address.
Sometimes the contact has a different address than the
company (thus the separate place to enter it), but many
times the contact has the same address as the Company. Is
there a button I could make that would fill in the address
from the Company form to a specific contact in the Contact
subform?

Thank you!

 

[Pavel Romashkin]

I am guessing that the address is a single field. Otherwise, the
solution will be a little more complicated as you will need to copy
several fields.
Try

Me.Form.Controls("Contact subform").Form![C Address #1] = Me![Address_#1]

This assumes that Address_#1 is the name of the field in the main form's
record source.
Generally, you use period to address a property or a method of an
object. You can use ! to address an item in a collection, such as
Forms!MyForm. You can use ! to address fields in the form's record
source. This is why Me![Contact Subform].Form may not work.
You use brackets to enclose a name of an object that contains characters
that you should avoid using , such as spaces. In my opinion, this
leads to much confusion, and I never use spaces in object names,
replacing them with underscores. In my case it would look like

Me.Contact_Subform.Form![C Address #1] = Me![Address_#1]
Hope this helps,
Pavel

 

[Dirk Goldgar]

You can use ! to address an item in a collection, such as
Forms!MyForm. You can use ! to address fields in the form's record
source. This is why Me![Contact Subform].Form may not work.

I think this could stand a little clarification. When you use the bang
notation (!) in conjunction with a form, as in

Me!Something

or

Forms!MyForm!Something

you are, properly speaking, referring to items in the form's default
collection the Controls collection. So if "Something" is the name of a
field in the form's recordsource, but not a control on the form, it
shouldn't work ... EXCEPT that Access does some magic behind the scenes
to match the reference up with a field in the recordsource, if it can't
be matched up with a control on the form. Conversely, if you use the
dot (.) notation, as in

Me.Something

or

Forms!MyForm.Something

you are, properly speaking, referring to properties or methods of the
form. So it shouldn't work to refer to controls on the form or fields
in the form's recordset ... EXCEPT that it does, because Access makes
the controls and fields available as properties of the form, so long as
their names don't conflict with those of innate properties or methods.

I take issue with Pavel's statement that "This is why Me![Contact
Subform].Form may not work." If "Contact Subform" is the name of a
subform control on the form referred to by the keyword Me, then
Me![Contact Subform] is a perfectly valid, canonical reference to that
control, and Me![Contact Subform].Form is a valid reference to the form
object being displayed by that control. Usually in my experience, when
this sort of reference doesn't work, it's because the name used is not
the name of the subform control at all, but rather the name of the form
that is the subform control's Source Object. These two names, the name
of the subform control and the name of the form that it displays, may be
the same, or they may not. If they are not, it's the name of the
subform *control* that must be used.

 

[Pavel Romashkin]

Dirk,

I believe you are right, and I am wrong. I read about this but never
worry too much about it because I try to avoid implicit references in my
code at all costs, exactly to avoid trying to figure out why they work
when they shouldn't and don't work when I think they should.
Thanks for clarifying this.

Pavel

You can use ! to address an item in a collection, such as
Forms!MyForm. You can use ! to address fields in the form's record
source. This is why Me![Contact Subform].Form may not work.

I think this could stand a little clarification. When you use the bang
notation (!) in conjunction with a form, as in

Me!Something

or

Forms!MyForm!Something

you are, properly speaking, referring to items in the form's default
collection the Controls collection. So if "Something" is the name of a
field in the form's recordsource, but not a control on the form, it
shouldn't work ... EXCEPT that Access does some magic behind the scenes
to match the reference up with a field in the recordsource, if it can't
be matched up with a control on the form. Conversely, if you use the
dot (.) notation, as in

Me.Something

or

Forms!MyForm.Something

you are, properly speaking, referring to properties or methods of the
form. So it shouldn't work to refer to controls on the form or fields
in the form's recordset ... EXCEPT that it does, because Access makes
the controls and fields available as properties of the form, so long as
their names don't conflict with those of innate properties or methods.

I take issue with Pavel's statement that "This is why Me![Contact
Subform].Form may not work." If "Contact Subform" is the name of a
subform control on the form referred to by the keyword Me, then
Me![Contact Subform] is a perfectly valid, canonical reference to that
control, and Me![Contact Subform].Form is a valid reference to the form
object being displayed by that control. Usually in my experience, when
this sort of reference doesn't work, it's because the name used is not
the name of the subform control at all, but rather the name of the form
that is the subform control's Source Object. These two names, the name
of the subform control and the name of the form that it displays, may be
the same, or they may not. If they are not, it's the name of the
subform *control* that must be used.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)