Need help using MsgBox to learn Bangs & Dots

[Mel]

I have these things

MyDb (data base name)
tblA
tblB

Fields in tblA = Name, Location, Phone
Fields in tblB = Date, Qty, Cost

FormA with Sub_FormB

The Question: How to write code to display Cost using bangs & dots?

Assume FormA is open & displaying a record... and we double click on
something... a field or a button or whatever...

BUT we are not going to use "Me" so we can see how it all works...
maybe something kinda like??

MsgBox code MyDb ! FormA ! Sub_FormB ! [Cost]

1 - Please show me the correct way to do the above.

2 - And... how would you do it using "Me" ??

thanks for any help.

Mel

 

[Graham Mandeno]

Hi Mel

A form you currently have open can be referenced via the Forms collection:

Forms!FormA!Sub_FormB.Form![Cost]

Note that "Sub_FormB" is the name of the *control* on FormA that contains
the subform, which is not necessarily the same as the name of the form
object that is contained in the control.

The actual form is reached via the Form property of the control
(Sub_FormB.Form).

This reference will work from anywhere in your application.

If the code you are executing is in the form class module of either FormA or
Sub_FormB, then you can get a more direct reference using "Me". "Me" refers
to the class object to which the module is attached (in this case a Form).

So, from FormA's module, you can use:

Me!FormA!Sub_FormB.Form![Cost]

And, from Sub_FormB's module:

Me![Cost]

 

[John W. Vinson]

I have these things

MyDb (data base name)
tblA
tblB

Fields in tblA = Name, Location, Phone
Fields in tblB = Date, Qty, Cost

FormA with Sub_FormB

The Question: How to write code to display Cost using bangs & dots?

Why would you WANT to? If you want to display the value of Cost for a given
record in sub_FormB, put a Textbox on sub_FormB. Set that textbox's COntrol
Source property to

[Cost]

You'll see the cost.

Assume FormA is open & displaying a record... and we double click on
something... a field or a button or whatever...

I'm not at all sure what you are expecting here. tblA is related one to many
to tblB. You have a record on tblA. That record might correspond to zero
records in tblB, or to one record, or to three hundred and seventy-two
records, which might all have different values of Cost.

What do you want to see!?

BUT we are not going to use "Me" so we can see how it all works...
maybe something kinda like??

I have NO idea what you mean here.

MsgBox code MyDb ! FormA ! Sub_FormB ! [Cost]

1 - Please show me the correct way to do the above.

Well, if you're using it in VBA code and (for some peculiar reason) want to
see the value of the currently selected record on Sub_FormB's Cost field,

MsgBox [Forms]![FormA]![Sub_FormB].Form![Cost]

2 - And... how would you do it using "Me" ??

If the Code exists on FormA:

MsgBox Me![Sub_FormB].Form![Cost]

If the Code exists on FormB:

MsgBox Me![Cost]

Translation:

Me! is a shortcut for [Forms]![TheFormContainingTheCode]. You can use Me! in
code embedded in a form to reference that form; or you can use
[Forms]![NameOfAForm] to reference any open form in the database (including
the current form).

A Form has Objects on it. You can reference those objects by specifying the
form - using either Me! or [Forms]![NameOfSomeForm] - followed by a bang
(meaning "an object in this collection") followed by the name of the object.
The square brackets are optional unless the object name contains a blank or
special character, but square brackets are *always* allowed and *never* hurt.

A Subform control has a Form property meaning "the form contained within this
subform control"; the .Form![Cost] syntax uses . to specify that you're
looking at a Property (rather than an object) of this control; in particular
you're looking at the Form property. Once you've specified that you're looking
at a Form you can then use ![controlname] to examine that control.

Hope this helps with what is admittedly very confusing syntax!

thanks for any help.

Mel

John W. Vinson [MVP]

 

[Guest]

Assuming that your subform control is called Sub_formB,
and that your button is on FormA:

MsgBox me.Sub_FormB.form!Cost

Each subform sits inside a subform control. It doesn't
matter what the name of the subform is (it is just .form).

What matters is the name of the control on FormA

(david)