Explanation needed of a VBA term

[David Newmarch]

I'm battling to see if I can adapt the code on the MSOffice Online assistance
article called "Basing one combo box on another".

(http://office.microsoft.com/en-us/assistance/HA011730581033.aspx)

My problem is that the article deals with one combo box based on another in
the same form. But I want a combo box in a subform to be based on the value
of a combo box on the main form, and so I suspect that code given in the
article, like the AfterUpdate event here,

Private Sub CategoryAfterUpdate()
Me.Product = Null
Me.Product.Requery
Me.Product = Me.Product.ItemData(0)
End Sub

would need to be adapted if the Category field was in the main form and the
Product field was in the subform.

I'm kind of blundering around trying to learn by trial and error, but it
would be a big help if I knew what "Me." means in that code, because I have a
hunch it's what I would need to change. I know it's a crucial bit of VBA
language (or whatever) because I see it all over the place, but not having
any kind of reference work available I can only guess at what it means.
Nobody ever seems to have offered an explanation of it that my web searches
have been able to pick up. Does it actually mean "me" like in English? In
which case would I simply replace it with the form name of the subform if
Procucts here was in a subform?

 

[John Spencer]

Good guess.

ME is a reference to the form or report that contains the code.

So if you want to reference a combobox on the main form from the subform. You
would need to

Forms![MainFormName]![ComboboxName]

If you are on the mainform and want to reference a combobox in the subform it is
a little more complex.

ME.[NameofSubformControl].Form![ComboboxNameonSubform]

Note that I used Me there, but you can use

Forms![MainFormName]![NameofSubformControl].Form![ComboboxNameonSubform]

Note the period right before the word Form and also note that it is Form not
FormS. A subformcontrol has a Form property. Also note that the name of the
subform control is NOT necessarily the same as the name of form referenced in
the subform's form property.

After you decode that statement, you should be on your way to a better understanding.

 

[Douglas J. Steele]

Me provides a way to refer to the specific instance of the class (or form)
where the code is executing.

In other words, if your code is running in the module associated with a form
named Form1, Me is the equivalent to Forms("Form1").

From a subform, you'd refer to a control named Product on the main form as
Me.Parent.Product.

For more details about referring to controls and properties of forms and
subforms, see http://www.mvps.org/access/forms/frm0031.htm as "The Access
Web".

Be aware that in that discussion, Subform1 and Subform2 are the names of
subform controls on the form. That may or not be the same name as the form
that's being used as a subform. If you created your subform by dragging one
form on top of another form, the names will likely be the same. If you
created your subform by adding a subform control to the main form and then
either using the wizard or filling in the properties manually, the name of
the subform controls is likely something like Child0.

 

[David Newmarch]

Thanks John, this is very helpful.

Can't tell you straight off if things now work because, inevitably, my real
example is more complicated, and I'll have think it through long and hard!

 

[David Newmarch]

many thanks. The Access Web checklist is very good to have. I'd got as far as
bookmarking the website, but hadn't yet burrowed down to the resource in your
link.

 

[David Newmarch]

Doug, just one more question about syntax. It's been extremely useful
studying the link you gave here:

For more details about referring to controls and properties of forms and
subforms, see http://www.mvps.org/access/forms/frm0031.htm as "The Access
Web".

But could I check that I'm right in assuming that the bit of syntax you use
when you "refer" to a control, is the way you represent the unique identifier
for *record* present in that control, rather than the actual text visible in
the control, since the control could be set to display a different column in
the record row?

 

[Douglas J Steele]

Doug, just one more question about syntax. It's been extremely useful
studying the link you gave here:


But could I check that I'm right in assuming that the bit of syntax you use
when you "refer" to a control, is the way you represent the unique identifier
for *record* present in that control, rather than the actual text visible in
the control, since the control could be set to display a different column in
the record row?

If you're talking about the difference between what's displayed in a combo
box versus what value you get when you refer to the combo box control in
code, it depends on what field's been defined as the Bound Column.

In the article to which you referred in your first post
(http://office.microsoft.com/en-us/assistance/HA011730581033.aspx), if you
look at that first picture, you'll see the Bound Column property at the
bottom of the visible portion of the Properties window. You'll see that it's
set to 1, which means that when you refer to the combo box in code, you'll
get the value of the 1st column of the currently selected row. From what you
can see of the Row Source property, the first column appears to be
"Categories" (although I suppose it could be that Categories is the name of
the table, and there's a fieldname after it: I can't really tell if that's a
comma or a period after Categories!). From the Column Widths property
(0";1"), the first column isn't visible, so you'd see a different value if
you're looking at the combo box than if you refer to it in code.

 

[David Newmarch]

If you're talking about the difference between what's displayed in a combo
box versus what value you get when you refer to the combo box control in
code, it depends on what field's been defined as the Bound Column.

Thanks, I think I've now got hold of this!